It seems to be slow for LinqPad to translate cross join style linq queries?

jiancheng

I am learning Linq and found writing readable linq queries to join multiple tables together a bit challenging until I saw the post. I understand stacking "from" clauses would cause CROSS JOIN which is expensive for performance, but if it is followed with ".Where" would actually make the query the same effect as INNER JOIN or OUTER JOIN, which is what I am after. However I found while stacking "from" seems to be OK on performance with database queries in my usage, it is usually very slow in LinqPad or even not usable for queries being executed forever. So my question is, if stacking "from" is common in Linq practice, should LinqPad be improved to handle it better? Or if it is not recommended to use stacking "from", how do I write easy to read and maintainable Linq queries for INNER JOIN and OUTER JOIN?

Many thanks!

nescafe

For LINQ-to-SQL, the explained logic should suffice:

from maintable in Repo.T_Whatever 
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()

If you omit the DefaultIfEmpty() you will have an inner join.

You could trace the query in SQL Profiler and examine the execution plan in SQL Server Management studio if the query takes forever to complete.

For LINQ-to-Entities, you'd better use the group join syntax, as mentioned by Tamir Daniely in the same thread:

LINQ to SQL translates this correctly to join operations. Over objects however this method forces a full scan, This is why the official documentation offers the group join solution that can take advantage of hashes to index searches. – Tamir Daniely Jan 5 at 0:37

You can find example usage here: http://stackoverflow.com/a/15599143/1105812

jiancheng

Thanks nescafe.

Because I use EF and use Left Outter Join I assume the Linq syntax of stacking "from" with .DefaultIfEmpty() would sacrifice performance, but it seems to be OK on performance so far on SQL Server. Is there any reason why the same query from LinqPad seems to be a lot slower to run?