linq-to-sql fails if local list variable

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 exe开发者_开发知识库cuting 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?

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.

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.