LINQ and order-by a date field

Question

I've a very simple LINQ query to select dates from a table column - and I want to order by the date values:

var allDates = db.Table1.Select(m => m.Date).OrderBy(m => m.Date).Distinct();

But during runtime, I get the exception

"The specified type member 'Date' is not supported in LINQ to Entities."

In the model and the database, date-field is of type "DateTime". Selecting the values without ordering works fine.

score 3 · Accepted Answer · edited May 23 '17 at 12:34

3

You are selecting only the Date column, then you are trying to order by the property Date of this Date.

Instead:

var allDates = db.Table1.OrderBy(x => x.Date).Select(x => x.Date).Distinct();

or

var allDates = db.Table1.Select(x => x.Date).OrderBy(date => date).Distinct();

The order doesn't matter.

Update: Linq-To-Entities doesn't support the Date property of DateTime

If you need to truncate the time and you want to order by the date only, you could use EntityFunctions.TruncateTime:

var allDates = db.Table1.Select(x => EntityFunctions.TruncateTime(x.Date)).OrderBy(dt => dt).Distinct();

edited May 23 '17 at 12:34

Community

1
1

answered Oct 19 '16 at 07:50

Tim Schmelter

450,073
74
686
939

If I am not mistaken, `DateTime` has `Date` property, but it is not supported in *Linq to Entities* – Adil Mammadov Oct 19 '16 at 07:53
1

@AdilMammadov: i need coffee :D – Tim Schmelter Oct 19 '16 at 07:55
afaik that's it. you need to use `DbFunctions.TruncateTime(m.Date)` for it – Misiakw Oct 19 '16 at 07:56
Thank you so much ... wow and so fast :-) ... The hint with the truncated time is a nice addon I'll try. – Konrad Oct 19 '16 at 08:04

score 2 · Answer 2 · edited May 23 '17 at 10:32

as in question The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties

DateTime.Date cannot be converted to SQL. Use EntityFunctions.TruncateTime method to get date part.

take a look at something like this:

var allDates = db.Table1.Select(m => DbFunctions.TruncateTime(m.Date)).OrderBy(date => date).Distinct();

Mostafiz · Answer 3 · 2016-10-19T07:59:20.810

0

You can do this way

var allDates = db.Table1.Select(m => m.Date).OrderBy(m => m.Value.Year)
                         .ThenBy(m => m.Value.Month)
                         .ThenBy(m => m.Value.Day).Distinct();

edited Oct 19 '16 at 07:59

answered Oct 19 '16 at 07:52

Mostafiz

7,243
3
28
42

LINQ and order-by a date field

3 Answers3