Sort by (asc | desc) in linq for SQL Server handles DateTime otherwise

Question

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

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

If SortingDirection set to desc , it works fine. But when asc , I don't get any records!

Looking at the SQL Server Profiler, it turns out that DateTime objects are 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 replaced, as a result of which the sql query does not return any results.

This for me suggests that the IQueryable.OrderBy() method does not use the correct local format / other format for OrderByDescending() , could this be an error in EF?

Is there anything in my connection string that I could add to apply this or another way that I could sort by these dates?

My setup:

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

Thank you very much

Answer 1

You do not show your linq request, but two things immediately come to mind. Firstly, SQL Server has its own globalization parameters, and secondly, if dates are parameterized (which you should always do with linq), you don’t have to worry about date string formats.

Answer 2

So, the problem I am facing has nothing to do with SQL or EF. It turns out the mvc problem. I did not set the culture in Web.Config for invariant culture ('en'). This led to the filter condition (in this case, ProcessorStartTime ) being parsed as a date string in the USA, since .NET is considered en-US by default if there is no culture in the configuration (see. This is a question ).

I did not see this until I tried to sort the result set. We create a new MvcHtmlString link on the sort button, which included a filter condition for ProcessorStartTime . It parsed the date string and changed the days and months. When I clicked on the link, it again goes back to DateTime , which flows into LINQ, and therefore sorting does not return results (additional information on mvc data processing in this question .

The reason OrderByDescending() worked was because the sort button is a toggle between the "asc | desc" character, so it brings days and months back to how they should be!

For future reference, make sure this kit is installed in your web configuration. I will mark this as a duplicate, since there is nothing new for SO. Thanks to everyone for posting (for example, you, Cylon, pointed me in the right direction ^^)