Nested expression building with linq and Entity Framework

Question

I'm trying to make a service that returns a catalog based on the filters.

I've seen a few results on the internet, but not quite my issue. I hope you can help me with mine.

The issue is that this query build cannot be translated into a store expression:

'LINQ to Entities does not recognize the method 'System.Linq.IQueryable'1[App.Data.Models.Subgroup] HasProductsWithState[Subgroup](System.Linq.IQueryable'1[App.Data.Models.Subgroup], System.Nullable`1[System.Boolean])' method, and this method cannot be translated into a store expression.'

How can I make it so the query can be translated into a store expression. Please don't suggest .ToList() as a answer as I don't want this to run in memory.

So what I have is:

    bool? isActive = null;
    string search = null;

    DbSet<Maingroup> query = context.Set<Maingroup>();

    var result = query.AsQueryable()
                      .HasProductsWithState(isActive)
                      .HasChildrenWithName(search)
                      .OrderBy(x => x.SortOrder)
                      .Select(x => new CatalogViewModel.MaingroupViewModel()
                              {
                                  Maingroup = x,
                                  Subgroups = x.Subgroups.AsQueryable()
                                               .HasProductsWithState(isActive)
                                               .HasChildrenWithName(search)
                                               .OrderBy(y => y.SortOrder)
                                               .Select(y => new CatalogViewModel.SubgroupViewModel()
                        {
                            Subgroup = y,
                            Products = y.Products.AsQueryable()
                                .HasProductsWithState(isActive)
                                .HasChildrenWithName(search)
                                .OrderBy(z => z.SortOrder)
                                .Select(z => new CatalogViewModel.ProductViewModel()
                                {
                                    Product = z
                                })
                        })
                });         

    return new CatalogViewModel() { Maingroups = await result.ToListAsync() };

In the code below you can see that I recursively call the extension to try and stack the expression. But when I walk through my code at runtime it does not enter the function again when

    return maingroups.Where(x => x.Subgroups.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>;

is called.

    public static class ProductServiceExtensions
    {
        public static IQueryable<TEntity> HasProductsWithState<TEntity>(this IQueryable<TEntity> source, bool? state)
        {
            if (source is IQueryable<Maingroup> maingroups)
            {
                return maingroups.Where(x => x.Subgroups.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>;
            }
            else if (source is IQueryable<Subgroup> subgroups)
            {
                return subgroups.Where(x => x.Products.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>;
            }
            else if (source is IQueryable<Product> products)
            {
                return products.Where(x => x.IsActive == state) as IQueryable<TEntity>;
            }

            return source;
        }

        public static IQueryable<TEntity> HasChildrenWithName<TEntity>(this IQueryable<TEntity> source, string search)
        {
            if (source is IQueryable<Maingroup> maingroups)
            {
                return maingroups.Where(x => search == null || x.Name.ToLower().Contains(search) || x.Subgroups.AsQueryable().HasChildrenWithName(search).Any()) as IQueryable<TEntity>;
            }
            else if (source is IQueryable<Subgroup> subgroups)
            {
                return subgroups.Where(x => search == null || x.Name.ToLower().Contains(search) || x.Products.AsQueryable().HasChildrenWithName(search).Any()) as IQueryable<TEntity>;
            }
            else if (source is IQueryable<Product> products)
            {
                return products.Where(x => search == null || x.Name.ToLower().Contains(search)) as IQueryable<TEntity>;
            }

            return source;
        }
    }

UPDATE

Missing classes:

    public class Maingroup
    {
        public long Id { get; set; }
        public string Name { get; set; }
        ...
        public virtual ICollection<Subgroup> Subgroups { get; set; }
    }

    public class Subgroup
    {
        public long Id { get; set; }
        public string Name { get; set; }

        public long MaingroupId { get; set; }
        public virtual Maingroup Maingroup { get; set; }
        ...
        public virtual ICollection<Product> Products { get; set; }
    }

    public class Product
    {
        public long Id { get; set; }
        public string Name { get; set; }

        public long SubgroupId { get; set; }
        public virtual Subgroup Subgroup { get; set; }
        ...
        public bool IsActive { get; set; }
    }

Tillo

Possible duplicate of Method cannot be translated into a store expression – Faro

@AleksAndreev It's definitely the same error, but not the same problem. Hes joining new relations and I'm stacking expressions – Tillo

If you comment out HasProductsWithState does it produce the same error on HasChildrenWithName, or does that translate OK? – Overstock

It will produce the same error, because recursive like this eturn maingroups.Where(x => x.Subgroups.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>; is probably the wrong way to do it. – Tillo

You seem to have a lot of AsQueryable that should be unnecessary - you should know the type at that point and whether it is IQueryable already... – Downfall

I have them .AsQueryable since the Maingroup.Subgroups is a ICollection and not IQuerable – Tillo

@Tillo can your solution use SQL? – Clatter

@Tillo could you please post your code for Maingroup and other classes? – Clatter

Answer 1

But when I walk through my code at runtime it does not enter the function again when

   return maingroups.Where(x => x.Subgroups.AsQueryable().HasProductsWithState(state)

Welcome to the world of expression trees!

x => x.Subgroups.AsQueryable().HasProductsWithState(state)

is lambda expression (Expression<Func<...>) with body

x.Subgroups.AsQueryable().HasProductsWithState(state)

The body is expression tree, in other words - code as data, hence is never executed (except if compiled to delegate as in LINQ to Objects).

It's easily overlooked since visually lambda expressions look like delegates. Even Harald in their answer after all explanations that one should not use custom methods, as a solution actually provides several custom methods with the rationale "I didn't use any self-defined function. My function calls will only change the Expression. The changed Expression can be translated into SQL". Sure, but if your functions are called! Which of course does not happen when they are inside expression tree.

With that being said, there is no good general solution. What I can offer is solution for your particular problem - transforming custom methods which receive IQueryable<T> plus other simple parameters and return IQueryable<T>.

The idea is to use custom ExpressionVisitor which identifies the "calls" to such method inside expression tree, actually calls them and replaces them with the result of the call.

The problem is to call

x.Subgroups.AsQueryable().HasProductsWithState(state)

when we have no actual x object. The trick is to call them with fake queryable expression (like LINQ to Objects Enumerable<T>.Empty().AsQueryble()) and then use another expression visitor to replace the fake expression with the original expression in the result (pretty much like string.Replace, but for expressions).

Here is the sample implementation of the above:

public static class QueryTransformExtensions
{ 
    public static IQueryable<T> TransformFilters<T>(this IQueryable<T> source)
    {
        var expression = new TranformVisitor().Visit(source.Expression);
        if (expression == source.Expression) return source;
        return source.Provider.CreateQuery<T>(expression);
    }

    class TranformVisitor : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.IsStatic && node.Method.Name.StartsWith("Has")
                && node.Type.IsGenericType && node.Type.GetGenericTypeDefinition() == typeof(IQueryable<>)
                && node.Arguments.Count > 0 && node.Arguments.First().Type == node.Type)
            {
                var source = Visit(node.Arguments.First());
                var elementType = source.Type.GetGenericArguments()[0];
                var fakeQuery = EmptyQuery(elementType);
                var args = node.Arguments
                    .Select((arg, i) => i == 0 ? fakeQuery : Evaluate(Visit(arg)))
                    .ToArray();
                var result = (IQueryable)node.Method.Invoke(null, args);
                var transformed = result.Expression.Replace(fakeQuery.Expression, source);
                return Visit(transformed); // Apply recursively
            }
            return base.VisitMethodCall(node);
        }

        static IQueryable EmptyQuery(Type elementType) =>
            Array.CreateInstance(elementType, 0).AsQueryable();

        static object Evaluate(Expression source)
        {
            if (source is ConstantExpression constant)
                return constant.Value;
            if (source is MemberExpression member)
            {
                var instance = member.Expression != null ? Evaluate(member.Expression) : null;
                if (member.Member is FieldInfo field)
                    return field.GetValue(instance);
                if (member.Member is PropertyInfo property)
                    return property.GetValue(instance);
            }
            throw new NotSupportedException();
        }
    }

    static Expression Replace(this Expression source, Expression from, Expression to) =>
        new ReplaceVisitor { From = from, To = to }.Visit(source);

    class ReplaceVisitor : ExpressionVisitor
    {
        public Expression From;
        public Expression To;
        public override Expression Visit(Expression node) =>
            node == From ? To : base.Visit(node);
    }
}

Now all you need is to call .TransformFilters() extension methods at the end of your queries, for instance in your sample

var result = query.AsQueryable()
    // ...
    .TransformFilters();

You can also call it on intermediate queries. Just make sure the call is outside expression tree :)

Note that the sample implementation is processing static methods having first parameter IQueryable<T>, returning IQueryable<T> and name starting with Has. The last is to skip Queryable and EF extension methods. In the real code you should use some better criteria - for instance the type of the defining class, or custom attribute etc.

Stamey

Answer 2

The cause of your problem

You have to be aware between an IEnumerable and an IQueryable. An IEnumerable object has everything in it to enumerate over all the elements: you can ask for the first element of the sequence, and once you've got an element, you can ask for the next element, until there are no more elements.

An IQueryable seems similar, however, the IQueryable does not hold everything to enumerate the sequence. It holds an Expression and a Provider. The Expression is a generic form of what must be queried. The Provider knows who must execute the query (usually a database management system), how to communicate with this executor and which language to use (usually something SQL-like).

As soon as you start enumerating, either explicitly by calling GetEnumerator and MoveNext, or implicitly by calling foreach, ToList, FirstOrDefault, Count, etc, the Expression is sent to the Provider, who will translate it into SQL and call the DBMS. The returned data is presented as an IEnumerable object, which is enumerated, using GetEnumerator

Because the Provider has to translate the Expression into SQL, the Expression may only call functions that can be translated into SQL. Alas, the Provider does not know HasProductsWithState, nor any of your own defined functions, and thus can't translate it into SQL. In fact, the entity framework provider also does not know how to translate several standard LINQ functions, and thus they can't be used AsQueryable. See Supported and Unsupported LINQ methods.

So you'll have to stick to functions that return an IQueryable where the Expression contains only supported functions.

Class Description

Alas you forgot to give us your entity classes, so I'll have to make some assumptions about them.

Apparently have a DbContext with at least three DbSets: MainGroups, SubGroups and Products.

There seems to be a one-to-many (or possible many-to-many) relation between MaingGroups and SubGroups: every MainGroup has zero or more SubGroups.

It seems that there is also a one-to-many relation between SubGroups and Products: every SubGroup has zero or more Products.

Alas you forgot to mentions that return relation: does every Product belong to exactly one SubGroup (one-to-many), or does every Product belong to zero or more SubGroups (many-to-many`)?

