none
When does IQueryable change from SQL to Method building? RRS feed

  • Question

  • I understand that IQueryable delays execution until Execute is called.  Usually this happens on ToList or enumeration. 

    Although, on hover of the Enumerator I have seen both Sql queries, and aggregation of methods.

    My question is what methods build Sql queries, and what are considered none Sql.

    I am building a dynamic query, which is ultimatily not complex, but has a complex class (about 12 classes) structure to build it.  It works great the way it does, but I am trying to improve it.

    Sunday, July 25, 2010 4:56 PM

Answers

  • Hello,

    Lots of linq to sql operators are used to build your query, and thes operators finally build a query expression. If you use lamda expression, it finally builds an expression trese which are similar to CLR delegates. In my opinion, any operator that could not build a query expression or an expression tree is invalid (none sql). About the operators, query expression and expression tree, please take a look this article for more information: http://msdn.microsoft.com/en-us/library/bb308959.aspx#linqoverview_topic2

    For performance issue, there are some small tips for us:
    http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

    Hope this helps!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by LitEnders Wednesday, July 28, 2010 9:46 PM
    Tuesday, July 27, 2010 11:03 AM
  • My question is what methods build Sql queries, and what are considered none Sql.


    A linq query against L2S IQueryables (tables / function results etc) will be translated to T-SQL in its entirety up to the point where it is enumerated ( e.g. .ToList / .ToArray / foreach loop ), or turned into enumerable (.AsEnumerable).

    Although I am not sure if there is a list anywhere (maybe on msdn..?) showing all methods and operators that are supported by L2S, it translates most of the common methods on strings, datetime etc, as well as a bunch of System.Math methods. If it encounters a method, method overload, or operator that it can not translate to T-SQL it will throw a runtime exception. That can often be worked around by splitting up the query into a L2S part that will be translated to TSQL, and a L2O part that will run client side.

    Hope that helps...


       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    • Marked as answer by LitEnders Wednesday, July 28, 2010 9:46 PM
    Wednesday, July 28, 2010 4:06 AM
    Answerer
  • It is funny.  I have been reading Frameworks Design Guidelines, and saw Expression class for the first time.  A week later I ran into a problem requiring the class.  These kind of things has been happening often. 

    I did manage to figure it out though.

    If you do not use IEnumerable, but IQueryable just within a method.  The IQueryable will not let you add methods that are convertable to sql.  Although, passing the IQueryable between methods have strange results.  In order to dynamically add queries from other classes/methods  the class Expression is required.

    • Marked as answer by LitEnders Thursday, July 29, 2010 2:13 PM
    Thursday, July 29, 2010 2:13 PM

All replies

  • Hello,

    Lots of linq to sql operators are used to build your query, and thes operators finally build a query expression. If you use lamda expression, it finally builds an expression trese which are similar to CLR delegates. In my opinion, any operator that could not build a query expression or an expression tree is invalid (none sql). About the operators, query expression and expression tree, please take a look this article for more information: http://msdn.microsoft.com/en-us/library/bb308959.aspx#linqoverview_topic2

    For performance issue, there are some small tips for us:
    http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

    Hope this helps!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by LitEnders Wednesday, July 28, 2010 9:46 PM
    Tuesday, July 27, 2010 11:03 AM
  • My question is what methods build Sql queries, and what are considered none Sql.


    A linq query against L2S IQueryables (tables / function results etc) will be translated to T-SQL in its entirety up to the point where it is enumerated ( e.g. .ToList / .ToArray / foreach loop ), or turned into enumerable (.AsEnumerable).

    Although I am not sure if there is a list anywhere (maybe on msdn..?) showing all methods and operators that are supported by L2S, it translates most of the common methods on strings, datetime etc, as well as a bunch of System.Math methods. If it encounters a method, method overload, or operator that it can not translate to T-SQL it will throw a runtime exception. That can often be worked around by splitting up the query into a L2S part that will be translated to TSQL, and a L2O part that will run client side.

    Hope that helps...


       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    • Marked as answer by LitEnders Wednesday, July 28, 2010 9:46 PM
    Wednesday, July 28, 2010 4:06 AM
    Answerer
  • It is funny.  I have been reading Frameworks Design Guidelines, and saw Expression class for the first time.  A week later I ran into a problem requiring the class.  These kind of things has been happening often. 

    I did manage to figure it out though.

    If you do not use IEnumerable, but IQueryable just within a method.  The IQueryable will not let you add methods that are convertable to sql.  Although, passing the IQueryable between methods have strange results.  In order to dynamically add queries from other classes/methods  the class Expression is required.

    • Marked as answer by LitEnders Thursday, July 29, 2010 2:13 PM
    Thursday, July 29, 2010 2:13 PM