是否有办法使这个linq2sql更干?

Question

我想删除这段代码中的重复部分。是否有可能编写一个函数来清理任何表nvarchar列与指定的字符串(或字符)数组?该查询将以ef core结尾执行。

我尝试了VS2022重构工具来提取方法，但它提取了整个Where()。我想"建造"。替换链。这可能吗?

var request = GetRequestValuesFromApi();
var query = _context.Persons.AsQueryable();
if (!string.IsNullOrWhiteSpace(request.Firstname))
{
string firstname = request.Firstname.Replace("-", "").Replace(" ", "").Replace("’", "").Replace("'", "");
query = query.Where(x => EF.Functions.Collate(x.Firstname!, "binary_ai").Replace("-", "").Replace(" ", "").Replace("’", "").Replace("'", "").Contains(firstname));
}
if (!string.IsNullOrWhiteSpace(request.Lastname))
{
string lastname = request.Lastname.Replace("-", "").Replace(" ", "").Replace("’", "").Replace("'", "");
query = query.Where(x => EF.Functions.Collate(x.Lastname!, "binary_ai").Replace("-", "").Replace(" ", "").Replace("’", "").Replace("'", "").Contains(lastname));
}
// ...
var results = query.ToList();

Answer 1

如果您愿意使用LINQKit，您可以在查询中嵌入Expression变量，并在执行查询之前将它们展开:

var query = _context.Persons.AsQueryable().AsExpandable();
Expression<Func<string,string>> FixNameEFn = (string name) => name.Replace("-", "").Replace(" ", "").Replace("’", "").Replace("'", "");
var FixNameFn = FixNameEFn.Compile();
if (!string.IsNullOrWhiteSpace(request.Firstname)) {
string firstname = FixNameFn(request.Firstname);
query = query.Where(x => FixNameEFn.Invoke(EF.Functions.Collate(x.Firstname!, "binary_ai")).Contains(firstname));
}
if (!string.IsNullOrWhiteSpace(request.Lastname)) {
string lastname = FixNameFn(request.Lastname);
query = query.Where(x => FixNameEFn.Invoke(EF.Functions.Collate(x.Lastname!, "binary_ai")).Contains(lastname));
}

你可以在没有LINQKit的情况下做到这一点，但它将涉及将特殊操作推入自定义Where方法(例如，像WhereNameContains)，然后构建或修改Expression<Func<>>并在常规Where调用中使用它。但这也允许你删除EF.Functions.Collate的重复。