If you've followed the entity framework code first conventions, you will have classes similar to this:

class MainGroup
{
    public int Id {get; set;}
    ...

    // every MainGroup has zero or more SubGroups (one-to-many or many-to-many)
    public virtual ICollection<SubGroup> SubGroups {get; set;}
}
class SubGroup
{
    public int Id {get; set;}
    ...

    // every SubGroup has zero or more Product(one-to-many or many-to-many)
    public virtual ICollection<Product> Products{get; set;}

    // alas I don't know the return relation
    // one-to-many: every SubGroup belongs to exactly one MainGroup using foreign key
    public int MainGroupId {get; set;}
    public virtual MainGroup MainGroup {get; set;}
    // or every SubGroup has zero or more MainGroups:
    public virtual ICollection<MainGroup> MainGroups {get; set;}
}

Something similar for Product:

class Product
{
    public int Id {get; set;}
    public bool? IsActive {get; set;} // might be a non-nullable property
    ...

    // alas I don't know the return relation
    // one-to-many: every Productbelongs to exactly one SubGroup using foreign key
    public int SubGroupId {get; set;}
    public virtual SubGroup SubGroup {get; set;}
    // or every Product has zero or more SubGroups:
    public virtual ICollection<SubGroup> SubGroups {get; set;}
}

