LINQ to SQL通配符 [英] LINQ to SQL Wildcards

问题描述

如何在LINQ To SQL lambda表达式中构建通配符?

How can I build in wildcards to my LINQ To SQL lambda expression?

这是我目前拥有的:

var query = from log in context.Logs select log;
foreach (string filter in CustomReport.ExtColsToFilter)
{
    string tempFilter = filter;
    query = query.Where(Log => Log.FormattedMessage.Contains(tempFilter));
}

在我尝试在过滤器字符串中传递通配符之前，此方法可以正常工作.我正在尝试SqlMethods.Like()，但无济于事.

This works fine up until I try and pass wildcards in the filter string. I'm experimenting with SqlMethods.Like() but to no avail.

上面的过滤器如下所示:"<key>NID</key><value>mcass</value>".

The filters above look like this: "<key>NID</key><value>mcass</value>".

我希望能够通过这样的过滤器:"<key>NID</key><value>%m%</value>"

I'd like to be able to pass filters like this: "<key>NID</key><value>%m%</value>"

推荐答案

String.Contains实际上在LINQ to SQL中作为LIKE表达式实现，因此这些查询是等效的:

String.Contains is actually implemented as a LIKE expression in LINQ to SQL, so these queries would be equivalent:

query = query.Where(Log => Log.FormattedMessage.Contains("m"));
query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%m%"));

但是，使用SqlMethods.Like，您可以指定更复杂的模式，例如"%m%a%".对我来说很好.您真的看不到与Visual Studio内部的区别，因为要匹配的表达式位于T-SQL的参数中.

However, with SqlMethods.Like, you can specify more complex patterns, such as "%m%a%". Works fine for me. You can't really see the difference from inside visual studio, because the expression to be matched against is put inside a parameter in the T-SQL.

如果要在分析器中记录SQL查询，它将看起来像这样:

If you were to log the SQL query in a profiler, it would look something like this:

exec sp_executesql N'SELECT [t0].[ID], [t0].[FormattedMessage]
FROM [dbo].[Log] AS [t0]
WHERE [t0].[FormattedMessage] LIKE @p0',N'@p0 nvarchar(5)',@p0=N'%m%a%'

与问题本身无关，但是String.StartsWith和String.EndsWidth也会转换为SQL LIKE，当然模式稍有不同.

Not relevant to the question per se, but String.StartsWith and String.EndsWidth also translate to a SQL LIKE, with slightly different patterns of course.

这篇关于LINQ to SQL通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！