Entity Framework Distinct with OrderBy

Question

I use Entity Framework 6.2 and noticed that Distinct ignores my OrderBy clause. I have class:

public class Order
{
    public int Id { get; set; }

    public string PartnerId { get; set; }

    // Other properties removed for clarity.
}

And want to get last 20 partners of my orders:

var list = db.Orders.AsNoTracking()
.OrderByDescending(obj => obj.Id)
.Select(x => x.PartnerId).Distinct().Take(20).ToList();

The generated SQL query ignoring my OrderBy clause:

SELECT 
[Limit1].[PartnerId] AS [PartnerId]
FROM ( SELECT DISTINCT TOP (20) 
    [Extent1].[PartnerId] AS [PartnerId]
    FROM [dbo].[Orders] AS [Extent1]
)  AS [Limit1]

How can I get exactly last 20 partners from orders?

Answer 1

Your problem is that you are sorting before using Distinct. As taken from your comments, you said you cant order by Id because its not available. You will have to include the Id in the Select query then, however that Id is unique so the distinct could include duplicate partner entries.

Your only option is to use the Id field to preserve some order (since Ids are incremental) with an aggregate function.

var list = db.Orders.AsNoTracking()
    .GroupBy(order => order.PartnerId)
    .Select(group => new { 
        PartnerId = x.Key,
        LastId = x.Max(order => order.Id)
    })
    .OrderByDescending(x => x.LastId)
    .Take(20)
    .Select(x => x.PartnerId)
    .ToList();

This works as Ids are usually incremental, but a date column would usually be preferred for this query.

I hope this helps.