Format DateTime in an EF query

Question

I have a database table that holds information for received files. One of the columns is a DateTime that specifies when the file was received. I need to get a distinct list of months with the year (MM/YYYY) for files received. I need to get it out of this table. There can be thousands of records in this table so the way I have done it, in Oracle, is in my select statement I format the datetime as MM/YYYY and do a sort desc with a distinct clause on it. This give me a list of just the months that a file was received. Very fast and efficient.

Now I need to do this using EFv4....here's the query I used in Oracle. Anyone know how I can translate it using one of EFs ways of querying?

select distinct
    to_char( i.date_received, 'MMYYYY')) MonthAndYear
    from table1
    order by MonthAndYear desc

Answer 1

Well, don't do it like Oracle. Do it like LINQ.

var q = (from i in Context.Table1
         select new
         {
             Month = i.date_received.Month,
             Year = i.date_received.Year
         }).Distinct();

To get a DateTime out:

var r = q.AsEnumerable().Select(d => new DateTime(d.Year, d.Month, 1));