如何从efcore中的字符串中选择多个

Question

假设有一个调色板

+----+--------+
| id |  name  |
+----+--------+
| 1  | pa     |
| 2  | pb     |
+----+--------+

颜色。

+----+------+------------+
| id | name | palette_id |
+----+------+------------+
| 1  | ca   |  1         |
| 2  | cb   |  2         |
+----+------+------------+

要选择和过滤我可以使用的调色板：

_dbContext.Palettes.Where(p => p.Colors.Any(x => x.Name.Contains("ca"))

然而，我想用一个字符串来构造它。给定像Colors.Name和ca这样的字符串，我如何创建一个efcore表达式，该表达式返回颜色名称与ca匹配的所有调色板？

它的用例是，我有一个过滤器efcore扩展，它接受一个字符串并将其转换为efcore表达式。

_dbContext.Palettes.Filter("Colors.Name contains ca")...

Answer 1

我实现了两个方法FilterContains和FilterEquals。我认为扩展它们会很容易。

函数负责任何嵌套级别并生成适当的过滤器，但它需要通过DbContext才能使用模型信息：

_dbContext.Palettes.FilterContains(_dbContext, "Colors.Name", "ca")
.ToList();
_dbContext.Palettes.FilterEquals(_dbContext, "Colors.Name", "ca")
.ToList();

但它也可以处理这样的事情：

_dbContext.Users.FilterContains(_dbContext, "Country.Regions.SubRegions.Name", "ca")
.ToList();

以及实施：

public static class DynamicQueryableExtensions
{
public static IQueryable<T> FilterContains<T>(this IQueryable<T> query, DbContext context, string propPath, string value)
{
return FilterQuery(query, context.Model, propPath, propExpression =>
Expression.Call(EnsureString(propExpression), nameof(string.Contains), Type.EmptyTypes,
Expression.Constant(value)));
}
public static IQueryable<T> FilterEquals<T>(this IQueryable<T> query, DbContext context, string propPath, object? value)
{
return FilterQuery(query, context.Model, propPath, propExpression =>
{
if (value == null)
{
var propType = propExpression.Type;
if (propType.IsValueType)
{
propExpression = Expression.Convert(propExpression, typeof(Nullable<>).MakeGenericType(propType));
}
}
else if (propExpression.Type != value.GetType())
{
value = Convert.ChangeType(value, propExpression.Type);
}
return Expression.Equal(propExpression, Expression.Constant(value, propExpression.Type));
});
}
private static IQueryable<T> FilterQuery<T>(IQueryable<T> query, IModel model, string propPath,
Func<Expression, Expression> filterFactory)
{
var propNames = propPath.Split('.');
var entityParameter = Expression.Parameter(typeof(T), "e");
var filter = BuildFilter(entityParameter, model, propNames, 0, filterFactory);
var filterLambda = Expression.Lambda<Func<T, bool>>(filter, entityParameter);
return query.Where(filterLambda);
}
private static Expression BuildFilter(Expression obj, IModel model, string[] propPath, int currentIndex, Func<Expression, Expression> predicateFactory)
{
var entityType = model.FindEntityType(obj.Type);
var propName = propPath[currentIndex];
var prop = entityType.FindProperty(propName);
Expression filter;
if (prop == null)
{
var navigation = entityType.GetNavigations().FirstOrDefault(n => n.Name == propName);
if (navigation == null)
throw new InvalidOperationException($"Property '{propName}' not found in type '{obj.Type}'");
var navigationAccess = Expression.MakeMemberAccess(obj, navigation.PropertyInfo);
if (navigation.IsCollection)
{
var targetType = navigation.TargetEntityType.ClrType;
var nParam = Expression.Parameter(targetType, "n");
var anyFilter = BuildFilter(nParam, model, propPath, currentIndex + 1, predicateFactory);
filter = Expression.Call(typeof(Enumerable), nameof(Enumerable.Any), new[] { targetType }, navigationAccess, Expression.Lambda(anyFilter, nParam));
}
else
{
filter = BuildFilter(navigationAccess, model, propPath, currentIndex + 1, predicateFactory);
}
}
else
{
var propAccess = Expression.MakeMemberAccess(obj, prop.PropertyInfo);
filter = predicateFactory(propAccess);
}
return filter;
}
// For safe conversion to string
private static Expression EnsureString(Expression expression)
{
if (expression.Type == typeof(string))
return expression;
if (expression.Type != typeof(object))
expression = Expression.Convert(expression, typeof(object));
expression = Expression.Call(_toStringMethod, expression);
return expression;
}
private static MethodInfo _toStringMethod = typeof(Convert).GetMethods()
.Single(m =>
m.Name == nameof(Convert.ToString) && m.GetParameters().Length == 1 &&
m.GetParameters()[0].ParameterType == typeof(object)
);
}