如何重用Linq To Sql查询中的where子句

I have users searching records of type Record. They type a search term in a textbox and then I search records by matching several fields with the search term.

我有用户搜索记录类型的记录。他们在文本框中键入搜索词,然后通过将多个字段与搜索词匹配来搜索记录。

My query looks like:

我的查询看起来像:

var results = from record in DataContext.Records
              where
                   record.Field1.ToLower().Contains(term) ||
                   record.Field2.ToLower().Contains(term) ||
                   record.Field3.ToLower().Contains(term)
              select record;

I have a number of queries that all use the same filter and thus I would like to extract the filtering so it can be reused. Something like:

我有许多查询都使用相同的过滤器,因此我想提取过滤,以便可以重复使用。就像是:

var filter = new Func<Record, string, bool>(
                (record, term) =>
                    record.Field1.ToLower().Contains(term) ||
                    record.Field2.ToLower().Contains(term) ||
                    record.Field3.ToLower().Contains(term)
             );

var results = from record in DataContext.Records
              where filter(record, term)
              select record;

However, it does not work because:

但是,它不起作用,因为:

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

方法'System.Object DynamicInvoke(System.Object [])'没有受支持的SQL转换。

How can I reuse my where condition across queries?

如何跨查询重用where where条件?

4 个解决方案

#1

Use a CompiledQuery!

使用CompiledQuery!

var filter = CompiledQuery.Compile(
    (DatabaseDataContext dc, Record record, string term) =>
        record.Field1.ToLower().Contains(term) ||
        record.Field2.ToLower().Contains(term) ||
        record.Field3.ToLower().Contains(term)
);

var results = from record in DataContext.Records
              where filter(DataContext, record, term)
              select record;

For more information, see How to: Store and Reuse Queries.

有关更多信息,请参见如何:存储和重用查询。

#2

You need to build an expression instead of a function:

您需要构建表达式而不是函数:

Expression<Func<Record, bool>> filter = 
  record => record.Field1.ToLower().Contains(term);  // rest omitted

The lambda expression remains the same, but you need to return it into a variable of type Expression<Func<Record, bool>> -- that will make the C# compiler compile it as an expression instead of a delegate, allowing it to be passed to LINQ to SQL.

lambda表达式保持不变,但是你需要将它返回到Expression >类型的变量中 - 这将使C#编译器将其编译为表达式而不是委托,允许它被传递到LINQ to SQL。

However, you won't be able to use an expression variable with a C#-syntax where clause: you'll need to use the Where extension method:

但是,您将无法将表达式变量与C#-syntax where子句一起使用:您需要使用Where扩展方法:

var results = DataContext.Records.Where(filter);

Edited to add: If you want to be able to create filters on different terms, you just need a method to produce an expression from a term:

编辑添加:如果您希望能够在不同的条件上创建过滤器,您只需要一个方法来从术语生成表达式:

private static Expression<Func<Record, bool>> Filter(string term)
{
  return r => r.Field1.ToLower().Contains(term);
}

var results = DataContext.Records.Where(Filter(term));

If you prefer to keep filter as a lambda as you have at the moment, you can do so, but the generics get a bit nested:

如果您希望将过滤器保留为目前的lambda,则可以这样做,但泛型有点嵌套:

Func<string, Expression<Func<Record, bool>>> filter =
  term => (r => r.Field1.ToLower().Contains(term));

var results = DataContext.Records.Where(filter(term));

Regardless, the important thing is that what goes in the Where clause must be an Expression<Func<Record, bool>> -- but as shown above you can make the expression depend on term by building a suitable expression on the fly. Which is exactly what LINQ to SQL would be doing if you spelled out the filter longhand in the Where clause.

无论如何,重要的是Where子句中的内容必须是Expression > - 但是如上所示,您可以通过动态构建合适的表达式使表达式依赖于term。如果你在Where子句中详细说明过滤器,这正是LINQ to SQL所要做的。

#3

In addition to the Expression<Func<Record, bool>> issue that others have pointed out, I suggest looking into PredicateBuilder. It's very good for dynamically combining lambda expressions.

除了其他人指出的Expression >问题之外,我建议调查PredicateBuilder。这对于动态组合lambda表达式非常有用。

#4

I think you need to make it an Expression<Func<Record, bool>>. Otherwise it's trying to translate the actual C# method call to SQL rather than the description of it. This is not a guarantee that this version will work; I'm not sure which string functions are translatable to SQL.

我认为你需要把它变成Expression >。否则,它试图将实际的C#方法调用转换为SQL而不是它的描述。这不能保证此版本能够正常运行;我不确定哪些字符串函数可以转换为SQL。

#1