none
Need help on CompiledQueries, please!

    Question

  • Hi,

     

    I'm working on a project for my graduation in which I'm testing the efficiency of various OR/M software. My first tests show that LINQ to SQL using uncompiled queries is slower than some other third party solution. I wanted to compile all the queries in LINQ to SQL but I have found a wall which I cannot jump over.

     

    The problem is with Compiled Queries and sequence parameters. The code below uses an uncomplied query (it's not one of those I use in my project, this one is much simpler and just shows the problem):

     

    Code Snippet

    NorthwindDataContext dc = new NorthwindDataContext();

    List<int> productIds = new List<int> { 1, 2, 3 };

     

    var q = from p in dc.Products

     where productIds.Contains(p.ProductID)

     select p;

     

     

    When I try to compile this query and execute it, I get an unsuported exception:

    Code Snippet

     

    var qProductsCompiled =

    CompiledQuery.Compile<NorthwindDataContext, List<int>, IEnumerable<Product>>

    (

    (context, idList) => from p in dc.Products

            where idList.Contains(p.ProductID)

            select p

    );

    try

    {

    qProductsCompiled(dc, productIds).ToList();

    }

    catch (NotSupportedException ex)

    {

    //ex.Message: Parameters cannot be sequences

    }

     

     

    Is there any workaround for this? I have quite a lot of queries which use a list of ids to filter the results.

     

    Any help would be appreciated.

    Maciej

    Saturday, June 07, 2008 2:36 PM

Answers

  • Queries with list parameters cannot be precompiled because the translation of the query is dependent on the number of items in the list.

     

    Sunday, June 08, 2008 5:42 PM

All replies

  • Queries with list parameters cannot be precompiled because the translation of the query is dependent on the number of items in the list.

     

    Sunday, June 08, 2008 5:42 PM
  • There is a workaround for your problem, however its not an easy thing to do.

    You would need to modify the lambda expression dinamically from:

     

    Code Snippet

    List<int> productIds = new List<int> { 1, 2, 3 };

     

    var qProductsCompiled =

    CompiledQuery.Compile<NorthwindDataContext, List<int>, IEnumerable<Product>>

    (

    (context, idList) => from p in dc.Products

               where idList.Contains(p.ProductID)

               select p

    );

     

    qProductsCompiled(dc, productIds).ToList();

     

    to something like:

     

    Code Snippet

    var qProductsCompiled =

    CompiledQuery.Compile<NorthwindDataContext, int, int, int, IEnumerable<Product>>

    (

    (context, p1, p2, p3, idList) => from p in dc.Products

               where new int[] { p1, p2, p3 }.Contains(p.ProductID)

               select p

    );

     

     

    qProductsCompiled(dc, 1, 2, 3).ToList();

     

    The best way to do that would be to create a custom ExpressionVisitor. Matt Warren provided a sample code of an original ExpressionVisitor here:

    http://blogs.msdn.com/mattwar/archive/2007/07/31/linq-building-an-iqueryable-provider-part-ii.aspx.

     

    Once you have an appropriate lambda, you would also need to use some reflection to make a call to appropriate CompiledQuery.Compile<T>.

     

    One issue is that compiled query can only take 3 arguments. If your list contains more arguments, you will need to create a structure with n properties and reference them as arguments:

     

    Code Snippet

        class ParameterStructure<T1, T2, T3, T4, T5>
        {
            public T1 Arg1 { get; set; }
            public T2 Arg2 { get; set; }
            public T3 Arg3 { get; set; }

            public T4 Arg3 { get; set; }
            public T5 Arg3 { get; set; }
        }

     

    var qProductsCompiled =

    CompiledQuery.Compile<NorthwindDataContext, ParameterStructure<int, int, int, int, int>, IEnumerable<Product>>

    (

    (context, ps, idList) => from p in dc.Products

                   where new int[] { ps.Arg1, ps.Arg2, ps.Arg3,

                         ps.Arg4, ps.Arg5 }.Contains(p.ProductID)

                   select p

    );

     
     
    Hope that helps,
    Maurycy
    Sunday, June 08, 2008 7:55 PM
  • Thank you Maurycy for this idea. I might try to use it for some of those queries that take a list of ids. Unfortunately the list size might sometimes be very big (like 100 id's). So creating so many types would be very painful (although I might generate them...).

     

    I understand that there is no other way of doing this. It's a pity, because I really would like LINQ to SQL to perform very good (I love LINQ). Just to picture the overhead: when I run my test for LINQ to SQL (all queries are uncompiled), the CPU utilization for transaction logic is 90%, SQL server is 10%. With some_other_pupular_ORM_software it's almost the opposite.

     

    Best regards,

    Maciek

    Monday, June 09, 2008 9:05 AM
  • If you use ExpressionVisitor and reflection you can automate the process, for any number of arguments and any LINQ expression. I don't know of any other way to do it. The other thing is, that since you are doing perf tests, these extra steps will definatelly corrupt the data you will get...

     

    Thanks,

    Maurycy

    Monday, June 09, 2008 11:27 PM
  • One solution that I have found myself doing (for MS SQL 2005/2008). And I'm not sure if it is appropriate in all scenarios is to just write dynamic sql and execute it against the datacontext using the ExecuteQuery method.

    For example, if I have an unbounded list that I am trying to pass to a query to do a contains...

    ' Mock a list of values
    Dim ids as New List(of Integer)
    ids.Add(1)
    ids.Add(2)
    ' ....
    ids.Add(1234)
    
    Dim indivs = (From c In context.Individuals _
              Where ids.Contains(c.Id) _
              Select c).ToList

    I would modify this query to create a SQL string to execute against the database directly like so...

    Dim str As New Text.StringBuilder("")
    Dim declareStmt as string = "declare @ids table (indivId int) " & vbcrlf)
    
    For i As Integer = 0 To ids.Count - 1
    
       str.Append("select " & ids(i).ToString() & " & vbcrlf)
    
       If i < ids.Count Then
         str.Append("union " & vbcrlf)
       End If
    
    Next
    
    Dim selStatement As String = "select * From " & context.Mapping.GetTable(GetType(Individuals)).TableName & _
       " indiv " & vbcrlf & _
       " inner join @ids ids on indiv.id = ids.id"
    
    Dim query = declareStmt & str.ToString & selStatement
    Dim result = context.ExecuteQuery(of Individual)(query).ToList

    So barring any syntax errors or bugs that I coded (the above is more or less psuedo code and not tested), the above will generate a table variable in SQL and execute an inner join against the desired table (Individuals in this example) and avoid the use of a "IN" statement.

    Hope that helps!

    Wednesday, April 28, 2010 12:59 PM
  • I had simlar issue and had gone with the approach of using Stored Procedure that is way faster than LINQ To Sql.  I had 6 LIST objects that need to b passed as arguements to the Compiled query and it was throwing me "Comparison operation is not suppored on System.int32 or System.string for different LIST objects"  I created a structure for these LIST and was comparing using lstObjectName.Contains(columnvalue) and fails.  But, I have everything passed to the stored procedure and that's lot better and accurate.  Probably you might want to use that instead of Dynamic sql for easy maintainence.

    Thursday, July 01, 2010 3:47 PM