CODEWITHSUNDEEP
c#entity-frameworklinq
Konrad
Konrad

Reputation: 4772

LINQ and order-by a date field

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.

Upvotes: 0

Views: 4494

Answers (3)

Mostafiz
Mostafiz

Reputation: 7352

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();

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460068

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();

Upvotes: 3

Misiakw
Misiakw

Reputation: 922

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();

Upvotes: 2

Related Questions