efcore 5 preview 4 filtered include many to many

[rawnanoob]

I tried the filtered include feature in the latest preview release.
It works great except if you want to try to filter with a many to many relationship.

Steps to reproduce

I tried the following
var filtered = context.Deal.Include(x => x.DealXComforts.Where(s => s.Comfort.Name == "Seat")).ToList();

It doesn't give an error. It just doesn't do anything.

Further technical details

EF Core version: EF Core 5 preview 4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET Core 3.1

[ajcvickers]

@rawnanoob Are you attempting to use the new many-to-many support in EF Core 5.0? If so, then queries are not yet implemented.

[rawnanoob]

No, I'm using an entity class for the join table and mapping the two separate one-to-many relationships.
Just like here
https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key

@rawnanoob Are you attempting to use the new many-to-many support in EF Core 5.0? If so, then queries are not yet implemented.

[maumar]

@rawnanoob can you post the code for your entity classes as well as contents of OnModelCreating method?

[rawnanoob]

@rawnanoob can you post the code for your entity classes as well as contents of OnModelCreating method?

public class Deal
    {
        public Guid Id { get; set; }
        public List<DealXComfort> DealXComforts { get; protected set; } = new List<DealXComfort>();
    }
public class Comfort
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public List<DealXComfort> DealXComforts { get; set; } = new List<DealXComfort>();

    }

        public class DealXComfort
    {
        public Guid DealId { get; set; }
        public Deal Deal { get; set; }
        public Guid ComfortId { get; set; }
        public Comfort Comfort { get; set; }
    }

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {


            modelBuilder.Entity<DealXComfort>()
               .HasKey(lc => new { lc.DealId, lc.ComfortId });
            modelBuilder.Entity<DealXComfort>()
                .HasOne(pt => pt.Deal)
            .WithMany(p => p.DealXComforts)
            .HasForeignKey(pt => pt.DealId);
            modelBuilder.Entity<DealXComfort>()
               .HasOne(pt => pt.Comfort)
           .WithMany(p => p.DealXComforts)
           .HasForeignKey(pt => pt.ComfortId);
        }

[maumar]

@rawnanoob I'm having troubles reproducing the issue. I used the following code sample:

        [ConditionalFact]
        public void Repro20997()
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var d1 = new Deal { Id = Guid.NewGuid() };
                var d2 = new Deal { Id = Guid.NewGuid() };
                var d3 = new Deal { Id = Guid.NewGuid() };
                var d4 = new Deal { Id = Guid.NewGuid() };

                var c1 = new Comfort { Id = Guid.NewGuid(), Name = "Not a seat" };
                var c2 = new Comfort { Id = Guid.NewGuid(), Name = "Seat" };
                var c3 = new Comfort { Id = Guid.NewGuid(), Name = "Seat" };

                var dc11 = new DealXComfort { Deal = d1, Comfort = c1 };
                var dc12 = new DealXComfort { Deal = d1, Comfort = c2 };
                var dc13 = new DealXComfort { Deal = d1, Comfort = c3 };
                var dc22 = new DealXComfort { Deal = d2, Comfort = c2 };
                var dc23 = new DealXComfort { Deal = d2, Comfort = c3 };
                var dc31 = new DealXComfort { Deal = d3, Comfort = c1 };
                var dc41 = new DealXComfort { Deal = d4, Comfort = c1 };

                ctx.DealXComfort.AddRange(dc11, dc12, dc13, dc22, dc23, dc31, dc41);
                ctx.Deal.AddRange(d1, d2, d3, d4);
                ctx.Comfort.AddRange(c1, c2, c3);
                ctx.SaveChanges();
            }


            using (var ctx = new MyContext())
            {
                var filtered = ctx.Deal.Include(x => x.DealXComforts.Where(s => s.Comfort.Name == "Seat")).ToList();
            }
        }

        public class Deal
        {
            public Guid Id { get; set; }
            public List<DealXComfort> DealXComforts { get; protected set; } = new List<DealXComfort>();
        }
        public class Comfort
        {
            public Guid Id { get; set; }
            public string Name { get; set; }
            public List<DealXComfort> DealXComforts { get; set; } = new List<DealXComfort>();
        }

        public class DealXComfort
        {
            public Guid DealId { get; set; }
            public Deal Deal { get; set; }
            public Guid ComfortId { get; set; }
            public Comfort Comfort { get; set; }
        }

        public class MyContext : DbContext
        {
            public DbSet<Deal> Deal { get; set; }
            public DbSet<Comfort> Comfort { get; set; }
            public DbSet<DealXComfort> DealXComfort { get; set; }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<DealXComfort>()
                   .HasKey(lc => new { lc.DealId, lc.ComfortId });

                modelBuilder.Entity<DealXComfort>()
                    .HasOne(pt => pt.Deal)
                    .WithMany(p => p.DealXComforts)
                    .HasForeignKey(pt => pt.DealId);

                modelBuilder.Entity<DealXComfort>()
                   .HasOne(pt => pt.Comfort)
                   .WithMany(p => p.DealXComforts)
                   .HasForeignKey(pt => pt.ComfortId);
            }

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

The sql that gets generated looks good:

SELECT [d].[Id], [t].[DealId], [t].[ComfortId], [t].[Id]
FROM [Deal] AS [d]
LEFT JOIN (
    SELECT [d0].[DealId], [d0].[ComfortId], [c].[Id]
    FROM [DealXComfort] AS [d0]
    INNER JOIN [Comfort] AS [c] ON [d0].[ComfortId] = [c].[Id]
    WHERE [c].[Name] = N'Seat'
) AS [t] ON [d].[Id] = [t].[DealId]
ORDER BY [d].[Id], [t].[DealId], [t].[ComfortId], [t].[Id]

and I'm getting expected results as well. Can you modify the sample I posted above so that it shows the problem you are seeing?

[rawnanoob]

@maumar My bad, the filter works. I was expecting to have the deals which only have the comfort "Seat". So in your example, I was expecting d1 and d2 to be returned.

[maumar]

Ah, the filter only applies to the entities that are included. In principle Include operation should never return fewer elements than the same query without include (although we have issues with that involving global query filters - #19801)