How Can I Add a Dynamically Generated Where Expression for a Navigable Object in My Linq-To-SQL Query?

Question

Background

My client would like to have a method of sending over an array of field (string), value (string), and comparison (enum) values in order to retrieve their data.

public class QueryableFilter {
    public string Name { get; set; }
    public string Value { get; set; }
    public QueryableFilterCompareEnum? Compare { get; set; }
}

My company and I have never attempted to do anything like this before, so it is up to my team to come up with a viable solution. This is the result of working on a solution with a week or so of research.

What Works: Part 1

I have created a service that is able to retrieve the data from our table Classroom. Retrieval of the data is done in Entity Framework Core by way of LINQ-to-SQL. The way I have written below works if one of the fields that are supplied in the filter doesn't exist for Classroom but does exist for its related Organization (the client wanted to be able to search among organization addresses as well) and has a navigatable property.

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;
}

What Works: Part 2

The BuildExpression that exists in code is something that I created as such (with room for expansion).

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);
        }
    }
}

The Problem / Getting Around to My Question

While the inner join on the Classroom and Organization works, I'd rather not have to pull in a second entity set for checking values that are navigatable. If I typed in a City as my filter name, normally I would do this:

classroomQuery = classroomQuery.Where(x => x.Organization.City == "Atlanta");

That doesn't really work here.

I have tried a couple of different methods in order to get me what I'm looking for:

• A compiled function that would return Func<T, bool>, but when put through LINQ-to-SQL, the query did not include it.
• I changed it to an Expression<Func<T, bool>>, but my return didn't return a bool in the way I attempted to implement it, so that didn't work.
• I switched the way that I was implementing the navigation property, but none of my functions would read the value properly.

Basically, is there some way that I can implement the following in a way that LINQ-to-SQL from Entity Framework Core will work? Other options are welcome as well.

classroomQuery = classroomQuery.Where(x => x.Organization.BuildExpression(filter.Name, filter.Value, filter.Compare));

Edit 01:

When using the expression without the dynamic builder like so:

IQueryable<ClassroomEntity>classroomQuery = ClassroomEntity.Where(x => x.ClassroomId.HasValue).Where(x => x.Organization.City == "Atlanta").AsNoTracking();

The debug reads:

.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"
}

I tried with the dynamic builder to get the Classroom teacher, which gave me a debug of:

.Lambda #Lambda3<System.Func`2[ClassroomEntity,System.Boolean]>(ClassroomEntity $var1)
{
    $var1.LeadTeacherName == "Sharon Candelariatest"
}

Still cannot figure out how to get ($var1.Organization) as the entity I'm reading from.

Answer 1

(Disclaimer: I've written code similar to this, but I haven't actually tested the code in this answer.)

Your BuildExpression takes one query (in the form of an IQueryable<T>) and returns another query. This constrains all your filters to be applied to the property of the parameter -- x.ClassroomId -- when you actually want to apply some of them to a property of a property of the parameter -- x.Organization.City.

I would suggest a GetFilterExpression method, which produces the filter expression off of some arbitrary base expression:

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;
}

Within GetClassroomsAsync, you can either build the filter expression against the original ClassroomEntity parameter, or against the returned value of the Organization property on the parameter, by passing in a different expression:

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 takes multiple bool-returning expressions and combines them into a single expression:

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;
}

---

Some suggestions:

As I've written it, GetFilterExpression is a static method. Since all the arguments (except the base expression) come from QueryableFilter, you might consider making it an instance method off of QueryableFilter.

---

I would also suggest changing GetBinaryExpression to use a dictionary to map from QueryableFilterCompareEnum to the built-in ExpressionType. Then, the implementation of GetBinaryExpression is just a wrapper for the built-in Expression.MakeBinary method:

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
    );
}

---

Both GetFilterExpression and GetClassroomsAsync handle the possibility that the specified property doesn't exist on either ClassroomEntity or OrganizationEntity, by trying to construct the member-access expression and handling the thrown exception.

It might be clearer to use reflection to test if the property exists on either type or not.

More, you might consider storing a static HashSet<string> with all the valid fieldnames, and check against that.

Answer 2

If you can ask the client to supply the full dot notation expression for the property. eg "Organization.City";

    dataField = (MemberExpression)propertyName.split(".")
        .Aggregate(
            (Expression)param,
            (result,name) => Expression.Property(result, name));

Answer 3

If I am getting your problem statement, you want to be able to travel up the navigation property chain.

If that is indeed the case the real challenge is getting the navigation relationships from EF. And this is where EntityTypeExtensions comes in handy. GetNavigations() in particular.

You could recursively travel up your navigation properties and build property accessor expressions as you go:

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;
        }

then your BuildExpression method can probably be a bit simplified. Notice, I added DbContext as parameter:

        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);
        }

and GetClassroomsAsync would look something like this:

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;
}

Testing it out

Since you didn't supply entity hierarchy, I experimented on one of my own:

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" }});
}

With the above, and a filter CompanyName = "Microsoft" EF Core 3.1 generated me the following 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]

This approach seems to produce desired result but has one issue: column names must be unique across all your entities. This likely can be dealt with but since I don't know much specifics of your data model I'd defer it to you.