Hi,

I'd like to add a field into my custom projection. This new field is from a different table, but i'm having problems getting the generated SQL to do the join.

Right now i have: var qf = new QueryFactory();
var query = qf.Person .Select(() => new { PerID = PersonFields.PerId.ToValue<string>(), Forename = PersonFields.PerForeName.ToValue<string>(), Surname = PersonFields.PerLastName.ToValue<string>(), DOB = PersonFields.PerDob.ToValue<string>(), AppliedDate = ApplicationFields.AppCreatedOn.ToValue<DateTime>() });

the 'AppliedDate' field is from another table, but i just cant figure out how to get the LEFT JOIN i need to happen. I have left my 'join attempt' code out of this example to make it clearer what i want to achieve.

Ultimately i'd like the SQL to look like this:

SELECT p.PER_ID,p.Forename, p.Surename, p.DOB, a.APP_CreatedOn FROm [PERSON] p LEFT JOIN [APPLICATION] a ON a.PER_ID = p.PER_ID AND Application_ID = ( SELECT MAX(Application_ID) FROM [APPLICATION] WHERE PER_ID = p.PER_ID )

Any help would be greatly appreciated.

Thanks.

Use the LeftJoin method in the From. Example:

var q = qf.Create()
    .Select(() => new 
    { 
        CompanyName = CustomerFields.CompanyName.ToValue<string>() ,
        OrderId = OrderFields.OrderId.ToValue<int>(),
        OrderDate = OrderFields.OrderDate.ToValue<DateTime>()
    })
    .From(qf.Order.LeftJoin(OrderEntity.Relations.CustomerEntityUsingCustomerId));

You also can specify the custom filter on the relation:

var q = qf.Create()
    .Select(() => new 
    { 
        CompanyName = CustomerFields.CompanyName.ToValue<string>() ,
        OrderId = OrderFields.OrderId.ToValue<int>(),
        OrderDate = OrderFields.OrderDate.ToValue<DateTime>()
    })
    .From(qf.Order.LeftJoin(qf.Customer)
        .On(OrderFields.CustomerId == CustomerFields.CustomerId
            & OrderFields.CustomerId == qf.Customer.Max(CustomerFields.CustomerId)
        ));