Joins

When building SELECT, UPDATE, and DELETE queries, you can use any number of join clauses while composing the query.

dbExpression supports the following join types:

Left Join
Right Join
Inner Join
Full Join
Cross Join

Left Join

//select all people who do not have an address
IEnumerable<Person> people = db.SelectMany<Person>()
    .From(dbo.Person)
    .LeftJoin(dbo.PersonAddress).On(dbo.PersonAddress.PersonId == dbo.Person.Id)
    .Where(dbo.PersonAddress.Id == dbex.Null)
    .Execute();

Right Join

//get person credit limit info for people in zip 94043
IEnumerable<dynamic> info = db.SelectMany(
        dbo.Person.Id,
        dbo.Person.FirstName,
        dbo.Person.LastName,
        dbo.Person.CreditLimit,
        dbo.Person.YearOfLastCreditLimitReview
    )
    .From(dbo.Address)
    .RightJoin(dbo.PersonAddress).On(dbo.PersonAddress.AddressId == dbo.Address.Id)
    .RightJoin(dbo.Person).On(dbo.Person.Id == dbo.PersonAddress.PersonId)
    .Where(dbo.Address.Zip == "94043" & dbo.Address.AddressType == AddressType.Billing)
    .Execute();

Inner Join

//select all address records for person with id equal 1
IEnumerable<Address> addresses = db.SelectMany<Address>()
    .From(dbo.Address)
    .InnerJoin(dbo.PersonAddress).On(dbo.PersonAddress.AddressId == dbo.Address.Id)
    .Where(dbo.PersonAddress.PersonId == 1)
    .Execute();

Full Join

//select data set for people's purchases
IEnumerable<dynamic> purchases = db.SelectMany(
        dbo.Person.Id,
        dbo.Person.FirstName,
        dbo.Person.LastName,
        dbo.Purchase.OrderNumber
    )
    .From(dbo.Person)
    .FullJoin(dbo.Purchase).On(dbo.Purchase.PersonId == dbo.Person.Id)
    .Execute();

Cross Join

//select all product combinations price totals
IEnumerable<double> prices = db.SelectMany(
        (dbo.Product.As("p1").Price + dbo.Product.As("p2").Price)
    )
    .From(dbo.Product.As("p1"))
    .CrossJoin(dbo.Product.As("p2"))
    .Execute();