Left Outer Joins in DA LINQ.

In terms of improving DA LINQ, I’m happy to announce that i have just committed improved support for the LEFT OUTER JOIN in the next release of the Data Abstract for .NET.

We can compose Left Outer join query by using DefaultIfEmpty standard query operator. See the sample below:

var query =from c in lrda.GetTable()where c.CustomerID=="PARIS"join o in lrda.GetTable() on c.CustomerID equals o.CustomerIDinto ords from o in ords.DefaultIfEmpty()selectnew{c.CustomerID, o.EmployeeID};

Notice that we are using an **into** clause to direct the matching join results into a temporary sequence *ords*. The *DefaultIfEmpty* operator will be applied onto each item of that sequence, so if the record is missing from the joined results, a default value will be provided.

The DA LINQ query above will be translated into following DA SQL statement that will be passed to the DASqlengine.

SELECT[t0].[CustomerID],[t1].[EmployeeID]FROM[Customers]AS[t0]LEFTOUTERJOIN[Orders]AS[t1]ON([t1].[CustomerID]=[t0].[CustomerID])WHERE([t0].[CustomerID]= :p0)--- :p0='PARIS'

This improvement will be in the next release.

and it will execute against the database with the expected result:

CustomerID EmployeeID ---------- ----------- PARIS NULL