Entity Framework Core 2.1 How to OrderBy() and Distinct()

Question

Entity Framework Core 2.1 How to OrderBy() and Distinct()

.net entity-framework-core

Question

I want to select distinct from my table to get list of years So I wrote the code:

return await _context.SdrSettingHistory
                .Where( x => x.StartDate != null)
                .OrderBy(x => x.StartDate)
                .Select(x => x.StartDate.Value.Year)
                .Distinct()
                .ToListAsync();

But it returned not ordered year. it returned like [2014,2015,2013] How I suppose to order the year ?

I tried to move OrderBy after Distinct() it produce error:

error CS1061: 'int' does not contain a definition for 'StartDate' and no accessible extension method 'StartDate' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)

Note: I use ASP.NET with EF Core 2.1

nightingale2k1

View more on Stack Overflow

Douglas · Accepted Answer · 10/6/2018 11:58:07 AM

The Select and Distinct operators are not guaranteed to preserve order on other data providers, such as SQL Server. You need to move your OrderBy operator after them. Since your projected value after the Select is an int, you can just order by it directly:

return await _context.SdrSettingHistory
    .Where(x => x.StartDate != null)
    .Select(x => x.StartDate.Value.Year)
    .Distinct()
    .OrderBy(x => x)
    .ToListAsync();

Entity Framework Core 2.1 How to OrderBy() and Distinct()

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related