[InvalidCastException] when using Include with Where that navigate to parent entity

[HairyPortal]

Whenever I try to use a combination of Include and Where, this exception occurred

InvalidCastException: Unable to cast object of type 'System.Linq.Expressions.FieldExpression' to type 'System.Linq.Expressions.ParameterExpression'.

It only happens when the Where function navigate to parent entity, for example, this will cause the exception.

db.Table.Include(x=>x.include1).Where(x => x.Parent1.GrandParent1.ID == 1).ToListAsync();

While these will not cause exception.

db.Table.Include(x=>x.include1).Where(x => x.ID == 1).ToListAsync(); // This is OK
db.Table.Where(x => x.Parent1.GrandParent1.ID == 1).ToListAsync(); // This also OK

Is this a bug or I screw something up?

[techyian]

Are you including the grandparent entity? I don't understand how the below can work correctly if you're not including the grandparent if you're searching on a property of a grandparent.

db.Table.Where(x => x.Parent1.GrandParent1.ID == 1).ToListAsync();

[rowanmiller]

@NoCommonSense EF will translate that to a WHERE clause in the database. There is no need to bring the data back into memory (which is what Include does) just to filter by it.

[rowanmiller]

@as14220808 looks like this is fixed in our current code base. I verified that the following code fails with the exception you saw on RC1, but succeeds on the latest rolling build (7.0.0-rc2-16619).

using Microsoft.Data.Entity;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp1
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new BloggingContext())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                db.Comments.Include(c => c.Post).Where(c => c.Post.Blog.BlogId == 1).ToList();
            }
        }
    }

    class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
        public DbSet<Comment> Comments { get; set; }

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

    class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; set; }
    }

    class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }

        public List<Comment> Comments { get; set; }
    }

    class Comment
    {
        public int CommentId { get; set; }
        public string Text { get; set; }

        public int PostId { get; set; }
        public Post Post { get; set; }
    }
}

Here is the SQL that is generated. BTW we have a couple of other issues tracking that we could consolidate the duplicated join to Post.

SELECT [c].[CommentId], [c].[PostId], [c].[Text], [p].[PostId], [p].[BlogId], [p].[Title]
FROM [Comment] AS [c]
INNER JOIN [Post] AS [c.Post] ON [c].[PostId] = [c.Post].[PostId]
INNER JOIN [Blog] AS [c.Post.Blog] ON [c.Post].[BlogId] = [c.Post.Blog].[BlogId]
INNER JOIN [Post] AS [p] ON [c].[PostId] = [p].[PostId]
WHERE [c.Post.Blog].[BlogId] = 1

[HairyPortal]

@rowanmiller Thanks so much for clarifying the issue.

[wmalgadey]

I experience this very same problem in ef6! Is it possible to port the solution to ef6 too?

[divega]

@wmalgadey I am not sure the exact same issue is present in EF6. Could you please create a new issue (complete with repro code) at https://github.com/aspnet/EntityFramework6/issues?

[wmalgadey]

sorry! my bad. I did some further investigations and found out, that my database had some issues. thanks for your response.