Linq2Sql how to get all distinct dates without time part

Question

I'm using SQL Server 2005 express I have a datetime field in a table witch contains date and time.

I want to select distinct the dates from the table ignoring the time part.

I used this code but the order by is ignored ! (the generated sql doesn't contain order by) :

        var dates = (from c in db.Comments
                     orderby c.Time descending
                     select c.Time.Day + "/" + c.Time.Month + "/" + c.Time.Year).Distinct();

Any ideas on how to do this are welcome.

Thanks

Answer 1

Try LinqPad, which lets you see the generated SQL. Play with different combinations.

Answer 2

Try this:

var comments = from c in db.Comments
               select c.Time.Day + "/" + c.Time.Month + "/" + c.Time.Year).Distinct();

comments = comments.OrderBy(c => c.Time);

Answer 3

Use the Date property of the DateTime type.

var dates = from c in db.Comments
             group c by c.Time.Date into g
             orderby g.Key
             select g.Key;

or

var dates = (from c in db.Comments
            select c.Time.Date).Distinct().OrderBy(d => d);

An SQL expert can tell you which of these is better.

Answer 4

order by is ignored!

Yes, you are instructing linq to order, and then to distinct. Distinct in linq is an operation that destroys ordering. You must orderby after the distinct:

query = query.Distinct().OrderBy(x => x);

Also, it is profane to use string manipulation to perform date logic. You should project using the Date property of DateTime.