Sort Datetime field only by Date part

Question

I have a property of an entity that’s like DateTime? and in SQL Server is of the type datetime also. I need to make a query that sorts by date, but without considering the time, because in this specific view it will not show the time. Then sort by code.

Make two ordinations I get with ThenBy, I don’t know how to ignore the time.

var query = contexto.Contas.OrderBy(c => c.Data).ThenBy(c => c.Codigo);

Suppose this data

001 | 22/12/2016 13:25

002 | 22/12/2016 11:25

003 | 22/12/2016 12:25

---

Today is displayed like this:

002 | 22/12/2016

003 | 22/12/2016

001 | 22/12/2016

---

But it should be:

001 22/12/2016

002 22/12/2016

003 22/12/2016

Answer 1

Entity Framework 6

Use DbFunctions.TruncateTime.

contexto.Contas.OrderBy(c => DbFunctions.TruncateTime(c.Data)).ThenBy(c => c.Codigo);

Entity Framework <= 5

Use EntityFunctions.TruncateTime.

contexto.Contas.OrderBy(c => EntityFunctions.TruncateTime(c.Data)).ThenBy(c => c.Codigo);

These functions will remove the part of the time, considering only the date.

---

In practice, this is the same thing as using c.Data.Date. The point is that the Entity Framework cannot "convert" this c.Data.Date for a query valid.

Using the .ToList(), you end up bringing all the data to memory and they will be ordered in the application. If you know what you are doing, it may be a valid alternative, but I (practically) always think it is best to bring the already ordered data.

Answer 2

An alternative would be for you to give one ToList before ordering, thus staying

var query = contexto.Contas.ToList().OrderBy(c => c.Data.Date).ThenBy(c => c.Codigo);