Allow using parent entity for filtering childs in include

[at-soft]

Hi,

I have an entity with children entities and I want to filter it by value in the parent entity. Now I get an exception The LINQ expression could not be translated.

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

namespace Ef.FilterInclude.ByParent
{
    internal class Program
    {
        private static void Main()
        {
            using var db = new Context();

            var result = db.Posts.Include(x => x.Tags.Where(y => y.Id == x.PrimaryTagId)).ToArray();
        }

        public class Context : DbContext
        {
            public DbSet<Post> Posts { get; set; }

            public DbSet<Tag> Tags { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder options) =>
                options.UseSqlServer("Data Source=blogging.db");

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);

                modelBuilder.Entity<Post>()
                    .HasMany(x => x.Tags)
                    .WithOne()
                    .HasForeignKey(x => x.PostId);
            }
        }

        public class Post
        {
            public int Id { get; set; }

            public int? PrimaryTagId { get; set; }

            public List<Tag> Tags { get; set; }
        }

        public class Tag
        {
            public int Id { get; set; }

            public int PostId { get; set; }

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

Now this code produces an exception

 The LINQ expression 'DbSet<Tag>()
    .Where(t => EF.Property<Nullable<int>>(EntityShaperExpression: 
        EntityType: Post
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    , "Id") != null && object.Equals(
        objA: (object)EF.Property<Nullable<int>>(EntityShaperExpression: 
            EntityType: Post
            ValueBufferExpression: 
                ProjectionBindingExpression: EmptyProjectionMember
            IsNullable: False
        , "Id"), 
        objB: (object)EF.Property<Nullable<int>>(t, "PostId")))
    .Where(t => (Nullable<int>)t.Id == x.PrimaryTagId)' could not be translated.

I hope to get a query like this

SELECT  *
FROM Posts as p
LEFT JOIN Tags t ON p.id = t.PostId AND t.Id = p.PrimaryTagId

Thank you

[AndriySvyryd]

@maumar

[maumar]

nav expansion produces incorrect tree:

DbSet<Post>()
    .Select(p => IncludeExpression(
        p, 
        MaterializeCollectionNavigation(
            Navigation: Post.Tags,
            subquery: DbSet<Tag>()
                .Where(t => EF.Property<Nullable<int>>(p, "Id") != null && object.Equals(
                    objA: (object)EF.Property<Nullable<int>>(p, "Id"), 
                    objB: (object)EF.Property<Nullable<int>>(t, "PostId")))
                .Where(t => (Nullable<int>)t.Id == x.PrimaryTagId), Tags)
    )

we should either throw when encountering variable from outer scope (x), or perhaps we can remap it to p to make the query work.

Note that we throw better exception in current bits:

The LINQ expression 'x' could not be translated.

[at-soft]

Is any updates on that issue?

[ajcvickers]

@at-soft This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 7.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.

[giarico]

Any chance this could be addressed in EF8?

[roji]

@giarico this enhancement is still in the backlog, and has only 6 votes on it; so it's unlikely we'll consider this for EF8.

[cord-agencyroot]

So, I had a similar issue and the work around is to add a navigation property on the Tag => Post.

var result = db.Posts.Include(post => post.Tags.Where(tag => tag.Id == tag.Post.PrimaryTagId)).ToArray();

Should work. Usually I try to avoid child -> parent navigation properties, but this was a work-around in my case