none
Complicated queries with LINQ to SQL RRS feed

  • Question

  • Hello!

    I've recently studied some LINQ sources and decided to use it in the project I'm working on. Everything is almost clear except for one thing.

    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 temporary tables. 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<T> 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

    Thursday, October 2, 2014 12:38 PM

All replies

  • Hello Summit2,

    >> If I am, how to find a workaround for this?

    For a complicated query, I usually use a stored procedure, however, as you said, the LINQ2SQL does not support the creation of temporary tables. My suggestion is to use the Entity Framework which is similar with the LINQ2SQL and supports the table valued function which could return a temporary table. Here are some links would be helpful:

    Entity Framework

    Table-Valued Function Support

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 6, 2014 2:38 AM
    Moderator
  • Are you talking about database temporary tables or the results of intermed queries that are collections in memory?

    Actually I don't like this way at all, first because LINQ is treated as a mean of getting rid of SQL coding (functions in particular) at all and that was my objective. Second, I've spent quite a time studying LINQ and don't want to waste it on anything else.

    I have another question on the point. If I get some data from DB into memory via queries and then process them further in memory, I assume it's going to be much slower than the same operation with temp tables in base. Is it right? 

    Monday, October 6, 2014 9:39 AM
  • Upon my experience, No (it will be not slower, but same speed)

    The temp table in SQL server does not exist unless you execute the query that create this temp table. So when the SQL Server create temp table, the temp table will exist in memory when it created (unless the temp table is very very large, then some flush will occur and the sql will write some data on the HDD) which is the same as processing data resides in memory again and again (since processing data already in memory will not had to read from HDD which is the slowest operation to be done here)

    I prefer the second way since I found L2S processing data in memory is much easier to deal with and maintain and update.

    For your first question about creating temp table in LinqToSQL, without knowing your exact need, but, why you don't create a datatable, filling it with data you need, then use this datatable with Linq?


    .Net Blog VFP Blog

    Tuesday, October 7, 2014 3:07 PM