SQL LINQ Evaluate逗号分隔字符串包含值

Question

我正在使用EFCore Linq，遇到了一个问题。我有一个存储逗号分隔字符串的表，我需要在where子句中使用它来只筛选那些包含特定值的字符串。这是我的Linq:

var volunteers = context.Volunteers
.Where(x => x.StatusId == 1
&& x.RoleIds.Split(',', StringSplitOptions.None).Contains("1")
).ToList();

该列为RoleId。我收到的错误是Linq查询无法翻译。我需要在这里做什么？谢谢

Answer 1

如果您可以使用LINQKit(或推出自己的重点版本(，您可以创建扩展来为您处理测试：

public static class IQueryableExt { // using LINQKit
// string fieldExpr(T row) - function returning multiple value string field to test
// delimiter - string separator between values in test field
// value - string value to find in values of test field
// dbq.Where(r => fieldExpr(r).Split(delimiter).Contains(value))
public static IQueryable<T> WhereSplitContains<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, string value) {
var pred = PredicateBuilder.New<T>(r => fieldExpr.Invoke(r) == value);
pred = pred.Or(r => fieldExpr.Invoke(r).StartsWith(value + delimiter));
pred = pred.Or(r => fieldExpr.Invoke(r).EndsWith(delimiter + value));
pred = pred.Or(r => fieldExpr.Invoke(r).Contains(delimiter + value + delimiter));
return dbq.Where((Expression<Func<T, bool>>)pred.Expand());
}
// values - string values, one of which to find in values of test field
// string fieldExpr(T row) - function returning multiple value string field to test
// delimiter - string separator between values in test field
// dbq.Where(r => values.Any(value => fieldExpr(r).Split(delimiter).Contains(value)))
public static IQueryable<T> WhereAnySplitContains<T>(this IQueryable<T> dbq, IEnumerable<string> values, Expression<Func<T, string>> fieldExpr, string delimiter) {
var pred = PredicateBuilder.New<T>();
foreach (var value in values) {
pred = pred.Or(r => fieldExpr.Invoke(r) == value);
pred = pred.Or(r => fieldExpr.Invoke(r).StartsWith(value + delimiter));
pred = pred.Or(r => fieldExpr.Invoke(r).EndsWith(delimiter + value));
pred = pred.Or(r => fieldExpr.Invoke(r).Contains(delimiter + value + delimiter));
}
return dbq.Where((Expression<Func<T, bool>>)pred.Expand());
}
public static IQueryable<T> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
dbq.WhereAnySplitContains(values, fieldExpr, delimiter);
public static IQueryable<T> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, params string[] values) =>
dbq.WhereAnySplitContains(values, fieldExpr, delimiter);
// values - string values, all of which to find in values of test field
// string fieldExpr(T row) - function returning multiple value string field to test
// delimiter - string separator between values in test field
// dbq.Where(r => values.All(value => fieldExpr(r).Split(delimiter).Contains(value)))
public static IQueryable<T> WhereAllSplitContains<T>(this IQueryable<T> dbq, IEnumerable<string> values, Expression<Func<T, string>> fieldExpr, string delimiter) {
var pred = PredicateBuilder.New<T>();
foreach (var value in values) {
var subPred = PredicateBuilder.New<T>(r => fieldExpr.Invoke(r) == value);
subPred = subPred.Or(r => fieldExpr.Invoke(r).StartsWith(value + delimiter));
subPred = subPred.Or(r => fieldExpr.Invoke(r).EndsWith(delimiter + value));
subPred = subPred.Or(r => fieldExpr.Invoke(r).Contains(delimiter + value + delimiter));
pred = pred.And(subPred);
}
return dbq.Where((Expression<Func<T, bool>>)pred.Expand());
}
public static IQueryable<T> WhereSplitContainsAll<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
dbq.WhereAllSplitContains(values, fieldExpr, delimiter);
public static IQueryable<T> WhereSplitContainsAll<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, params string[] values) =>
dbq.WhereAllSplitContains(values, fieldExpr, delimiter);
}

有了这些扩展，您的查询将是：

var volunteers = context.Volunteers.Where(x => x.StatusId == 1)
.WhereSplitContains(r => r.RoleIds, ",", "1")
.ToList();

如果你有多个值，你可以使用其他变体：

var volunteers = context.Volunteers.Where(x => x.StatusId == 1)
.WhereSplitContainsAny(r => r.RoleIds, ",", "1", "2")
.ToList();