And of cours your DbContext:

class MyDbContext : DbContext
{
    public DbSet<MainGroup> MainGroups {get; set;}
    public DbSet<SubGroup> SubGroups {get; set;}
    public DbSet<Product> Products {get; set;}
}

This is all that entity framework needs to know to detect your tables, the columns in your tables and the relations between the tables (one-to-many, many-to-many, one-to-zero-or-one). Only if you want to deviate from the standard naming you'll need attributes of fluent API.

In entity framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many).

Note that although the SubGroups of a MainGroup is declared as a collection, if you query the SubGroups of the MaingGroup with Id 10 you'll still get an IQueryable.

Requirements

Given a queryable sequence of Products and a nullable Boolean State, HasProductsWithState(products, state) should return the queryable sequence of Products that have a value of IsActive equal to State

Given a queryable sequence of SubGroups and a nullable Boolean State, HasProductsWithState(subGroups, state) should return the queryable sequence of SubGroups that have at least one Product that "HasProductsWithState(Product, State)1

Given a queryable sequence of MainGroups and a nullable Boolean State, HasProductsWithState(mainGroups, state) should return the queryable sequence of MainGroups, that contains all MainGroups that have at least one SubGroup that HasProductsWithState(SubGroup, State)

Solution

Well If you write the requirements like this, the extension methods are easy:

