distinct made before projection

[sebestyn168]

error description

When preparing a query projecting a single id column and using Distinct to avoid duplicate I expect to retrieve... well distinct results

Include your code

            var query = context.Affaires
                .Where(a => a.StatutAffaire == StatutAffaire.Won)
                .Where(a => a.ReferenceId == null)
                .Where(a => listPassIds.Contains(a.EntityId))
                ;

            var passIds = await query
                .Select(a => a.PassId) // projection
                .Distinct() // distinct
                .ToArrayAsync(cancellationToken);

expected query

exec sp_executesql N'SELECT DISTINCT [l].[PassId]
FROM [Link_Pass_AtlasAffaire] AS [l]
WHERE [l].[StatutAffaire] = 4 AND [l].[ReferenceId] IS NULL AND [l].[PassId] IN (
    SELECT [l0].[value]
    FROM OPENJSON(@__listPassIds_0) WITH ([value] uniqueidentifier ''$'') AS [l0]
)',N'@__listPassIds_0 nvarchar(4000)',@__listPassIds_0=N'["be8a8784-978d-4a68-9484-08dd095619d2","1579e349-0992-4a3b-6ee1-08dc9c32a29e"]'

query produced

exec sp_executesql N'SELECT DISTINCT [l].[Id], [l].[AtlasId], [l].[PassId], [l].[LastControlDateTime], [l].[LastSendingDateTime], [l].[ReferenceId], [l].[AtlasGroupId], [l].[StatutAffaire]
FROM [Link_Pass_AtlasAffaire] AS [l]
WHERE [l].[StatutAffaire] = 4 AND [l].[ReferenceId] IS NULL AND [l].[PassId] IN (
    SELECT [l0].[value]
    FROM OPENJSON(@__listPassIds_0) WITH ([value] uniqueidentifier ''$'') AS [l0]
)',N'@__listPassIds_0 nvarchar(4000)',@__listPassIds_0=N'["be8a8784-978d-4a68-9484-08dd095619d2","1579e349-0992-4a3b-6ee1-08dc9c32a29e"]'

my 2 cents

projection is made 'outside' of query scope making distinct work on all columns

Include provider and version information

EF Core version:

    <PackageVersion Include="Microsoft.EntityFrameworkCore" Version="9.0.0" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.SqlServer" Version="9.0.0" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.0" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Relational" Version="9.0.0" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Tools" Version="9.0.0" />

Database provider: SqlServer 2022 on docker
Target framework: .NET 9
Operating system: Windows 11
IDE: Visual Studio 2022 17.12.3

[roji]

I cannot reproduce this: that's why it's always best for users to submit an actual runnable repro, rather than an isolated code snippet as above, which usually isn't enough to reproduce the problem.

I've reconstructed a minimal console program around your query, and it projects only PassId with DISTINCT, as expected. You can try tweaking that code to show what's needed to make it behave incorrectly.

Attempted repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

int[] listPassIds = [1, 2, 3];

var query = context.Affaires
        .Where(a => a.StatutAffaire == StatutAffaire.Won)
        .Where(a => a.ReferenceId == null)
        .Where(a => listPassIds.Contains(a.EntityId))
    ;

var passIds = await query
    .Select(a => a.PassId) // projection
    .Distinct() // distinct
    .ToArrayAsync();

public class BlogContext : DbContext
{
    public DbSet<Affaire> Affaires { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

public class Affaire
{
    [Key]
    public int EntityId { get; set; }
    public StatutAffaire StatutAffaire { get; set; }
    public string? ReferenceId { get; set; }
    public string? PassId { get; set; }
}

public enum StatutAffaire { Won }