How to map blog archive using NHibernate

I would like to format a blog archive like below given Year=2008 and Month=2 (February):

2009
2008
- March
- February
  - Article C
  - Article B
  - Article A
- January
2007

I have the following classes:

public class BlogArchive {
    public int Year { get; set; }
    public int Month { get; set; }
    public List<BlogYear> Years { get; set; }
}

public class BlogYear {
    public virtual int Year { get; set; }
    public virtual List<BlogMonth> Months { get; set; }
}

public class BlogMonth {
    public virtual int Month { get; set; }
    public virtual List<BlogArticle> Articles { get; set; }
}

public class BlogArticle {
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual string Title { get; set; }
}

The table schema is like:

CREATE TABLE BlogArticles
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    ArticleDate datetime,
    Title varchar(100)
)

In SQL I would get the years like:

SELECT DISTINCT YEAR(ArticleDate) AS Year FROM BlogArticles ORDER BY Year DESC

and months for a year like:

SELECT DISTINCT MONTH(ArticleDate) AS Month FROM BlogArticles WHERE YEAR(ArticleDate) = @year ORDER BY Month DESC

How would I map these collections using (Fluent) NHibernate? It doesn't seem obvious because year and month have to be calculated from ArticleDate rather than directly corresponding to specific key columns.

Or would you recommend a completely different strategy?

Note: This design assumes lazy-loading so only relevant data would be loaded.

Thanks

ansaurus

tags:

views:

answers:

How to map blog archive using NHibernate

related questions