I have been trying to create dynamically generated expression trees for filtering models in my database (PSQL, using Npgsql with EF Core). Yet when I try to filter a list of Player models against a list of ids (int ids of Hero model), I get an exception
System.ArgumentNullException: Value cannot be null. (Parameter 'parameter')
I have the following model being mapped:
public class Player : IProfile
{
public Guid Id { get; set; }
public Guid UserId { get; set; }
public string? Name { get; set; }
public string? Description { get; set; }
public bool? Displayed { get; set; }
public int? PositionId { get; set; }
public Position? Position { get; set; }
public DateTime UpdatedAt { get; set; }
public ICollection<Hero> Heroes { get; protected set; } = [];
public ICollection<Team> Teams { get; protected set; } = [];
public ICollection<TeamPlayer> TeamPlayers { get; protected set; } = [];
}
Expression tree is generated and then used in Where in this function depending on filtering parameters provided:
public static IQueryable<Player> FilterWith(this IQueryable<Player> query, PlayerConditions queryConfig)
{
var parameter = Expression.Parameter(typeof(Player), "profile");
Expression expr = Expression.Constant(true);
if (queryConfig.NameFilter != null)
{
var condExpr = GetStringFilteringExpression<Player>(queryConfig.NameFilter, "Name", parameter);
if (condExpr != null)
expr = Expression.AndAlso(expr, condExpr);
}
// ...
if (queryConfig.HeroFilter != null)
{
var condExpr = GetValueListFilteringOnListExpression<Player, Hero, int>(queryConfig.HeroFilter, "Heroes", "Id", parameter);
if (condExpr != null)
expr = Expression.AndAlso(expr, condExpr);
}
// ...
Expression<Func<Player, bool>> finalLambda = Expression.Lambda<Func<Player, bool>>(expr, parameter);
return query.Where(finalLambda);
}
The exception occurs when trying to filter the players based on a list of Hero ids passed.
Here's how the HeroFilter expression is created:
public static Expression? GetValueListFilteringOnListExpression<TProfile, TListItem, TListItemProp>(ValueFilter<TListItemProp>? filter, string listPropertyName, string listItemPropertyName, ParameterExpression parameter) where TProfile : class, IProfile
{
var listType = typeof(ICollection<TListItem>);
var property = typeof(TProfile).GetProperty(listPropertyName);
var listItemProperty = typeof(TListItem).GetProperty(listItemPropertyName);
if (filter == null || property == null || property.PropertyType != listType || listItemProperty == null || listItemProperty.PropertyType != typeof(TListItemProp))
return null;
var listItemType = typeof(TListItem);
var valueList = Expression.Constant(filter.ValueList, typeof(ICollection<TListItemProp>));
var memberAccess = Expression.MakeMemberAccess(parameter, property);
var intersectByMethodInfo = typeof(Enumerable)
.GetMethods()
.Where(x => x.Name == "IntersectBy")
.Single(x => x.GetParameters().Length == 3)
.MakeGenericMethod(typeof(TListItem), typeof(TListItemProp));
var countMethod = typeof(Enumerable)
.GetMethods(BindingFlags.Static | BindingFlags.Public)
.First(m => m.Name == "Count" && m.GetParameters().Length == 1)
.MakeGenericMethod(typeof(TListItem));
var listItemParameter = Expression.Parameter(listItemType, "li");
var listItemPropertyEx = Expression.MakeMemberAccess(listItemParameter, listItemProperty);
var listItemPropertyLambda = Expression.Lambda(listItemPropertyEx, listItemParameter);
//var countProperty = typeof(ICollection<>).MakeGenericType(typeof(TListItemProp)).GetProperty("Count");
var intersectByExpression = Expression.Call(intersectByMethodInfo, memberAccess, valueList, listItemPropertyLambda);
//var intersectedCount = Expression.Property(intersectByExpression, countProperty);
var intersectedCount = Expression.Call(countMethod, intersectByExpression);
Expression? finalExpression = null;
switch (filter.FilterType)
{
case ValueListFilterType.Exact:
{
var valueCount = Expression.Constant(filter.ValueList.Count);
finalExpression = Expression.Equal(intersectedCount, valueCount);
break;
}
case ValueListFilterType.Including:
{
var valueCount = Expression.Constant(filter.ValueList.Count);
finalExpression = Expression.GreaterThanOrEqual(intersectedCount, valueCount);
break;
}
case ValueListFilterType.Excluding:
{
var valueCount = Expression.Constant(0);
finalExpression = Expression.Equal(intersectedCount, valueCount);
break;
}
case ValueListFilterType.Any:
{
var valueCount = Expression.Constant(0);
finalExpression = Expression.GreaterThan(intersectedCount, valueCount);
break;
}
default:
break;
}
return finalExpression;
}
I am still figuring out how to debug LINQ, but I've tested my filtering function against a List of Players in memory directly and it works! It filters correctly. So, after looking around a little more I've discovered that not all C# Collection functions have translations to SQL syntax in Npgsql (seemingly it's only Collections.Contains). Is that my problem here? If so, what's the best way to perform set operations with navigational properties?
I did it. I was using IEnumerable methods, when LINQ to SQL needs IQueryable, a cast to IQueryable with AsQueryable was all that was needed (and methods being from Queryable):
public static Expression? GetValueListFilteringOnListExpression<TProfile, TListItem, TListItemProp>(ValueFilter<TListItemProp>? filter, string listPropertyName, string listItemPropertyName, ParameterExpression parameter) where TProfile : class, IProfile
{
var listType = typeof(ICollection<TListItem>);
var property = typeof(TProfile).GetProperty(listPropertyName);
var listItemProperty = typeof(TListItem).GetProperty(listItemPropertyName);
if (filter == null || property == null || property.PropertyType != listType || listItemProperty == null || listItemProperty.PropertyType != typeof(TListItemProp)) return null;
var valueList = Expression.Constant(filter.ValueList, typeof(ICollection<TListItemProp>));
var memberAccess = Expression.Property(parameter, listPropertyName);
var asQueryable = typeof(Queryable)
.GetMethods()
.First(m => m.Name == "AsQueryable" && m.IsGenericMethodDefinition);
var asQueryableListItemProp = asQueryable.MakeGenericMethod(typeof(TListItemProp));
var asQueryableListItem = asQueryable.MakeGenericMethod(typeof(TListItem));
var select = typeof(Queryable)
.GetMethods()
.Where(m => m.Name == "Select" && m.IsGenericMethodDefinition)
.Single(m =>
{
var parameters = m.GetParameters();
if (parameters.Length != 2)
return false;
var delegateType = parameters[1].ParameterType.GetGenericArguments()[0];
return delegateType.IsGenericType && delegateType.GetGenericTypeDefinition() == typeof(Func<,>);
}).MakeGenericMethod(typeof(TListItem), typeof(TListItemProp));
var intersect = typeof(Queryable)
.GetMethods()
.Where(x => x.Name == "Intersect")
.Single(x => x.GetParameters().Length == 2)
.MakeGenericMethod(typeof(TListItemProp));
var count = typeof(Queryable)
.GetMethods()
.First(m => m.Name == "Count" && m.GetParameters().Length == 1)
.MakeGenericMethod(typeof(TListItemProp));
var listItemParameter = Expression.Parameter(typeof(TListItem), "li");
var listItemPropertyEx = Expression.Property(listItemParameter, listItemPropertyName);
var listItemPropertyLambda = Expression.Lambda(listItemPropertyEx, listItemParameter);
var asQueryableExpression = Expression.Call(asQueryableListItem, memberAccess);
var valueListAsQueryable = Expression.Call(asQueryableListItemProp, valueList);
var selectExpression = Expression.Call(select, asQueryableExpression, listItemPropertyLambda);
var intersectExpression = Expression.Call(intersect, selectExpression, valueListAsQueryable);
var intersectedCount = Expression.Call(count, intersectExpression);
Expression? finalExpression = null;
switch (filter.FilterType)
{
case ValueListFilterType.Including:
{
var valueCount = Expression.Constant(filter.ValueList.Count);
finalExpression = Expression.GreaterThanOrEqual(intersectedCount, valueCount);
break;
}
case ValueListFilterType.Exact:
{
var valueCount = Expression.Constant(filter.ValueList.Count);
finalExpression = Expression.Equal(intersectedCount, valueCount);
break;
}
case ValueListFilterType.Excluding:
{
var valueCount = Expression.Constant(0);
finalExpression = Expression.Equal(intersectedCount, valueCount);
break;
}
case ValueListFilterType.Any:
{
var valueCount = Expression.Constant(0);
finalExpression = Expression.GreaterThan(intersectedCount, valueCount);
break;
}
default:
break;
}
return finalExpression;
}