Query: Allow use of AsQueryable method

[colltoaction]

Update

Modifying the description to track support for AsQueryable in linq queries.
AsQueryable method is required when user wants to pass Expression to linq method which is hanging of an navigation property since navigation is of type IEnumerable. With the relinq fix now we can get parsed query with AsQueryable but we are client eval-ing afterwards. This issue is to track work needed on EF Core side to translate query to server.

Query
var query3 = db.Products.Select(p => p.ProductCategories.AsQueryable().Select(pc => pc.Category).Where(Category.IsGenre)).ToList();

QueryExecution:

dbug: Microsoft.EntityFrameworkCore.Query[10104]
      Optimized query model: 
      'from Product p in DbSet<Product>
      select 
          from ProductCategory pc in 
              (from ProductCategory <generated>_1 in DbSet<ProductCategory>
              where Property([p], "ProductId") ?= Property([<generated>_1], "ProductId")
              select [<generated>_1]).AsQueryable()
          join Category pc.Category in DbSet<Category>
          on Property([pc], "CategoryId") equals Property([pc.Category], "CategoryId")
          where [pc.Category].ParentId == (Nullable<Guid>)__Genre_0
          select [pc.Category]'
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'join Category pc.Category in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[EFSampleApp.Category]) on Property([pc], "CategoryId") equals Property([pc.Category], "CategoryId")' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where ([pc.Category].ParentId == Convert(__Genre_0, Nullable`1))' could not be translated and will be evaluated locally.
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      (QueryContext queryContext) => IEnumerable<IOrderedQueryable<Category>> _InterceptExceptions(
          source: IEnumerable<IOrderedQueryable<Category>> _ShapedQuery(
              queryContext: queryContext, 
              shaperCommandContext: SelectExpression: 
                  SELECT [p].[ProductId]
                  FROM [Commerce].[Product] AS [p], 
              shaper: TypedProjectionShaper<ValueBufferShaper, ValueBuffer, IOrderedQueryable<Category>>), 
          contextType: EFSampleApp.MyContext, 
          logger: DiagnosticsLogger<Query>, 
          queryContext: queryContext)

Original Issue

Hi!

I'm trying to map an existing database to EF Core. Our project has a terrible model where we have to compare the ParentId to a specific Guid to find out the type of a row. E.g. we have the table Categories and each Guid identifies Genre, Mood, etc. (we have a music app).

So I'm trying to write this property in the Category class, but if I do, I'm unable to use Include because it can't be translated:

public virtual bool IsGenre => ParentId == Genre;
private static Guid Genre = Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3")

If I instead do ctx.Categories.Where(c => c.ParentId == Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3") it works perfectly.

I'm wondering if there's any way to move this to the Category class so I can avoid copy and pasting for each query I need to write.

I tried to manually create the Expression:

public static Expression<Func<Category, bool>> IsGenre = c => c.ParentId == Genre;

but I get the following error on p.ProductCategories.Select(pc => pc.Category).Where(Category.IsGenre):

Error   CS1929  'IEnumerable<Category>' does not contain a definition for 'Where' and the best extension method overload 'Queryable.Where<Category>(IQueryable<Category>, Expression<Func<Category, bool>>)' requires a receiver of type 'IQueryable<Category>'

With .AsQueryable() I'm able to compile the program but I get This overload of the method 'System.Linq.Queryable.AsQueryable' is currently not supported.

Thanks!

[smitpatel]

I could not reproduce this. Below is the code i used. Can you make modifications in it according to what you are trying to do?

public class Program
{
    public static void Main(string[] args)
    {
        using (var ctx = new MyContext())
        {
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            var query1 =
                ctx.Categories.Where(c => c.ParentId == Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3")).ToList();

            var query2 = ctx.ProductCategories.Select(pc => pc.Category).Where(Category.IsGenre).ToList();

        }
    }
}

public class MyContext : DbContext
{
    public DbSet<ProductCategory> ProductCategories { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;");
    }
}

public class ProductCategory
{
    public Guid Id { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    private static Guid Genre = Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3");
    public static Expression<Func<Category, bool>> IsGenre = c => c.ParentId == Genre;

    public Guid Id { get; set; }
    public Guid ParentId { get; set; }
    public ProductCategory Parent { get; set; }
}

[colltoaction]

This is actually a many-to-many relationship. Please try the code below (and let me know if you need seed data):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace EFIssue
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                var query1 =
                    ctx.Categories.Where(c => c.ParentId == Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3")).ToList();

                var query2 = ctx.ProductCategories.Select(pc => pc.Category).Where(Category.IsGenre).ToList();

                var query3 = ctx.Products.Select(p =>
                    p.ProductCategories.Select(pc => pc.Category)
                                       .Where(Category.IsGenre))
                    .ToList();
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Product> Products { get; set; }
        public DbSet<ProductCategory> ProductCategories { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Category>(entity =>
            {
                entity.HasKey(e => e.CategoryId)
                    .HasName("PK__Category__19093A0BEC90FC43");

                entity.ToTable("Category", "Commerce");

                entity.Property(e => e.CategoryId).ValueGeneratedNever();

                entity.Property(e => e.Name).HasMaxLength(255);
            });

            modelBuilder.Entity<Product>(entity =>
            {
                entity.ToTable("Product", "Commerce");

                entity.Property(e => e.ProductId).ValueGeneratedNever();

                entity.Property(e => e.Name).HasMaxLength(255);
            });

            modelBuilder.Entity<ProductCategory>(entity =>
            {
                entity.ToTable("ProductCategory", "Commerce");

                entity.HasIndex(e => new { e.ProductId, e.CategoryId })
                    .HasName("nci_wi_ProductCategory_E47C12BC-D5AC-4997-BCF5-AD7A7AF8751B");

                entity.HasIndex(e => new { e.CategoryId, e.Id, e.ProductId })
                    .HasName("nci_wi_ProductCategory_C39551F634191AFB3482");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.HasOne(e => e.Category)
                    .WithMany(c => c.ProductCategories)
                    .HasForeignKey(e => e.CategoryId)
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("FK__ProductCa__Categ__0D44F85C");

                entity.HasOne(e => e.Product)
                    .WithMany(p => p.ProductCategories)
                    .HasForeignKey(e => e.ProductId)
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("FK__ProductCa__Produ__0C50D423");
            });
        }
    }

    public class Product
    {
        public Product()
        {
            ProductCategories = new HashSet<ProductCategory>();
        }

        public Guid ProductId { get; set; }
        public string Name { get; set; }

        public virtual ICollection<ProductCategory> ProductCategories { get; set; }
    }

    public class ProductCategory
    {
        public Guid CategoryId { get; set; }
        public Guid ProductId { get; set; }
        public int Id { get; set; }

        public virtual Category Category { get; set; }
        public virtual Product Product { get; set; }
    }

    public class Category
    {
        public Guid CategoryId { get; set; }
        public Guid? ParentId { get; set; }
        public string Name { get; set; }

        public virtual ICollection<ProductCategory> ProductCategories { get; set; }

        public static Expression<Func<Category, bool>> IsGenre = c => c.ParentId == Genre;
        // public virtual bool IsGenre => ParentId == Genre;
        private static Guid Genre = Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3");
    }
}

[smitpatel]

@tinchou - Thanks for working repro code. Can you tell in words what you are trying to query so I can translate it to linq.

[colltoaction]

In my real app I would like to:

1. Find some products based on some criteria (ctx.Products.Where(...))
2. Find the categories related to that product (I have to go through the ProductCategories intermediate table since it's a many-to-many relationship)

[smitpatel]

I tried running different queries. The queries in repro code are based criteria on category (rather than product as you mentioned in previous post) but since relationship is many to many, it can be easily written from other way depending on what you want to query.
Query1: Categories based on condition
Query2: ProductCategories based on condition on Category property
Query3: Categories based on condition with data for ProductCategories and products populated.
Query4: List of Products for all ProductCategories of (Category filtered by condition)
Query5: Similary to query you were writing, with forced client eval.

var query1 =
    ctx.Categories.Where(c => c.ParentId == Guid.Parse("3CA9FA61-EB62-4480-B476-867F78A9ADB3")).ToList();

var query2 = ctx.ProductCategories.Select(pc => pc.Category).Where(Category.IsGenre).ToList();

var query3 = ctx.Categories
    .Where(Category.IsGenre)
    .Include(c => c.ProductCategories)
    .ThenInclude(pc => pc.Category)
    .ToList();

var query4 = ctx.Categories.Where(Category.IsGenre)
    .SelectMany(c => c.ProductCategories)
    .Select(pc => pc.Product)
    .ToList();

var query5 = ctx.Products.ToList()
    .Where(p => p.ProductCategories.Select(pc => pc.Category).AsQueryable().Any(Category.IsGenre))
    .ToList();

As you can see you can still get products for categories even with starting from categories db set.
The issue with the query you were writing,
First you need to use Where instead of Select since you want to filter products and not project something from products. And that still would not work without AsQueryable() call since it is Enumerable. The AsQueryable() call is correct compilation wise. But the issue is EF uses Remotion.Linq to parse the query model which is not able to parse AsQueryable() method call yet therefore it throws runtime exception. Doing ToList() on product would mean that Where clause will be evaluated locally using Linq though it would also load whole table from database.
Query4 should be proper way to achieve what you are trying to query.

@anpete

[rowanmiller]

@smitpatel anything to do on the EF side here?

[smitpatel]

From discussions so far, the way user wants to write query is not possible due to limited from re-linq. And there is alternate way to write the query which gives same results.

[divega]

I was curious about this one because it ringed a bell so I took a look:

In the past I found it very useful to create repositories that take predicates of the form Expression<Func<T, bool>> in their methods and I also hit the need to call AsQueryable() just to be able to use those predicates in the context of collection nav. props.

It was very handy that AsQueryable() worked in the old EF stack to satisfy this scenario.

I agree with @smitpatel that in some cases it should be possible to change your code to get the same data loaded from the other side, but I don't think that it is true in all cases, e.g. it won't help if the reverse nav. prop. isn't present. It might also be harder to figure out what query to write.

If this is a fundamental issue in re-linq with AsQueryable() I think it might be worth passing the bug to them.

[rowanmiller]

@smitpatel could you pass off a request to re-linq to look at removing this limitation, then you can close this as external.

[colltoaction]

Fine with me. Please post here if you open an issue on re-linq so I can keep an eye on it!

[rowanmiller]

BTW re-linq issue tracker is at https://www.re-motion.org/jira/browse/RMLNQ

[smitpatel]

Found the existing bug in re-linq
https://www.re-motion.org/jira/projects/RMLNQ/issues/RMLNQ-28?filter=allopenissues