Linq Compiled Queries and int[] as parameter

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 related , 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!

Answer 1

Like the post that you referenced, it's not really possible out of the box. The post also references creating your own query provider, but it's a bit of overhead and complexity that you probably don't need.

You have a few options here:

1. Don't use a compiled query. Rather, have a method which will create a where clause from each item in the array resulting in something like this (psuedo-code):

   where 
       online[0] == u.username ||
       online[1] == u.username ||
       ...
       online[n] == u.username
   

   Note that you would have to use expression here to create each OR clause.

2. If you are using SQL Server 2008, create a scalar valued function which will take a table-valued parameter and a value to compare againt. It will return a bit (to indicate if the item is in the values in the table). Then expose that function through LINQ-to-SQL on your data context. From there, you should be able to create a CompiledQuery for that. Note that in this case, you should take an IEnumerable<string> (assuming username is of type string) instead of an array, just because you might have more than one way of representing a sequence of strings, and to SQL server for this operation, it won't matter what the order is.

Answer 2

hi casperOne, thanks again.

ill post a better example of my code and maybe you can help me .

it seems that using contains does work (with compiled query) due im not sure if it will work on large amount of users. linq seems to generate a paramter for each user in the int[].

This is my query:

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 is the resulting sql :

exec sp_executesql N'SELECT [t1].[u_username]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY 
        (CASE 
            WHEN [t0].[u_username] IN (@p0, @p1, @p2, @p3, @p4, @p5) THEN 1
            WHEN NOT ([t0].[u_username] IN (@p0, @p1, @p2, @p3, @p4, @p5)) THEN 0
            ELSE NULL
         END) DESC) AS [ROW_NUMBER], [t0].[u_username]
    FROM [dbo].[Users] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p6 + 1 AND @p6 + @p7
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int',@p0=315152,@p1=315151,@p2=315150,@p3=315149,@p4=73621,@p5=36982,@p6=12,@p7=12

1. Do you think its better to use the method you wrote ("create a where clause from each item in the array" - orderby in my case).

2. any change you can write a small example of this function? i dont understand how i can pass the int[] value to the scalar based function ?

THANKS.

Answer 3

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 someone out!