dotnet / dotnet/EntityFramework.Docs

Include with filter is ignored in the generated SQL

Open
#3,223 3 comments 0 reactions 0 assignees View on GitHub
area-query-related-data
Dominant language
Mermaid
Stars
1.7k
Forks
2k
Avg merge
7d 23h
Merged PRs (30d)
16

Description

## File a bug

GIVEN I use filtered Include
```C#
.Include(blog => blog .Posts.Where(post => !EF.Functions.Like(post.Title, "%-1%")))`
```
WHEN I use blog.Posts.Select() in a Select clause

```C#
.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.

```C#
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
{
public void Configure(EntityTypeBuilder 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);
}
```

```TSQL
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:

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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.