C# – Multiple Left Outer Joins in LinqToSql

cc#-3.0linq-to-sqlnet

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?

Best Answer

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).

Related Topic