I want to generate this SQL statement in LINQ:

我想在LINQ中生成这个SQL语句:

select * from Foo where Value in ( 1, 2, 3 )

The tricky bit seems to be that Value is a column that allows nulls.

棘手的一点似乎是Value是一个允许空值的列。

The equivalent LINQ code would seem to be:

等效的LINQ代码似乎是:

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.

当然,这不会编译,因为foo.Value是一个int?和值的类型为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.

这两个版本都给了我期望的结果,但它们不会生成我想要的SQL。它们似乎正在生成全表结果,然后在内存中进行Contains()过滤(即:在普通LINQ中,没有-to-SQL); DataContext日志中没有IN子句。

Is there a way to generate a SQL IN for Nullable types?

有没有办法为Nullable类型生成SQL IN?

NOTE

As it turns out, the problem I was having didn't have anything to do Contains or Nullable, and so the phrasing of my question is largely irrelevant. See @Nick Craver's accepted answer for details.

事实证明,我遇到的问题没有任何关系包含或Nullable,所以我的问题的措辞在很大程度上是无关紧要的。有关详细信息,请参阅@Nick Craver的接受答案。

1 个解决方案

#1

This should work for your example:

这适用于您的示例:

IEnumerable<int> values = GetMyValues();
var myFoos = from foo in MyDataContext.Foos;
             where values.Contains(foo.Value.Value)
             select foo;

Casting as an IEnumerable<T> from the start means execution will be outside of SQL, instead call the IQueryable<Foo> directly. If you cast as IEnumerable and use it in a query, it gets all MyDataContext.Foos then uses that iterator and executes the rest of the query in C# instead of in SQL.

从一开始就作为IEnumerable 进行转换意味着执行将在SQL之外,而是直接调用IQueryable 。如果您转换为IEnumerable并在查询中使用它,它将获取所有MyDataContext.Foos然后使用该迭代器并在C#中而不是在SQL中执行查询的其余部分。

If you want to run in SQL, don't cast as IEnumerable anywhere along the way. The effect is the same as using MyDataContext.Foos.AsEnumerable() in the query.

如果要在SQL中运行,请不要在此过程中的任何位置转换为IEnumerable。效果与在查询中使用MyDataContext.Foos.AsEnumerable()相同。

#1