Include with filter is ignored in the generated SQL

[Liero]

File a bug

GIVEN I use filtered Include

.Include(blog => blog .Posts.Where(post => !EF.Functions.Like(post.Title, "%-1%")))`

WHEN I use blog.Posts.Select() in a Select clause

.Select(blog => blog.Post.Select(p => new {  ... }))

THEN the where condition is ignored in the generated SQL

I've not noticed this behavior in the docs so I guess it is unexpected / bug.

Include your code

here is exactly the code and generated SQL.

    var dbContext = new MyDbContext();
    dbContext.Export.AsNoTracking()
                .Include(e => e.ExportVýrobky.Where(i => !EF.Functions.Like(i.Výrobok.Zákazka.Objednávka, "%-1%")))
                .Where(e => e.Zákazník == customerId)
                .Select(e => new DeliveryNote
                {
                    Id = e.Id,
                    Date = e.Dátum.Value,
                    DeliveryService = e.Shipper.Názov,
                    Invoice = e.Faktúra,
                    PackageSize = e.Balenie,
                    TrackingNumber = e.Awb,
                    Items = e.ExportVýrobky.Select(item => new DeliveryNoteItem
                    {
                        Count = item.Množstvo,
                        Descrition = item.Výrobok.Popis,
                        OrderId = item.IdVýrobku ?? 0,
                        CustomerOrderId = item.Výrobok.Objednávka,
                    }).ToList()
                })
                .OrderByDescending(e => e.Date);         

    public class ExportVýrobkyConfiguration : IEntityTypeConfiguration<ExportVýrobky>
    {
        public void Configure(EntityTypeBuilder<ExportVýrobky> entity)
        {      
            entity.HasOne(d => d.Export)
                .WithMany(p => p.ExportVýrobky)
                .HasForeignKey(d => d.IdExportu);

            entity.HasOne(d => d.Výrobok)
                .WithMany(p => p.ExportVýrobky)
                .HasForeignKey(d => d.IdVýrobku);
        }   

      SELECT [e].[ID], [e].[Dátum], [z].[Názov], [e].[Faktúra], [e].[Balenie], [e].[AWB], [z].[ID], [t].[Count], [t].[Descrition], [t].[SylexOrderId], [t].[CustomerOrderId], [t].[ID], [t].[ID0]
      FROM [Export] AS [e]
      LEFT JOIN [Zákazníci] AS [z] ON [e].[Dopravca] = [z].[ID]
      LEFT JOIN (
          SELECT [e0].[Množstvo] AS [Count], [v].[Popis] AS [Descrition], COALESCE([e0].[IDVýrobku], 0) AS [OrderId], [v].[Objednávka] AS [CustomerOrderId], [e0].[ID], [v].[ID] AS [ID0], [e0].[IdExportu]
          FROM [ExportVýrobky] AS [e0]
          INNER JOIN [Výrobky] AS [v] ON [e0].[IDVýrobku] = [v].[ID]
      ) AS [t] ON [e].[ID] = [t].[IdExportu]
      WHERE [e].[Zákazník] = @__customerId_1
      ORDER BY [e].[Dátum] DESC, [e].[ID], [z].[ID], [t].[ID], [t].[ID0]

Include provider and version information

EF Core version:

   <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.0" />

Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: (NET 5.0 Operating system: Windows 10

[maumar]

@Liero this is by design. Include only applies to entities that are directly projected in the final result. Because you are using DTO, the entity on which include is defined is not present in the final result and the include is completely ignored.

You can get the desired result by applying the filter directly to the ExportVýrobky collection in the final projection instead:

    dbContext.Export.AsNoTracking()
                .Where(e => e.Zákazník == customerId)
                .Select(e => new DeliveryNote
                {
                    Id = e.Id,
                    Date = e.Dátum.Value,
                    DeliveryService = e.Shipper.Názov,
                    Invoice = e.Faktúra,
                    PackageSize = e.Balenie,
                    TrackingNumber = e.Awb,
                    Items = e.ExportVýrobky.Where(i => !EF.Functions.Like(i.Výrobok.Zákazka.Objednávka, "%-1%")).Select(item => new DeliveryNoteItem
                    {
                        Count = item.Množstvo,
                        Descrition = item.Výrobok.Popis,
                        OrderId = item.IdVýrobku ?? 0,
                        CustomerOrderId = item.Výrobok.Objednávka,
                    }).ToList()
                })
                .OrderByDescending(e => e.Date); 

[maumar]

assigning myself to fix the documentation - this is not the first time users were expecting include to be applied on entity which is not projected directly, we should make an explicit note about this

[maumar]

note to self: also consider mentioning this scenario: https://github.com/dotnet/efcore/issues/26327