Как добавить динамически генерируемое выражение Where для объекта с возможностью навигации в запрос Linq-To-SQL?
Задний план
Мой клиент хотел бы иметь метод отправки массива значений поля (строки), значения (строки) и сравнения (перечисления) для получения их данных.
public class QueryableFilter {
public string Name { get; set; }
public string Value { get; set; }
public QueryableFilterCompareEnum? Compare { get; set; }
}
Моя компания и я никогда раньше не пытались делать что-либо подобное, поэтому моя команда должна найти жизнеспособное решение. Это результат работы над решением с неделей исследований.
Что работает: часть 1
Я создал службу, которая может извлекать данные из нашей таблицы Classroom. Получение данных выполняется в Entity Framework Core посредством LINQ-to-SQL. То, как я написал ниже, работает, если одно из полей, представленных в фильтре, не существует для Класса, но существует для связанной с ним Организации (клиент также хотел иметь возможность поиска среди адресов организации) и имеет возможность навигации свойство.
public async Task<IEnumerable<IExportClassroom>> GetClassroomsAsync(
IEnumerable<QueryableFilter> queryableFilters = null) {
var filters = queryableFilters?.ToList();
IQueryable<ClassroomEntity> classroomQuery = ClassroomEntity.All().AsNoTracking();
// The organization table may have filters searched against it
// If any are, the organization table should be inner joined to all filters are used
IQueryable<OrganizationEntity> organizationQuery = OrganizationEntity.All().AsNoTracking();
var joinOrganizationQuery = false;
// Loop through the supplied queryable filters (if any) to construct a dynamic LINQ-to-SQL queryable
if (filters?.Count > 0) {
foreach (var filter in filters) {
try {
classroomQuery = classroomQuery.BuildExpression(filter.Name, filter.Value, filter.Compare);
} catch (ArgumentException ex) {
if (ex.ParamName == "propertyName") {
organizationQuery = organizationQuery.BuildExpression(filter.Name, filter.Value, filter.Compare);
joinOrganizationQuery = true;
} else {
throw new ArgumentException(ex.Message);
}
}
}
}
// Inner join the classroom and organization queriables (if necessary)
var query = joinOrganizationQuery
? classroomQuery.Join(organizationQuery, classroom => classroom.OrgId, org => org.OrgId, (classroom, org) => classroom)
: classroomQuery;
query = query.OrderBy(x => x.ClassroomId);
IEnumerable<IExportClassroom> results = await query.Select(ClassroomMapper).ToListAsync();
return results;
}
Что работает: часть 2
BuildExpression, который существует в коде является то, что я создал, как таковые (с комнатой для расширения).
public static IQueryable<T> BuildExpression<T>(this IQueryable<T> source, string columnName, string value, QueryableFilterCompareEnum? compare = QueryableFilterCompareEnum.Equal) {
var param = Expression.Parameter(typeof(T));
// Get the field/column from the Entity that matches the supplied columnName value
// If the field/column does not exists on the Entity, throw an exception; There is nothing more that can be done
MemberExpression dataField;
try {
dataField = Expression.Property(param, propertyName);
} catch (ArgumentException ex) {
if (ex.ParamName == "propertyName") {
throw new ArgumentException($"Queryable selection does not have a \"{propertyName}\" field.", ex.ParamName);
} else {
throw new ArgumentException(ex.Message);
}
}
ConstantExpression constant = !string.IsNullOrWhiteSpace(value)
? Expression.Constant(value.Trim(), typeof(string))
: Expression.Constant(value, typeof(string));
BinaryExpression binary = GetBinaryExpression(dataField, constant, compare);
Expression<Func<T, bool>> lambda = (Expression<Func<T, bool>>)Expression.Lambda(binary, param)
return source.Where(lambda);
}
private static Expression GetBinaryExpression(MemberExpression member, ConstantExpression constant, QueryableFilterCompareEnum? comparisonOperation) {
switch (comparisonOperation) {
case QueryableFilterCompareEnum.NotEqual:
return Expression.Equal(member, constant);
case QueryableFilterCompareEnum.GreaterThan:
return Expression.GreaterThan(member, constant);
case QueryableFilterCompareEnum.GreaterThanOrEqual:
return Expression.GreaterThanOrEqual(member, constant);
case QueryableFilterCompareEnum.LessThan:
return Expression.LessThan(member, constant);
case QueryableFilterCompareEnum.LessThanOrEqual:
return Expression.LessThanOrEqual(member, constant);
case QueryableFilterCompareEnum.Equal:
default:
return Expression.Equal(member, constant);
}
}
}
Проблема / Решение моего вопроса
Хотя внутреннее объединение Класса и Организации работает, я бы предпочел не использовать второй набор сущностей для проверки значений, по которым можно перемещаться. Если бы я ввел город в качестве имени фильтра, обычно я бы сделал следующее:
classroomQuery = classroomQuery.Where(x => x.Organization.City == "Atlanta");
Здесь это действительно не работает.
Я попробовал несколько разных методов, чтобы получить то, что ищу:
- Скомпилированная функция, которая возвращала бы Func
- Я изменил его на Expression
>, но мое возвращение не вернуло bool так, как я пытался его реализовать, поэтому это не сработало. - Я изменил способ реализации свойства навигации, но ни одна из моих функций не могла правильно прочитать значение.
В принципе, есть ли способ реализовать следующее так, чтобы LINQ-to-SQL из Entity Framework Core работал? Также приветствуются другие варианты.
classroomQuery = classroomQuery.Where(x => x.Organization.BuildExpression(filter.Name, filter.Value, filter.Compare));
Изменить 01:
При использовании выражения без динамического построителя, например:
IQueryable<ClassroomEntity>classroomQuery = ClassroomEntity.Where(x => x.ClassroomId.HasValue).Where(x => x.Organization.City == "Atlanta").AsNoTracking();
Отладка гласит:
.Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Call System.Linq.Queryable.Where(
.Call System.Linq.Queryable.Where(
.Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ClassroomEntity]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ClassroomEntity]),
'(.Lambda #Lambda1<System.Func`2[ClassroomEntity,System.Boolean]>)),
'(.Lambda #Lambda2<System.Func`2[ClassroomEntity,System.Boolean]>)))
.Lambda #Lambda1<System.Func`2[ClassroomEntity,System.Boolean]>(ClassroomEntity $x)
{
($x.ClassroomId).HasValue
}
.Lambda #Lambda2<System.Func`2[ClassroomEntity,System.Boolean]>(ClassroomEntity $x)
{
($x.Organization).City == "Bronx"
}
Я попытался с помощью динамического построителя получить учителя Класса, который дал мне отладку:
.Lambda #Lambda3<System.Func`2[ClassroomEntity,System.Boolean]>(ClassroomEntity $var1)
{
$var1.LeadTeacherName == "Sharon Candelariatest"
}
Все еще не могу понять, как получить ($var1.Organization) как объект, из которого я читаю.
3 ответа
Если вы можете попросить клиента предоставить полное точечное выражение для свойства. например"Organization.City";
dataField = (MemberExpression)propertyName.split(".")
.Aggregate(
(Expression)param,
(result,name) => Expression.Property(result, name));
Если я получаю вашу формулировку проблемы, вы хотите иметь возможность перемещаться по цепочке свойств навигации.
Если это действительно так, то настоящая проблема - получить навигационные отношения от EF. И вот где EntityTypeExtensions пригодится. GetNavigations() особенно.
Вы можете рекурсивно перемещаться по своим свойствам навигации и создавать выражения доступа к свойствам по мере продвижения:
private static IEnumerable<Tuple<IProperty, Expression>> GetPropertyAccessors(this IEntityType model, Expression param)
{
var result = new List<Tuple<IProperty, Expression>>();
result.AddRange(model.GetProperties()
.Where(p => !p.IsShadowProperty()) // this is your chance to ensure property is actually declared on the type before you attempt building Expression
.Select(p => new Tuple<IProperty, Expression>(p, Expression.Property(param, p.Name)))); // Tuple is a bit clunky but hopefully conveys the idea
foreach (var nav in model.GetNavigations().Where(p => p is Navigation))
{
var parentAccessor = Expression.Property(param, nav.Name); // define a starting point so following properties would hang off there
result.AddRange(GetPropertyAccessors(nav.ForeignKey.PrincipalEntityType, parentAccessor)); //recursively call ourselves to travel up the navigation hierarchy
}
return result;
}
тогда твой BuildExpressionметод, вероятно, можно немного упростить. Обратите внимание, я добавилDbContext как параметр:
public static IQueryable<T> BuildExpression<T>(this IQueryable<T> source, DbContext context, string columnName, string value, QueryableFilterCompareEnum? compare = QueryableFilterCompareEnum.Equal)
{
var param = Expression.Parameter(typeof(T));
// Get the field/column from the Entity that matches the supplied columnName value
// If the field/column does not exists on the Entity, throw an exception; There is nothing more that can be done
MemberExpression dataField;
try
{
var model = context.Model.FindEntityType(typeof(T)); // start with our own entity
var props = model.GetPropertyAccessors(param); // get all available field names including navigations
var reference = props.FirstOrDefault(p => RelationalPropertyExtensions.GetColumnName(p.Item1) == columnName); // find the filtered column - you might need to handle cases where column does not exist
dataField = reference.Item2 as MemberExpression; // we happen to already have correct property accessors in our Tuples
}
catch (ArgumentException)
{
throw new NotImplementedException("I think you shouldn't be getting these anymore");
}
ConstantExpression constant = !string.IsNullOrWhiteSpace(value)
? Expression.Constant(value.Trim(), typeof(string))
: Expression.Constant(value, typeof(string));
BinaryExpression binary = GetBinaryExpression(dataField, constant, compare);
Expression<Func<T, bool>> lambda = (Expression<Func<T, bool>>)Expression.Lambda(binary, param);
return source.Where(lambda);
}
а также GetClassroomsAsync будет выглядеть примерно так:
public async Task<IEnumerable<IExportClassroom>> GetClassroomsAsync(IEnumerable<QueryableFilter> queryableFilters = null)
{
IQueryable<ClassroomEntity> classroomQuery = ClassroomEntity.All().AsNoTracking();
// Loop through the supplied queryable filters (if any) to construct a dynamic LINQ-to-SQL queryable
foreach (var filter in queryableFilters ?? new List<QueryableFilter>())
{
try
{
classroomQuery = classroomQuery.BuildExpression(_context, filter.Name, filter.Value, filter.Compare);
}
catch (ArgumentException ex)
{
// you probably should look at catching different exceptions now as joining is not required
}
}
query = classroomQuery.OrderBy(x => x.ClassroomId);
IEnumerable<IExportClassroom> results = await query.Select(ClassroomMapper).ToListAsync();
return results;
}
Тестируем это
Поскольку вы не предоставили иерархию сущностей, я поэкспериментировал с одной из своих:
public class Entity
{
public int Id { get; set; }
}
class Company: Entity
{
public string CompanyName { get; set; }
}
class Team: Entity
{
public string TeamName { get; set; }
public Company Company { get; set; }
}
class Employee: Entity
{
public string EmployeeName { get; set; }
public Team Team { get; set; }
}
// then i've got a test harness method as GetClassroomsAsync won't compile wothout your entities
class DynamicFilters<T> where T : Entity
{
private readonly DbContext _context;
public DynamicFilters(DbContext context)
{
_context = context;
}
public IEnumerable<T> Filter(IEnumerable<QueryableFilter> queryableFilters = null)
{
IQueryable<T> mainQuery = _context.Set<T>().AsQueryable().AsNoTracking();
// Loop through the supplied queryable filters (if any) to construct a dynamic LINQ-to-SQL queryable
foreach (var filter in queryableFilters ?? new List<QueryableFilter>())
{
mainQuery = mainQuery.BuildExpression(_context, filter.Name, filter.Value, filter.Compare);
}
mainQuery = mainQuery.OrderBy(x => x.Id);
return mainQuery.ToList();
}
}
// --- DbContext
class MyDbContext : DbContext
{
public DbSet<Company> Companies{ get; set; }
public DbSet<Team> Teams { get; set; }
public DbSet<Employee> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=.\\SQLEXPRESS;Database=test;Trusted_Connection=true");
base.OnConfiguring(optionsBuilder);
}
}
// ---
static void Main(string[] args)
{
var context = new MyDbContext();
var someTableData = new DynamicFilters<Employee>(context).Filter(new
List<QueryableFilter> {new QueryableFilter {Name = "CompanyName", Value = "Microsoft" }});
}
С указанным выше и фильтром CompanyName = "Microsoft" EF Core 3.1 сгенерировал мне следующий SQL:
SELECT [e].[Id], [e].[EmployeeName], [e].[TeamId]
FROM [Employees] AS [e]
LEFT JOIN [Teams] AS [t] ON [e].[TeamId] = [t].[Id]
LEFT JOIN [Companies] AS [c] ON [t].[CompanyId] = [c].[Id]
WHERE [c].[CompanyName] = N'Microsoft'
ORDER BY [e].[Id]
Такой подход, кажется, дает желаемый результат, но имеет одну проблему: имена столбцов должны быть уникальными для всех ваших сущностей. С этим, вероятно, можно разобраться, но поскольку я не очень разбираюсь в особенностях вашей модели данных, я бы поручил это вам.
(Отказ от ответственности: я написал код, похожий на этот, но на самом деле я не тестировал код в этом ответе.)
Ваш BuildExpression принимает один запрос (в виде IQueryable<T>) и возвращает другой запрос. Это ограничивает применение всех ваших фильтров к свойству параметра -x.ClassroomId - когда вы действительно хотите применить некоторые из них к свойству свойства параметра - x.Organization.City.
Я бы предложил GetFilterExpression метод, который производит выражение фильтра из некоторого произвольного базового выражения:
private static Expression GetFilterExpression(Expression baseExpr, string columnName, string value, QueryableFilterCompareEnum? compare = QueryableFilterCompareEnum.Equal) {
MemberExpression dataField;
try {
dataField = Expression.Property(baseExpr, columnName);
} catch (ArgumentException ex) {
if (ex.ParamName == "propertyName") {
throw new ArgumentException($"Base expression type does not have a \"{propertyName}\" field.", ex.ParamName);
} else {
throw new ArgumentException(ex.Message);
}
}
if (!string.IsNullOrWhiteSpace(value)) {
value = value.Trim();
}
ConstantExpression constant = Expression.Constant(value, typeof(string));
BinaryExpression binary = GetBinaryExpression(dataField, constant, compare);
return binary;
}
В GetClassroomsAsync, вы можете построить выражение фильтра по исходному ClassroomEntity параметр, или против возвращаемого значения Organization свойство параметра, передав другое выражение:
public async Task<IEnumerable<IExportClassroom>> GetClassroomsAsync(IEnumerable<QueryableFilter> queryableFilters = null) {
var filters = queryableFilters?.ToList();
var param = Expression.Parameter(typeof(ClassroomEntity));
var orgExpr = Expression.Property(param, "Organization"); // equivalent of x.Organization
IQueryable<ClassroomEntity> query = ClassroomEntity.All().AsNoTracking();
if (filters is {}) {
// Map the filters to expressions, applied to the `x` or to the `x.Organization` as appropriate
var filterExpressions = filters.Select(filter => {
try {
return GetFilterExpression(param, filter.Name, filter.Value, filter.Compare);
} catch (ArgumentException ex) {
if (ex.ParamName == "propertyName") {
return GetFilterExpression(orgExpr, filter.Name, filter.Value, filter.Compare);
} else {
throw new ArgumentException(ex.Message);
}
}
});
// LogicalCombined is shown later in the answer
query = query.Where(
Expression.Lambda<Func<ClassroomEntity, bool>>(LogicalCombined(filters))
);
}
query = query.OrderBy(x => x.ClassroomId);
IEnumerable<IExportClassroom> results = await query.Select(ClassroomMapper).ToListAsync();
return results;
}
LogicalCombined занимает несколько bool-возвращает выражения и объединяет их в одно выражение:
private static Expression LogicalCombined(IEnumerable<Expression> exprs, ExpressionType expressionType = ExpressionType.AndAlso) {
// ensure the expression type is a boolean operator
switch (expressionType) {
case ExpressionType.And:
case ExpressionType.AndAlso:
case ExpressionType.Or:
case ExpressionType.OrElse:
case ExpressionType.ExclusiveOr:
break;
default:
throw new ArgumentException("Invalid expression type for logically combining expressions.");
}
Expression? final = null;
foreach (var expr in exprs) {
if (final is null) {
final = expr;
continue;
}
final = Expression.MakeBinary(expressionType, final, expr);
}
return final;
}
Некоторые предложения:
Как я написал, GetFilterExpression это staticметод. Поскольку все аргументы (кроме базового выражения) исходят изQueryableFilter, вы можете подумать о том, чтобы сделать его методом экземпляра из QueryableFilter.
Я бы также предложил изменить GetBinaryExpression использовать словарь для отображения QueryableFilterCompareEnum на встроенный ExpressionType. Затем реализацияGetBinaryExpression это просто оболочка для встроенного Expression.MakeBinary метод:
private static Dictionary<QueryableFilterCompareEnum, ExpressionType> comparisonMapping = new Dictionary<QueryableFilterCompareEnum, ExpressionType> {
[QueryableFilterCompareEnum.NotEqual] = ExpressionType.NotEqual,
[QueryableFilterCompareEnum.GreaterThan] = ExpressionType.GreaterThan,
[QueryableFilterCompareEnum.GreaterThanOrEqual] = ExpressionType.GreaterThanOrEqual,
[QueryableFilterCompareEnum.LessThan] = ExpressionType.LessThan,
[QueryableFilterCompareEnum.LessThanOrEqual] = ExpressionType.LessThanOrEqual,
[QueryableFilterCompareEnum.Equal] = ExpressionType.Equal
}
private static Expression GetBinaryExpression(MemberExpression member, ConstantExpression constant, QueryableFilterCompareEnum? comparisonOperation) {
comparisonOperation = comparisonOperation ?? QueryableFilterCompareEnum.Equal;
var expressionType = comparisonMapping[comparisonOperation];
return Expression.MakeBinary(
expressionType,
member,
constant
);
}
И то и другое GetFilterExpression а также GetClassroomsAsync обрабатывать возможность того, что указанное свойство не существует ни на одном ClassroomEntity или OrganizationEntity, пытаясь построить выражение доступа к члену и обработав возникшее исключение.
Было бы проще использовать отражение, чтобы проверить, существует ли свойство для любого типа или нет.
Более того, вы можете рассмотреть возможность сохранения статического HashSet<string> со всеми допустимыми именами полей и проверьте их.