如何检查字符串是否包含实体框架中列表中的任何字符串
本文关键字:字符串 框架 实体 列表 任何 包含 是否 何检查 检查 | 更新日期: 2023-09-27 18:33:03
我正在尝试搜索数据库以查看字符串是否包含搜索词列表的元素。
var searchTerms = new List<string> { "car", "232" };
var result = context.Data.Where(data => data.Name.Contains(searchTerms) ||
data.Code.Contains(searchTerms));
如果 searchTerm 是一个字符串,这将起作用,但我一直在尝试让它与字符串列表一起使用。
本质上我需要SQL会说
SELECT * FROM Data
WHERE Name LIKE '%car%'
OR Name LIKE '%232%'
OR Code LIKE '%car%'
OR Code LIKE '%232%'
linq 列表包含列表中的任何内容似乎是我能找到的情况最接近的东西。
Where(data => searchTerms.Contains(data.Name) || searchTerms.Contains(data.Code)
只会将完全匹配项带回搜索词列表。
我也尝试在实体框架中搜索多个关键字搜索,并用尽了这项工作。有什么方法可以实现我的目标吗?
尝试使用Any
方法,我不确定它是否受支持,但值得尝试:
var result = context.Data.Where(data => searchTerms.Any(x => data.Name.Contains(x)) ||
searchTerms.Any(x => data.Code.Contains(x));
如果这为您提供了NotSupportedException
则可以在Where
之前添加AsEnumerable
以获取所有记录并在内存而不是数据库中执行查询。
我迟到了,但使用 SearchExtensions nuget 包,您可以执行以下操作
var result = context.Data.Search(x => x.Name, x => x.Code).Containing(searchTerms);
这将构建一个表达式树,因此仍将在服务器上(而不是在内存中(执行查询,并且基本上将运行上面所需的SQL
下面是一个功能齐全的示例,介绍如何使用多个关键字实现不同类型的搜索。
这个例子特别针对@Hamza坎扎达关于Pomelo.EntityFrameworkCore.MySql
的类似问题。
它执行类似于@NinjaNye命名的库的操作。
<小时 />
最简单的方法是EqualsQuery()
,它只是根据关键字的完全匹配来测试数据库字段(尽管大小写无关紧要(。这就是@Mohsen Esmailpour的建议。
它生成类似于以下内容的 SQL:
SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
WHERE LOWER(`i`.`Name`) IN ('cookie', 'berry')
ORDER BY `i`.`IceCreamId`
但是,这对于您的情况可能还不够,因为您不是在寻找完全匹配,而是希望返回包含仅包含关键字(可能还有其他单词(的字段的行。
<小时 />AndContainsQuery()
使用了第二种方法,它仍然非常简单,但做了一些稍微不同的事情。它只返回包含所有关键字(可能还有其他单词(的结果。
它生成类似于以下内容的 SQL:
set @__keyword_0 = 'Cookie';
set @__keyword_1 = 'choco';
SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
WHERE
(LOCATE(LCASE(@__keyword_0), LCASE(`i`.`Name`)) > 0) AND
(LOCATE(LCASE(@__keyword_1), LCASE(`i`.`Name`)) > 0)
ORDER BY `i`.`IceCreamId`;
这不是您想要的,但我认为展示它也很好,因为它非常简单,无需手动构建表达式树即可完成。
<小时 />最后,orContainsQuery()
使用第三种方法,并手动构建表达式树的一部分。它构造多个嵌套OR
表达式的WHERE
表达式的主体。这就是你想要的。
它生成类似于以下内容的 SQL:
set @__keyword_0 = 'berry';
set @__keyword_1 = 'Cookie';
SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
WHERE
(LOCATE(LCASE(@__keyword_0), LCASE(`i`.`Name`)) > 0) OR
(LOCATE(LCASE(@__keyword_1), LCASE(`i`.`Name`)) > 0)
ORDER BY `i`.`IceCreamId`;
<小时 />以下是功能齐全的控制台项目:
using System;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Storage;
namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3306;user=root;password=;database=so60914868",
b => b.ServerVersion(new ServerVersion("8.0.20-mysql")))
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla"},
new IceCream {IceCreamId = 2, Name = "Berry"},
new IceCream {IceCreamId = 3, Name = "Strawberry"},
new IceCream {IceCreamId = 4, Name = "Berry & Fruit"},
new IceCream {IceCreamId = 5, Name = "cookie"},
new IceCream {IceCreamId = 6, Name = "Chocolate chip cookie"},
new IceCream {IceCreamId = 7, Name = "Choco-Cookie & Dough"});
});
}
}
internal class Program
{
private static void Main()
{
using (var context = new Context())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
EqualsQuery();
AndContainsQuery();
OrContainsQuery();
}
private static void EqualsQuery()
{
//
// This will find only matches that match the word exactly (though case-insensitive):
//
using var context = new Context();
var keywords = new[] {"Cookie", "berry"}
.Select(s => s.ToLower())
.ToArray();
var equalsResult = context.IceCreams
.Where(i => keywords.Contains(i.Name.ToLower()))
.OrderBy(i => i.IceCreamId)
.ToList();
Debug.Assert(equalsResult.Count == 2);
Debug.Assert(
equalsResult[0]
.Name == "Berry");
Debug.Assert(
equalsResult[1]
.Name == "cookie");
}
private static void AndContainsQuery()
{
//
// This will find matches, that contain ALL keywords (and other words, case-insensitive):
//
using var context = new Context();
var keywords = new[] {"Cookie", "choco"};
var andContainsQuery = context.IceCreams.AsQueryable();
foreach (var keyword in keywords)
{
andContainsQuery = andContainsQuery.Where(i => i.Name.Contains(keyword, StringComparison.CurrentCultureIgnoreCase));
}
var andContainsResult = andContainsQuery
.OrderBy(i => i.IceCreamId)
.ToList();
Debug.Assert(andContainsResult.Count == 2);
Debug.Assert(
andContainsResult[0]
.Name == "Chocolate chip cookie");
Debug.Assert(
andContainsResult[1]
.Name == "Choco-Cookie & Dough");
}
private static void OrContainsQuery()
{
//
// This will find matches, that contains at least one keyword (and other words, case-insensitive):
//
using var context = new Context();
var keywords = new[] {"Cookie", "berry"};
// The lambda parameter.
var iceCreamParameter = Expression.Parameter(typeof(IceCream), "i");
// Build the individual conditions to check against.
var orConditions = keywords
.Select(keyword => (Expression<Func<IceCream, bool>>) (i => i.Name.Contains(keyword, StringComparison.OrdinalIgnoreCase)))
.Select(lambda => (Expression) Expression.Invoke(lambda, iceCreamParameter))
.ToList();
// Combine the individual conditions to an expression tree of nested ORs.
var orExpressionTree = orConditions
.Skip(1)
.Aggregate(
orConditions.First(),
(current, expression) => Expression.OrElse(expression, current));
// Build the final predicate (a lambda expression), so we can use it inside of `.Where()`.
var predicateExpression = (Expression<Func<IceCream, bool>>)Expression.Lambda(
orExpressionTree,
iceCreamParameter);
// Compose and execute the query.
var orContainsResult = context.IceCreams
.Where(predicateExpression)
.OrderBy(i => i.IceCreamId)
.ToList();
Debug.Assert(orContainsResult.Count == 6);
Debug.Assert(orContainsResult[0].Name == "Berry");
Debug.Assert(orContainsResult[1].Name == "Strawberry");
Debug.Assert(orContainsResult[2].Name == "Berry & Fruit");
Debug.Assert(orContainsResult[3].Name == "cookie");
Debug.Assert(orContainsResult[4].Name == "Chocolate chip cookie");
Debug.Assert(orContainsResult[5].Name == "Choco-Cookie & Dough");
}
}
}
此请求将在数据库端执行
var searchTerms = new List<string> { "car", "232" };
Expressions<Func<Data, bool>> expression = it => false;
foreach(var searchTerm in searchTerms)
{
expression = expression.Or(it => it.Name.Contains(searchTerm));
//you can implement your own 'Or' extensions method,
//or use third-party libraries, i.e. LinqKit
}
var result = context.Data.Where(expression);
您还可以使用规范模式,以确保代码纯度