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.
