efcore: Missing ORDER BY

Include your code

[Table("Table_1")]
public partial class Table1
{
    [Key]
    public int Id { get; set; }

    public DateTime CreateTime { get; set; }
}

public partial class TestContext : DbContext
{
    public TestContext()
    {
    }

    public TestContext(DbContextOptions<TestContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Table1> Table1 { get; set; }
}

var services = new ServiceCollection();
services.AddDbContext<TestContext>();
var serviceProvider = services.BuildServiceProvider();

var testContext = serviceProvider.GetRequiredService<TestContext>();
var sql = testContext.Table1.OrderBy(a => a.Id).Distinct().ToQueryString();
Console.WriteLine(sql);

Expected Behavior

SELECT DISTINCT [t].[Id], [t].[CreateTime]
FROM [Table_1] AS [t]
ORDER BY [t].[Id]

Actual Behavior

SELECT DISTINCT [t].[Id], [t].[CreateTime]
FROM [Table_1] AS [t]

Include provider and version information

EF Core version:6.0.0-preview.7.21378.4 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system:Win11 IDE: Visual Studio 2022 17.0

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 19 (19 by maintainers)

Most upvoted comments

A a very simple example where items are not returned in the order they are inserted:

var hashSet = new HashSet<int>();
for (var i = 0; i < 10; i++)
{
    hashSet.Add(i);
}

hashSet.Remove(2);
hashSet.Add(1000);

foreach (var i in hashSet)
{
    Console.WriteLine(i);
}

C:/local/AllTogetherNow/FiveOh/bin/Debug/net5.0/FiveOh.exe
0
1
1000
3
4
5
6
7
8
9

ajcvickers on Sep 3, 2021

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew#distinct-queries

@roji @ajcvickers why are there different behaviors on different database providers?

Varorbc on Nov 2, 2021

If I insert a collection in a specific order, it will also be returned in a specific order.

That is implementation detail and not the contract. It can be changed any time in future. We don’t rely on undocumented behavior. This has been discussed at length in past. (Github is not being really co-operative in finding the earlier issue where discussion took place). And we are not going to change the behavior. As said above, if ordering is important for results, then specify it after Distinct operator.

smitpatel on Sep 3, 2021

Since Distinct returns an unordered sequence, putting ORDER BY in SQL is unnecessary computation with perf penalty which is not required to generate results and hence removed. As @roji mentioned before if you want ordered sequence after Distinct operator, apply OrderBy after Distinct operator.

smitpatel on Aug 26, 2021

@Varorbc Distinct does not preserve ordering. As state in the docs:

The expected behavior is that it returns an unordered sequence of the unique items in source.

Apply your OrderBy after Distinct to get the results you want:

var sql = testContext.Table1.Distinct().OrderBy(a => a.Id).ToQueryString();

roji on Aug 25, 2021