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:

```cs
    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

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

```

is called.

```cs
    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:

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

```

```cs
    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; }
    }

```

```cs
    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; }
    }

```