Multiple Left Outer Joins in LinqToSql?

Question

Is it possible to accomplish something like this using linqtosql?

select * from table t1
left outer join table2 t2 on t2.foreignKeyID=t1.id
left outer join table3 t3 on t3.foreignKeyID=t1.id

I can make it work using both DataLoad options or join syntax. But the problem is whenever I add a second left join, linqtosql queries with MULTIPLE sql statements, instead of doing a second left join in the underlying sql.

So a query like the one above will result in dozens of sql calls instead of one sql call with 2 left joins.

What are my other options? I can use a view in the DB, but now I'm responsible for creating the hierarchy from the flattened list, which is one of the reasons to use an ORM in the first place.

Note that T2 and T3 are 1:M relationships with T1. Is it possible to have linq efficiently query these and return the hierarchy?

Answer 1

Here is a similar question. Be cognizant of how the joins are composed.

For example, the following Linq to Sql query on AdventureWorks:

AdventureWorksDataContext db = new AdventureWorksDataContext();

var productStuff = from p in db.Products
                   join pl in db.ProductListPriceHistories on p.ProductID equals pl.ProductID into plv
                   from x in plv.DefaultIfEmpty()
                   join pi in db.ProductInventories on p.ProductID equals pi.ProductID into pii
                   from y in pii.DefaultIfEmpty()
                   where p.ProductID == 764
                   select new { p.ProductID, x.StartDate, x.EndDate, x.ListPrice, y.LocationID, y.Quantity };

Yielded the same SQL (verified via Profiler) as this SQL query:

SELECT Production.Product.ProductID, 
       Production.ProductListPriceHistory.StartDate,
       Production.ProductListPriceHistory.EndDate,
       Production.ProductListPriceHistory.ListPrice,
       Production.ProductInventory.LocationID,
       Production.ProductInventory.Quantity
FROM Production.Product
LEFT OUTER JOIN Production.ProductListPriceHistory ON Production.Product.ProductID = Production.ProductListPriceHistory.ProductID 
LEFT OUTER JOIN Production.ProductInventory ON Production.Product.ProductID = Production.ProductInventory.ProductID 
WHERE Production.Product.ProductID = 764

Multiple LEFT JOINs on the Primary Key of the parent table, yielding one generated SQL query.

Answer 2

I would think LINQ to SQL would be able to translate your left outer joins as long as you put the DefaultIfEmpty() calls in the right places:

var q = from t1 in table
        join t2 in table2 on t1.id equals t2.foreignKeyID into j2
        from t2 in j2.DefaultIfEmpty()
        join t3 in table3 on t1.id equals t3.foreignKeyID into j3
        from t3 in j3.DefaultIfEmpty()
        select new { t1, t2, t3 };

Answer 3

I don't think this is likely to be the right solution to your problem because there are more than one many to one relationships to your parent entity table:

select * from table t1 
left outer join table2 t2 on t2.foreignKeyID = t1.id 
left outer join table3 t3 on t3.foreignKeyID = t1.id

This is like a person with multiple children and multiple vehicles:

Say t1 is the person

id             str
1              Me

Say t2 are the children

PK  foreignKeyID   str
A   1              Boy
B   1              Girl

Say t3 are the vehicles

PK  foreignKeyID   str
A   1              Ferrari
B   1              Porsche

Your result set is:

Me Boy Ferrari
Me Girl Ferrari
Me Boy Porsche
Me Girl Porcshe

Which I fail to see how this is a useful query (even in SQL).

Answer 4

You dont need that terrible join syntax if you FK's are properly setup.

You would probably write:

var q = from t1 in dc.table1s
        from t2 in t1.table2s.DefaultIfEmpty()
        from t3 in t1.table3s.DefaultIfEmpty()
        select new { t1, t2, t3 };