IQueryable<Product> WhereHasState(this IQueryable<Product> products, bool? state)
{
    return products.Where(product => product.IsActive == state);
}

Because this function does not check whether a Product has this state, but returns all Product that have this state, I chose to use a different name.

bool HasAnyWithState(this IQueryable<Product> products, bool? state)
{
    return products.WhereHasState(state).Any();
}

Your code will be slightly different if IsActive is a non-nullable property.

I'll do something similar with SubGroups:

IQueryable<SubGroup> WhereAnyProductHasState(this IQueryable<SubGroup> subGroups, bool? state)
{
    return subgroups.Where(subGroup => subGroup.Products.HasAnyWithState(state));
}

bool HasProductsWithState(this IQueryable<SubGroup> subGroups, bool? state)
{
     return subGroups.WhereAnyProductHasState(state).Any();
}

Well, you'll know the drill by now for MainGroups:

IQueryable<MainGroup> WhereAnyProductHasState(this IQueryable<MainGroup> mainGroups, bool? state)
{
    return maingroups.Where(mainGroup => mainGroup.SubGroups.HasProductsWithState(state));
}

bool HasProductsWithState(this IQueryable<MainGroup> mainGroups, bool? state)
{
     return mainGroups.WhereAnyProductHasState(state).Any();
}

If you look really closely, you'll see that I didn't use any self-defined function. My function calls will only change the Expression. The changed Expression can be translated into SQL.

I've separated the function into a lot of smaller functions, because you didn't say whether you want to use HasProductsWithState(this IQueryable<SubGroup>, bool?) and HasProductsWithState(this IQueryable<Product>, bool?).

TODO: do something similar for similar for HasChildrenWithName: separate into smaller functions that contain only LINQ functions, and nothing else

If you'll only call HasProductsWithState(this IQueryable<MainGroup>, bool?) you can do it in one function, using `SelectMany:

IQueryable<MainGroup> HasProductsWithState(this IQueryable<MainGroup> mainGroups, bool? state)
{
    return mainGroups
        .Where(mainGroup => mainGroup.SelectMany(mainGroup.SubGroups)
                                     .SelectMany(subGroup => subGroup.Products)
                                     .Where(product => product.IsActive == state)
                                     .Any() );
}

Perlie

While this is improving the custom extension method structure, it does not address / solve the original problem. Which is custom methods cannot be translated to SQL. So in your final "solution", return maingroups.Where(mainGroup => mainGroup.SubGroups.HasProductsWithState(state)); has exactly the same issue - method HasProductsWithState cannot be translated. Note that the "call" is inside query expression tree, so the method will not be called at all. – Stamey

@harald, This is a good explanation of a possible solution but unfortunatly it won't work. Products in Subgroup is an ICollection, therefor subGroup.Products.HasAnyWithState(state) will tell you the ICollection cannot be casted to an IQueryable I wan't the eager loaded items to be filtered aswell 'ICollection<Subgroup>' does not contain a definition for 'HasProductsWithState' and the best extension method overload 'ProductServiceExtensions.HasProductsWithState(IQueryable<Subgroup>, bool?)' requires a receiver of type 'IQueryable<Subgroup>' – Tillo