dotnet / dotnet/EntityFramework.Docs
Include with filter is ignored in the generated SQL
- 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
Assessment
This issue has not been assessed yet.