Is there any in build functions available in NPoco for applying joins without in query joins

Question

I want to use in built NPoco functions for using join rather than any in query join support like:

var users = db.FetchOneToMany<UserDto, CarDto>(x => x.UserId, 
    "select u.*, c.* from Users u inner join Cars c on u.UserId = c.UserId order by u.UserId");

Please anybody give some idea how can use Joins in NPoco using C#.

Answer 1

Have a look at this example which has a one-to-many relationship from Person to Address, i.e. a person can have many addresses. You use IncludeMany combined with the Reference attribute.

[TableName("Person")]
[PrimaryKey("Id")]
public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }

    [Reference(ReferenceType.Many, ColumnName = "Id", ReferenceMemberName = "PersonId")]
    public List<Address> Addresses { get; set; } = new List<Address>();
}

[TableName("Address")]
[PrimaryKey("Id")]
public class Address
{
    public int Id { get; set; }
    public int PersonId { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Address3 { get; set; }
    public AddressStatus Status { get; set; }
}

This is how you retrieve all the people, each person with a list of addresses.

    public async Task<People[]> GetPeople()
    {
        var query = _database.QueryAsync<People>()
            .IncludeMany(s => s.Address);
        var people = await query.ToArray();
        return people;
    }

To take it to the next level, you may need to filter out the addresses based on their Status or something.