linq-to-sql fails if local list variable

Question

I'm struggling to get what I thought would be a simple LINQ-to-SQL query to work. I can construct the query ok and can verify that the SQL it generates is correct but when executing get the dreaded "System.NotSupportedException: Queries with local collections are not supported" exception.

I've simplified my query but in short the query below works:

var query = from asset in context where new[] { 1, 2, 3 }.Contains(asset.code) select asset

this query also works:

var query = from asset in context where new List<int>() { 1, 2, 3 }.Contains(asset.code) select asset

But the query below will fail when an attempt is made to get the result set:

List<int> myList = new List<int>(){1, 2, 3};
var query = from asset in context where myList.Contains(asset.code) select asset

Anyone solved this sort of issue?

Answer 1

This works fine for me in LINQPad:

List<int> myList = new List<int>(){1, 2, 3}; /* Fixed your compiler error here */
var query = from asset in assets where myList.Contains(asset.code) select asset; 

As such, I assume you aren't actually using a List for myList, but rather a generic IEnumerable or something similar?

Please try pasting your "unsimplified" version, as your simplication corrects your error.

Answer 2

What you posted should work, leading me to believe you didn't actually post the broken code.

Make sure that the MyList variable is a List<int> and not an IList<int>... if the variable type is IList<int>, you'd get that exception.