none
Linq Compiled Queries and int[] as parameter RRS feed

  • Question

  • Hello ,
    i'm using the following Linq to Sql compiled query.
        
     private static Func<MyDataContext, int[], int> MainSearchQuery =
             CompiledQuery.Compile((MyDataContext db, int[] online ) =>
                      (from u in db.Users where online.Contains(u.username)
                       select u));

    i know it is not possible to use sequence input paramter for a compiled query and im getting “Parameters cannot be sequences” error when running it.
    on another post here ,http://social.msdn.microsoft.com/forums/en-US/linqtosql/thread/08ccbb12-da4f-421a-8912-8fa95ebbead0/
     i saw that there is some solution but i couldn't understand it.
    does anyone know to use complied query with array as input paramter?
    please post example if you do.
    Thanks!
    Monday, December 28, 2009 5:53 PM

Answers

  • As Damien mentioned, LINQ to SQL does not support table-valued parameters and thus must send every item in the collection as a separate parameter to SQL Server. Consequently you might run into this problem even using an uncompiled query.

    I assume you are trying to compile the query to meet a performance goal. If that is the case, have you tried using ADO.NET directly with a table-valued parameter? This wouldn't incur the overhead of compiling the query, and it would also allow you to bypass the limit of 2100 parameters.

    It is possible that with such a large number of users that sending all of the online user IDs to SQL would incur some unacceptable overhead as well, in which case you may consider doing the ordering on the client or storing the online state of users in the database.

    Hope this helps provide you with some options.

    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Wednesday, January 6, 2010 4:08 PM
    Answerer

All replies

  • Right - as far as I know; LINQ doesn't support the "in" functionality when it comes to where clauses.

    Something like the following should work for you.  The only change is you would use the Any extension method of your array and Invoke for each item within it.  As soon as something returns "true," the remainder of the collection is ignored.

            private static Func<MyDataContext, int, XXX.Users> MainSearchQuery =
                  System.Data.Linq.CompiledQuery.Compile((MyDataContext db, int online) =>
                            from u in db.Users
                            where online == u.username
                            select u);
            private static bool IsAnyUserOnline(params int[] sequenceIds)
            {
                MyDataContext databaseInstance = new MyDataContext();
                return sequenceIds.Any<int>(holdId => MainSearchQuery.Invoke(databaseInstance, holdId).Count() > 0);
            }

     

    A couple of thigns to note:

    1 - Change XXX to the qualified path of your table (or leave as int - whichever works for you).
    2 - I declared the databaseInstance of MyDataContext in the method for completeness.  Scope it how you see fit.
    3 - If you aren't retaining info about the user other than they exist or their Id number...  You may want to consider creating a CompiledQuery against the count or only select the relevent data instead of the entire user record.  Depends on your requirements, but it might help trim some unecessary cycles.

    Monday, December 28, 2009 9:33 PM
  • hi syntaxeater , thank you .

    i think the function you wrote wont help me.
    maybe my example wasn't good. i just post a simple example instead of what i really need.

    this is the correct example:

      int[] onlineUsers = GetOnlineArray(); // Cache stored array in example {100,101,102,...,...,...,N}
    
                using (var db = new MyDataContext())
                {
                    var users = (from u in db.Users
                                 where u.u_gender == gender 
                                 orderby (onlineUsers.Contains(u.u_username)) descending
                                 select u.u_username).Skip(startRowIndex).Take(maximumRows).ToList();
                }
    this works great but wont work in complied query.

    any ideas?

    thanks!
    Wednesday, December 30, 2009 1:34 PM
  • LINQ to SQL does support the IN clause however it does not support table value parameters so it has to send each item in the list as a parameter.

    A compiled query produces a single TSQL statement and all the parameters it needs ready to be populated and executed. As a contains takes a list of variables size it therefore can't be used in a compiled query.

    If your list is of a fixed size you could just use multiple OR's that referenced each array item by index.

    [)amien
    Wednesday, December 30, 2009 3:43 PM
    Moderator
  • My list is not fixed size - it represnts the users online which changes all the time.
    The problem with sending each item in the list as a parameter is that sql has a limit of 2100 paramters. so this will fail when alot of users come online.

    I was thinking it might be possible to use linq dynamic libary to do the sorting - OrderBy(string) but i couldn't get the syntax right.


    Wednesday, December 30, 2009 3:51 PM
  • As Damien mentioned, LINQ to SQL does not support table-valued parameters and thus must send every item in the collection as a separate parameter to SQL Server. Consequently you might run into this problem even using an uncompiled query.

    I assume you are trying to compile the query to meet a performance goal. If that is the case, have you tried using ADO.NET directly with a table-valued parameter? This wouldn't incur the overhead of compiling the query, and it would also allow you to bypass the limit of 2100 parameters.

    It is possible that with such a large number of users that sending all of the online user IDs to SQL would incur some unacceptable overhead as well, in which case you may consider doing the ordering on the client or storing the online state of users in the database.

    Hope this helps provide you with some options.

    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Wednesday, January 6, 2010 4:08 PM
    Answerer