6

I am currently trying to add an order by to a LINQ query that will order by a datetime field in an EF object:

return this.SortingDirection.Equals("asc", StringComparison.InvariantCultureIgnoreCase) ? entities.OrderBy(e => e.ProcessStartTime) : entities.OrderByDescending(e => e.ProcessStartTime);

When the SortingDirection is set to desc it works fine. But when set to asc I get no records!

Upon looking at SQL Server Profiler, it turns out that DateTime objects are being formatted differently!

For DESC:

ORDER BY [Project1].[StartTime] DESC',N'...@p__linq__22='2015-01-07 09:00:23',@p__linq__23='2015-01-07 09:00:23',@p__linq__24='2015-01-07 09:05:30',@p__linq__25='2015-01-07 09:05:30'

and for ASC:

ORDER BY [Project1].[StartTime]  ASC',N'...@p__linq__22='2015-07-01 09:00:23',@p__linq__23='2015-07-01 09:00:23',@p__linq__24='2015-07-01 09:05:30',@p__linq__25='2015-07-01 09:05:30'

Days and months have been swapped, causing the sql query to return no results.

This to me suggests that the IQueryable.OrderBy() method is not using the correct local format / different format to OrderByDescending(), could this be a bug in EF?

Is there something in my connection string I could add to enforce this or another way I could sort by these dates?

My setup:

  • .NET 4.5
  • Entity Framework 5.0.0
  • SQL Server 2012 Standard

Many thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2831628
  • 191
  • 8
  • 1
    How come a *specific* datetime value, e.g. '2015-01-07', is involved in an order by clause? – Giorgos Betsos Jan 09 '15 at 14:07
  • possible duplicate of [MVC DateTime binding with incorrect date format](http://stackoverflow.com/questions/528545/mvc-datetime-binding-with-incorrect-date-format) – user2831628 Jan 09 '15 at 17:14

2 Answers2

2

You don't show your linq query, but two things come to mind immediately. First, SQL Server has its own globalization settings, and second, if dates are paramertized (which linq should always do) you shouldn't need to care about date string formats.

Cylon Cat
  • 7,111
  • 2
  • 25
  • 33
0

So the problem i'm having has nothing to do with SQL or EF, It turns out Mvc is the problem. I had not set the culture in the Web.Config to the Invariant culture ('en'). This was causing the filter condition (in this case the ProcessorStartTime) to be parsed as a US date string as .NET seems to default to en-US if no culture is set in the config (see this question).

I did not see this until I tried to sort the result set. We create a new MvcHtmlString link on the sort button that included the filter condition for ProcessorStartTime. This parsed the date string and reversed the days and months. When I clicked on the link, it gets parsesd back to a DateTime as is which trickled down to the LINQ, and therefore causing the sort to return no results (more info on mvc's date handling in this question.

The reason OrderByDescending() was working was that the sort button is a toggle bettween off|asc|desc, so it re-reversed days and months back to how they should be!

For future reference, make sure that you have got this set in your web config. I'll be marking this as a duplicate since its nothing new to SO. Thank you all for those that posted (esp. you Cylon, it pointed me in the right direction ^^)

Community
  • 1
  • 1
user2831628
  • 191
  • 8