LINQ-to-SQL IN/Contains() for Nullable<T>
- by Craig Walker
I want to generate this SQL statement in LINQ:
select * from Foo where Value in ( 1, 2, 3 )
The tricky bit seems to be that Value is a column that allows nulls.
The equivalent LINQ code would seem to be:
IEnumerable<Foo> foos = MyDataContext.Foos;
IEnumerable<int> values = GetMyValues();
var myFoos = from foo in foos
where values.Contains(foo.Value)
select foo;
This, of course, doesn't compile, since foo.Value is an int? and values is typed to int.
I've tried this:
IEnumerable<Foo> foos = MyDataContext.Foos;
IEnumerable<int> values = GetMyValues();
IEnumerable<int?> nullables = values.Select( value => new Nullable<int>(value));
var myFoos = from foo in foos
where nullables.Contains(foo.Value)
select foo;
...and this:
IEnumerable<Foo> foos = MyDataContext.Foos;
IEnumerable<int> values = GetMyValues();
var myFoos = from foo in foos
where values.Contains(foo.Value.Value)
select foo;
Both of these versions give me the results I expect, but they do not generate the SQL I want. It appears that they're generating full-table results and then doing the Contains() filtering in-memory (ie: in plain LINQ, without -to-SQL); there's no IN clause in the DataContext log.
Is there a way to generate a SQL IN for Nullable types?
