Linq Compiled Queries and int[] as parameter

Question

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.

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

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!