How to retrive distinct month/year values from a table using LINQ to SQL

Question

In a blog engine system I need to show all Month/Year pair values which we have blog posts in (e.g. January 2009, February 2009, ...)

Is it possible in LINQ to SQL?

Answer 1

This will get the groupings. dates will be an IEnumerable of anonymous object that have a property "Month" and "Year".

  System.Globalization.DateTimeFormatInfo provider = new System.Globalization.DateTimeFormatInfo();
                SODataContext cnx = new SODataContext();
                var dates = from item in cnx.Projects
                            group item by new { item.DateColumn.Value.Year, item.DateColumn.Value.Month } into grouping
                            select new { Month = provider.GetMonthName(grouping.Key.Month), grouping.Key.Year};