Select + Distinct + Select Collection results in incorrect SQL and results
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 42/100
Research direction
Start with the MyDbContext and DistinctBug.Run reproduction, comparing the generated SQL and ShapedQueryExpression for the two LINQ queries. Trace the Select, Distinct, and collection-navigation translation path, then verify that the second query materializes tbl2s and preserves regression coverage for the reported case.
Written by the indexing model from the issue text.
Description
public class MyDbContext : DbContext
{
public DbSet<tbl1> tbl1s { get; set; }
public DbSet<tbl2> tbl2s { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.LogTo(Console.Out.WriteLine)
.UseNpgsql("...");
}
[Table("tbl1")]
public class tbl1
{
public int id { get; set; }
public string col1 { get; set; }
public virtual List<tbl2> tbl2s { get; set; }
}
[Table("tbl2")]
public class tbl2
{
public int id { get; set; }
public string col1 { get; set; }
public int tbl1id { get; set; }
public virtual tbl1 tbl1 { get; set; }
}
public class DistinctBug
{
public static void Run()
{
using (var db = new MyDbContext())
{
var ok = db.tbl1s.Distinct().Select(t1 => new { t1.tbl2s }).ToList();
Debug.Assert(ok.All(item => item.tbl2s != null));
}
using (var db = new MyDbContext())
{
var buggy = db.tbl1s.Select(t1 => new { t1 }).Distinct().Select(t1 => new { t1.t1.tbl2s }).ToList();
Debug.Assert(buggy.All(item => item.tbl2s != null));
}
}
}
The first query works as expected. SQL:
SELECT t0.id, t1.id, t1.col1, t1.tbl1id
FROM (
SELECT DISTINCT t.id, t.col1
FROM tbl1 AS t
) AS t0
LEFT JOIN tbl2 AS t1 ON t0.id = t1.tbl1id
ORDER BY t0.id, t1.id
In the second query, this SQL query is generated:
SELECT t0.id, t0.col1
FROM (
SELECT DISTINCT t.id, t.col1
FROM tbl1 AS t
) AS t0
The ShapedQueryExpression before materialization inject:
ShapedQueryExpression:
QueryExpression:
Projection Mapping:
SELECT t0.id, t0.col1
FROM Projection Mapping:
(
SELECT DISTINCT t.id, t.col1
FROM tbl1 AS t
) AS t0
ShaperExpression: new { tbl2s = EntityShaperExpression:
EntityType: tbl1
ValueBufferExpression:
ProjectionBindingExpression: id:0,
col1:1,
IsNullable: False
== default(tbl1) ? null : EntityShaperExpression:
EntityType: tbl1
ValueBufferExpression:
ProjectionBindingExpression: id:0,
col1:1,
IsNullable: False
.tbl2s }
There are no exceptions generated. The issue is that tbl2s on the result items are null, instead of a list of entities, as in the first query.
EF Core version: 5.0.2
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.8.5
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- Avg merge
- 2d 5h
- Merged PRs (30d)
- 134
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from dotnet/efcore
-
Difficulty 4/5 3-5 days Newbie friendliness 55/100
-
customer-reported
Difficulty 5/5 Over a week Newbie friendliness 38/100
-
area-cosmos area-vector-search
Difficulty 5/5 Over a week Newbie friendliness 25/100
-
area-cosmos
Difficulty 5/5 Over a week Newbie friendliness 25/100
-
area-tools needs-design
Difficulty 4/5 3-5 days Newbie friendliness 25/100
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 86/100
-
:watch: Not Triaged 11.0 fundamentals/subsvc
Difficulty 2/5 1-3 hours Newbie friendliness 92/100
dotnet/AspNetCore.Docs#37699 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
SubtitleEdit/subtitleedit#15108 · 1 comment ·
-
area/docs-content Bug pulumi/docs
Difficulty 1/5 1-3 hours Newbie friendliness 94/100
-
agentic-workflows untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100