c# LINQ中的多短语搜索无法转换为SQL

Question

我正试图编写一个半高级的LINQ到SQL查询来搜索我在。net 6项目中的实体。我想让用户能够输入诸如"用户搜索"或"替代查找"之类的东西;变成了搜索。在处理了用户输入之后，我的LINQ查询设置如下:

bool isNameSearch = true;
bool isNoteSearch = true;
List<List<string>> _multiSearchList = new()
{
new List<string>() { "user", "search", "a" },
new List<string>() { "alternative", "lookup", "b" },
new List<string>() { "seperate", "query", "from", "joe" }
};

var _query = (from tblHeader in _DbContext.batches
where tblHeader.isDeleted != true
select tblHeader)
_query = _query.Where(x => 
_multiSearchList.Any(y =>
y.All(z => 
(isNameSearch && x.Name.Contains(z)) ||
(isNoteSearch && x.Note.Contains(z))
)
)
);
var _results = await _query.ToListAsync();
var _totalCount = await _query.CountAsync();

问题是，尽管这个查询与本地集合一起工作，但它不能转换为sql。有办法做到这一点吗?

Answer 1

由于EF Core不支持本地集合查询(Contains除外)，我建议使用具有PredicateBuilder的LINQKit。

然后您可以为您的特定情况构建谓词:

bool isNameSearch = true;
bool isNoteSearch = true;
List<List<string>> _multiSearchList = new()
{
new List<string>() { "user", "search", "a" },
new List<string>() { "alternative", "lookup", "b" },
new List<string>() { "seperate", "query", "from", "joe" }
};

var _query = (from tblHeader in _DbContext.batches
where tblHeader.isDeleted != true
select tblHeader)
var mainPredicate = PredicateBuilder.New(_query);
foreach (var sentense in _multiSearchList)
[
var predicate = PredicateBuilder.New(_query);
foreach(var word in sentense)
[
var subPredicate = PredicateBuilder.New(_query);

if (isNameSearch)
subPredicate = subPredicate.Or(x => x.Name.Contains(word));
if (isNoteSearch)
subPredicate = subPredicate.Or(x => x.Note.Contains(word));
predicate = predicate.And(subPredicate)
]
mainPredicate = mainPredicate.Or(predicate);
]
_query =  _query.Where(mainPredicate);