Complicated queries with LINQ to SQL

CrimRepo

Hello!

I've recently studied some LINQ sources and decided to use it in the project I'm working on. I'm having trouble understanding the basic strategy of working with LINQ to SQL. Hope you can help me with that.

I'm making complicated reports that make up of several tables. Earlier I used stored procedures for the purpose. I formed several temporary pieces of data that I stored in temporary tables and then joined them together using a series of 2-table joins.
Trouble is: LINQ doesn't allow the creation of SQL temporary tables. I assume that if I use the temp tables in memory, it's going to be much slower than in the stored procedure. I know that complicated queries are built in LINQ in a "cascade" way, but if I do it this way,
Question is: what am I going to receive in DataContext.Log in the end? I assume it's going to be a really huge query that is impossible to understand and use for debugging. Am I right? If I am, how to find a workaround for this? DataLoadOptions and LoadWith won't do, because I am processing all the data at once and using it will lead to an avalanche of queries.

Thanks in advance

JoeAlbahari

If you need to create temporary tables, the best solution can be to write a table-valued function that does the initial work of selecting into temporary tables and joining. Then you can use LINQ to query the table-valued function, adding predicates and performing additional joins, groups and projections as required.

kimbonics

LINQ-SQL is possibly better if your temp tables aren't too large (many 10's of megabytes), and, your script only joins temp tables to other temp tables.

You can write LINQ-SQL queries that join multiple result sets that have been pulled into memory. You can optimize the in-memory queries by storing each in-memory temp table with .ToDictionary()