Generic Expression Based Any Method on Navigation Property

Question

I am attempting to write a generic expression-based method for adding an "Any" query on a navigation property of a DbSet. I'm using dotnet core 5 and entity framework core. At the moment I am getting the following:

'DbSet().Where(u => u.UserRoles.Any())' could not be translated

which looks like a reasonable query to me, though I'm not sure if all of the types are correct or if this is possible at all. The method I currently have to construct this is below:

private IQueryable<TEntity> GenericAny(IQueryable<TEntity> queryable)
{
    // attempting to replicate
    // users.Where(u => u.UserRoles.Any());
    var linkedEntityName = "UserRoles";
    // once working look at passing these in as parameters

    Type listType = typeof(TEntity).GetProperty(linkedEntityName).PropertyType.GetGenericArguments()[0];

    var param = Expression.Parameter(typeof(TEntity), "u");
    MemberExpression body = Expression.Property(param, linkedEntityName);

    // find AsQueryable method to be called on navigation prop
    var toQueryable = typeof(Queryable).GetMethods()
            .Where(m => m.Name == "AsQueryable")
            .Single(m => m.IsGenericMethod)
            .MakeGenericMethod(listType);

    var anyLambda = Expression.Lambda<Func<TEntity, bool>>(
                            Expression.Call(
                                typeof(Enumerable),
                                "Any",
                                new Type[] { listType },
                                Expression.Call(null, toQueryable, body)),
                            Expression.Parameter(typeof(TEntity), "u"));

    var whereMethod = typeof(Queryable)
                            .GetMethods()
                            .Where(m => m.Name == "Where" && m.GetParameters().Length == 2)
                            .First()
                            .MakeGenericMethod(typeof(TEntity));

    var whereCallExpression = Expression.Call(
                                    whereMethod,
                                    queryable.Expression,
                                    anyLambda);

    return queryable = queryable.Provider.CreateQuery<TEntity>(whereCallExpression);
}  

The UserRoles property is defined on the User object as:

[InverseProperty(nameof(UserRole.User))]
public virtual ICollection<UserRole> UserRoles { get; set; }

I am able to make calls on the dbset directly using:

var query =  _context.Users
                .Include(u => u.UserRoles)
                .AsQueryable();

return query.Where(u => u.UserRoles.Any());

Am I missing something here? Any help much appreciated.

Answer 1

This is correct implementation without not needed operations.

public static IQueryable<TEntity> GenericAny<TEntity>(this IQueryable<TEntity> queryable, string linkedEntityName)
{
    var param = Expression.Parameter(typeof(TEntity), "e");
    var propExpression = Expression.Property(param, linkedEntityName);
    var listType = propExpression.Type.GetGenericArguments()[0];

    var anyCall =
        Expression.Call(
            typeof(Enumerable),
            nameof(Enumerable.Any),
            new[] { listType },
            propExpression
        );

    var predicate = Expression.Lambda<Func<TEntity, bool>>(anyCall, param);

    return queryable.Where(predicate);
}