在C#LINQ中使用SQL LIKE运算符

j2datikz  于 2022-12-06  发布在  C#
关注(0)|答案(5)|浏览(201)

我正在用C#建立一个查询。对于整型和字符串字段,大小写很简单。对于日期字段,我使用以下查询:

list.Where("myDateColumn >= DateTime(2017,1,20)");

如何在LINQ中执行以下SQL LIKE查询?

select * from table where myTextColumn LIKE '%abc%';
taor4pac

taor4pac1#

Like in Linq有很多可能性:
对于LIKE“%abc %”;

list.Where(x => x.myTextColumn.Contains('abc'));

对于LIKE 'abc %';

list.Where(x => x.myTextColumn.StartWith('abc'));

对于LIKE '% abc';

list.Where(x => x.myTextColumn.EndsWith('abc'));

**更新:**如果您还需要添加日期比较,则可以执行以下操作:

DateTime date2Compare = new DateTime(2017, 1, 20);
list.Where(x => myDateColumn >= date2Compare && x.myTextColumn.Contains('abc'));
rkue9o1l

rkue9o1l2#

Placement of the Wildcard '%' in a LIKE clause makes a difference, and C# has the methods to back this up. Below is what the placements of the Wildcard means.
LIKE '%abc'

Meaning: Find any word ending with 'abc'.
C# equivalent: EndsWith

LIKE 'abc%'

Meaning: Find any word starting with 'abc', and you don't care about the text after.
C# equivalent: StartWith

LIKE '%abc%'

Meaning: Find any word that contains 'abc', and you don't care where in the word it appears.
C# equivalent: Contains

rlcwz9us

rlcwz9us3#

可以将ContainsmyTextColumn字段一起使用

var date = new DateTime(2017,1,20);
list.Where(x => x.myDateColumn >= date  && x.myTextColumn.Contains('abc'));
zpgglvta

zpgglvta4#

虽然其他答案(包括被接受的答案)的正确率为80%,但也有一些注意事项。
T-SQL允许Like语句不仅在字符串的开头和结尾使用通配符,而且在中间也可以使用通配符。%不是语法中唯一的特殊字符,有关详细信息,请参阅docs
. NET核心具有EF.Functions.Like()方法,该方法按字面翻译为LIKE语句。请参见下面的示例。

//assuming there is a db context with a table of Foos
await using var dbContext = new SqlContext();

//assuming table of Foos has Ipsum, Iptum, Ipuum
var result = dbContext.Foos.Where(x
    => EF.Functions.Like(x.Bar, "Ip_um"));

注意事项:

  • 这只适用于服务器端评估。尝试在客户端评估中使用此功能将会掷回NotSupportedException
  • EF.Functions.LikeContainsStartsWith之间的SQL转换存在显著差异,可能是impact performance

如果您确定使用的是服务器端评估,而且效能很重要,请使用EF.Functions.Like

55ooxyrt

55ooxyrt5#

让我们试着在一般情况下解决这个问题。

select ...
 where ... MyField like '%abc%'

我们可以尝试将 * like * 表达式转换为相应 * regular * 表达式:

Like | Description                       |Regular
-------------------------------------------------
   _ | any character  (one and only one) | .
   % | any characters (zero or more)     | .*

实施

// If you want to implement both "*" and "?"
private static String LikeToRegular(String value) {
  return "^" + Regex.Escape(value).Replace("_", ".").Replace("%", ".*") + "$"; 
}

使用方法:

var result list
  .Where(x => Regex.IsMatch(x.myTextColumn, LikeToRegular("%abc%")));

您可能希望在匹配之前将数据转换为string

var result list
  .Where(x => Regex.IsMatch(x.myDate.ToString(), LikeToRegular("%abc%")));

相关问题