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.

Just read deeper and THEN let us know when you get stuck. social.msdn.microsoft.com/forums/en-US/linqtosql/thread/… — Hamish Grubijan
– Hamish Grubijan, Commented Dec 28, 2009 at 17:57
i did :( , it is really complex and examples aren't really related to a simple requirement of passing a array as parameter. — dan
– dan, Commented Dec 28, 2009 at 18:01

casperOne · Accepted Answer · 2009-12-29 16:20:53Z

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:

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.
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.

edited Dec 29, 2009 at 16:20

answered Dec 28, 2009 at 18:30

casperOne

74.7k19 gold badges189 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dan Over a year ago

thx! i couldn't make any of them work. 1) if i dont use compiled query i have no problem at all , i can just pass the int[] as parameter. maybe i miss understood what you meant. 2) i read about table-valued , but i think im getting it wrong. i created a scalar based function that get a table-value parameter but what does it return?

casperOne Over a year ago

@dan 1) If you don't use the compiled query at all, you still can't use Contains. You have to split the query out into a number of OR statements 2) The scalar function with the table valued parameter would return a bit, which is 0 if the item is not contained in the table you pass in, 1 if it is.

Athari · Accepted Answer · 2012-12-24 01:08:27Z

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!

Collectives™ on Stack Overflow

Linq Compiled Queries and int[] as parameter

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related