Distinct year-months with Linq (to entities)

Question

I have an entity set of Publications with a ReleaseDate property. I would like to get a List of all distinct year-&-month combos from this set for the purpose of creating a pagination widget.

Preferably, I'd like a list of DateTime values with the day as 1 for each distinct year-month from my publications set:

IEnumerable<DateTime> DistinctYearMonths = from p in context.Publications.  .... ?

How can I finish this linq-to-entities query?

Answer 1

IEnumerable<DateTime> DistinctYearMonths = context.Publications
    .Select(p => new { p.ReleaseDate.Year, p.ReleaseDate.Month })
    .Distinct()
    .ToList() // excutes query
    .Select(x => new DateTime(x.Year, x.Month, 1)); // copy anonymous objects
                                                    // into DateTime in memory

The intermediate step to project into an anonymous type is necessary because you cannot directly project into a DateTime (constructors with parameters are not supported in projections in LINQ to Entities and the Year and Month properties of DateTime are readonly, so you can't set them with initializer syntax (new DateTime { Year = p.ReleaseDate.Year, ... } is not possible)).

Answer 2

Try the following query:

(from p in publications
 select new DateTime(p.ReleaseDate.Year, p.ReleaseDate.Month, 1)).Distinct();