How to use subItem level using Expression?

Question

I have the next models:

public class MaterialCatalog {
  public int MaterialCatalogID { get; set; }
  public int AccountID { get; set; }
  public string MaterialName { get; set; }
  public ICollection<MaterialCategory> Categories { get; set; }
}

public class MaterialCategory {
  public int MaterialCatalogID { get; set; }
  public int UserCategoryID { get; set; }
}

I need to create IQueryable generic external method for this expression:

var items = from l in ctx.WorkareaTemplates
            where l.WorkareaTemplateCategories.Any(mc => mc.UserCategoryID == req.Category;

Here what I have tried. I get an error:

The double operator Equal is not defined for the types 'System.Collections.Generic.ICollection`1[MaterialCategory]' and 'System.Int32'.

How can I go to the subProperty to work with MaterialCategory?

public static IQueryable<TSource> FilterByUserCategoryId<TSource>(this IQueryable<TSource> source, object searchValue) {
  if(typeof(TSource).GetProperties().Any(x => x.Name == "Categories")) { 
    var parameter = Expression.Parameter(typeof(TSource), "e");
    var prop = Expression.Property(parameter, "Categories");
    var value = Expression.Constant(searchValue);
    var body = Expression.Equal(prop, value);
    var lambda = Expression.Lambda<Func<TSource, bool>>(body, parameter);

    var overload = typeof(Queryable).GetMethods().First(x => x.Name == "Any" && x.GetParameters().Length == 2);
    var orderByGeneric = overload.MakeGenericMethod(typeof(TSource), prop.Type);

    var result = orderByGeneric.Invoke(null, new object[] { source, lambda });

    return (IQueryable<TSource>)result;
  }
  return source;
}

Answer 1

So, the operation you want to do is basically this:

var items = ctx.WorkareaTemplates
  .Where(x => x.Categories.Any(c => c.UserCategoryID == req.Category));

The most important part you are missing is that you need to perform 2 querying operations:

• IQueryable<MaterialCatalog>.Where (to filter over MaterialCatalog)
• IEnumerable<MaterialCategory>.Any (to check membership on MaterialCategory).

That being said, check this sample implementation (you most probably want to strengthen it for corner cases and exceptions):

public static IQueryable<TSource> FilterByUserCategoryId<TSource>(this IQueryable<TSource> source, object searchValue) {
    var catsType = typeof(TSource).GetProperties().Single(p => p.Name == "Categories").PropertyType; // ICollection<MaterialCategory>
    var isIEnum = (Type t) => t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>); 
    var catType = catsType.GetInterfaces().First(isIEnum).GetGenericArguments()[0]; // MaterialCategory

    // GOAL:
    // source.Where(x => x.Categories.Any(c => c.UserCategoryID == searchValue));

    var cParam = Expression.Parameter(catType, "c"); // MaterialCategory c
    var idProp = Expression.Property(cParam, "UserCategoryID"); // c.UserCategoryID
    var idConst = Expression.Constant(searchValue); // searchValue
    var equal = Expression.Equal(idProp, idConst); // c.UserCategoryID == searchValue
    var anyPred = Expression.Lambda(equal, new[] { cParam }); // c => c.UserCategoryID == searchValue

    // this is questionable... relying on the current API of Enumerable.cs
    var anyMeth = typeof(Enumerable).GetMethods().Single(m => m.Name == "Any" && m.GetParameters().Count() == 2); // Enumerable.Any<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate)
    var anyMethGeneric = anyMeth.MakeGenericMethod(catType); // Enumerable.Any<MaterialCategory>(this IEnumerable<MaterialCategory> source, Func<MaterialCategory, bool> predicate)
    var xParam = Expression.Parameter(typeof(TSource), "x"); // MaterialCatalog x
    var catsMember = Expression.Property(xParam, "Categories"); // x.Categories
    var anyCall = Expression.Call(null, anyMethGeneric, catsMember, anyPred); // x.Categories.Any(c => c.UserCategoryID == searchValue));
    var wherePred = Expression.Lambda(anyCall, new[] { xParam }); // x => x.Categories.Any(c => c.UserCategoryID == searchValue)

    // this is even more questionable... relying on the private implementation of Queryable.cs
    var whereMeth = typeof(Queryable).GetMethods().First(m => m.Name == "Where"); // QueriableWhere<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
    var whereMethGeneric = whereMeth.MakeGenericMethod(typeof(TSource)); // Queriable.Where<MaterialCategory>(this IQueryable<MaterialCategory> source, Expression<Func<MaterialCategory, bool>> predicate)

    var result = whereMethGeneric.Invoke(null, new object[] { source, wherePred }); // source.Where(x => x.Categories.Any(c => c.UserCategoryID == searchValue))
    return (IQueryable<TSource>)result;
}