LINQ-To-SQL OrderBy with DateTime Not Working

Question

I have this LINQ query. The date is stored as a string in the database, but I need to order by it. So I convert it to a DateTime, but it doesn't order.

   (from m in dbDataContext.TimeCards
    where m.TIMECARDDATE != ""
    && m.TIMECARDDATE != null
    orderby Convert.ToDateTime(m.TIMECARDDATE) descending
    select Convert.ToDateTime(m.TIMECARDDATE)).Distinct().ToList();

Any idea why it doesn't work? I can't change the database, so I have to deal with the data the way it is. I get the data back ordered like this...

 2/27/2009
 2/26/2009
 2/25/2009
 2/28/2009
 2/24/2009

Solution 1

Try evaluating the expression without sorting, and then apply the sort to the results of Distinct:

(Linq-To-Sql-Expression).Distinct().OrderByDescending(x => x.TIMECARDDATE).ToList()

Solution 2

First you may use string.IsNullOrEmpty(), instead of checking whether the string is null or ""

Beside, this should work. You probably reorder it outside of this code.

Pay attention this isn't execute until you foreach it (call GetEnumerator()). So if you change something before iterating it.. that might be the reason.

p.s. is TIMECARDDATE is object or string?

Comments

[Ryan Alford]

It's actually executed when that line is executed because I am using the .ToList() which causes it to be evaluated at that time. TIMECARDDATE is a string.(sadly)

[Ryan Alford]

also, string.IsNullOrEmpty is not supported in LINQ-To-SQL

[Letterman]

i haven't noticed the ToList, and there is nothing to do with LINQ to SQL.... you can use string.IsNullOrEmpty on any string.

[Thiago Silva]

Distinct() does not guarantee that it will maintain the order of the items that went into it - just that it will return a distinct result set. So although the input to Distinct() was originally sorted properly, its output was not.