Eager Loading more than 1 table in LinqtoSql

When I've tried in Linq2Sql to load table with 2 child tables, I've noticed, that multiple SQLs are generated. I've found that it is

a known issue, if you try to specify more than one to pre-load it just picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)

There are more explanations in

http://codebetter.com/blogs/david.hayden/archive/2007/08/06/linq-to-sql-query-tuning-appears-to-break-down-in-more-advanced-scenarios.aspx

The reason the relationship in your blog post above is generating multiple queries is that you have two (1:n) relationship (Customers->Orders) and (Orders->OrderDetails). If you just had one (1:n) relationship (Customer->Orders) or (Orders->OrderDetails) LINQ to SQL would optimize and grab it in one query (using a JOIN).

The alternative -to use SQL and POCO classes-see http://stackoverflow.com/questions/238504/linq-to-sql-loading-child-entities-without-using-dataloadoptions?rq=1

Fortunately the problem is not applicable to Entity Framework, that we want to use in future development instead of Linq2Sql

Product firstProduct =  db.Product.Include("OrderDetail").Include("Supplier").First();

Posted on Sunday, December 9, 2012 3:45 PM SQL Server , LINQ | Back to top