Select + Distinct + Select Collection results in incorrect SQL and results

Open
#24,263 3 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
42/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
csharp, postgresql
Domain
backend, databases

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

area-query blocked customer-reported priority-bug
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from dotnet/efcore

All issues in dotnet/efcore

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.