How do I get only unique month and year combinations?

Question

Using VBNET, MVC 3 and Entity Framework to write my first mvc application - a single user blog application. I am trying to create an archive sidebar that will render something like October 2011 and when the user clicks they will see all posts in october. Right now all my attempts show either duplicate dates - if there are 3 posts in october then i see october 2011 3 times or i only get back one month year combo say oct 2011.

Using groupby with firstordefault i only get back one month yaear combo.

posts = _rdsqlconn.Posts.Where(Function(p) p.PostIsPublished = True).GroupBy(Function(d) d.PostDatePublished).FirstOrDefault

How can i get back unique month year combos with EF?

---

Additional info

I have that function in my repository. I want to pull the month and year pairs so that i have only one pair for say ocotober even if there are 3 posts in october. In the repository:

Public Function SelectPostsByDate() As IEnumerable(Of Entities.Post) Implements Interfaces.IPostRepository.SelectPostsByDate  

 Using _rdsqlconn As New RDSQLConn
            Dim posts
            posts = _rdsqlconn.Posts.Where(Function(p) p.PostIsPublished = True).GroupBy(Function(p) New With {p.PostDateCreated.Year, p.PostDateCreated.Month}).Select(Function(g) g.Key)
            'posts = _rdsqlconn.Posts.Where(Function(p) p.PostIsPublished = True).GroupBy(Function(p) New With {p.PostDatePublished.Value.Year, p.PostDatePublished.Value.Month})
            Return posts
        End Using
    End Function

In my controller i have

  Function DateViewPartial() As PartialViewResult
        Return PartialView(_postRepository.SelectPostsByDate)
    End Function

My partial view has:

      @ModelType IEnumerable (of RiderDesignMvcBlog.Core.Entities.Post)
    <hr />
    <ul style="list-style: none; margin-left:-35px;"> 
   @For Each item In Model       
      @<li> @Html.ActionLink(item.PostDatePublished.Value.ToString("Y"), "Archives", "Blog", New With {.year = item.PostDatePublished.Value.Year, .month = item.PostDatePublished.Value.Month}, Nothing)</li>      
     Next
     </ul>

In _Layout.vbhtml i call the partial view to render in the sidebar:

 <h3>Posts by Date</h3>
        @code
            Html.RenderAction("DateViewPartial", "Blog")
        End Code   

Answer 1

I would try this (in C#):

var yearMonths = _rdsqlconn.Posts
    .Where(p => p.PostIsPublished)
    .GroupBy(p => new { p.PostDatePublished.Year, p.PostDatePublished.Month })
    .Select(a => a.Key)
    .ToList();

It gives you a list of anonymous objects. Each object has a Year and a Month property - for instance yearMonths[0].Year and yearMonths[0].Month, etc. By applying the Select you actually throw away the elements in each group and you get only a list of group keys (year and month).

Edit

I think, for your purpose the best way is to introduce a "ViewModel" for your sidebar partial view. The ViewModel would describe the year and month group, for instance:

public class ArchiveMonthViewModel
{
    public int Year { get; set; }
    public int Month { get; set; }
}

Then you don't group with an anonymous type but use this ViewModel type:

var archiveViewModels = _rdsqlconn.Posts
    .Where(p => p.PostIsPublished)
    .GroupBy(p => new ArchiveMonthViewModel
    {
        Year = p.PostDatePublished.Year,
        Month = p.PostDatePublished.Month
    })
    .Select(a => a.Key)
    .ToList();

archiveViewModels is now a named type: List<ArchiveMonthViewModel> which you can return from your method:

public IEnumerable<ArchiveMonthViewModel> SelectPostsByDate()
{
    // code above ...
    return archiveViewModels;
}

Now your partial view should be based on a model of type IEnumerable<ArchiveMonthViewModel> (and not IEnumerable<Post>). In your foreach loop (@For Each item In Model) you pull out the ArchiveMonthViewModel elements which are the item in the loop now and then create the action link using item.Year and item.Month.

(Hopefully you can translate this sketch into VB.)

Answer 2

In VB:

Dim groupedPosts = _rdsqlcon.Posts.
        Where(Function(p) p.PostIsPublished = True).
        GroupBy(Function(p) New With {p.PostDatePublished.Year, p.PostDatePublished.Month }).
        Select(g => g.Key)

This just returns the unique Years and Months. If you want to include the Posts for each, try the following:

Dim groupedPosts = _rdsqlcon.Posts.
            Where(Function(p) p.PostIsPublished = True).
            GroupBy(Function(p) New With {p.PostDatePublished.Year, p.PostDatePublished.Month 

From there, you can show the year/month groupings and the associated posts for each:

For Each group In groupedPosts
   Console.WriteLine(group.Key.Year & "-" & group.Key.Month)
   For Each post In group
      Console.WriteLine(post.PostDatePublished)
   Next
Next