Databases
StackOverflow2013
Postsdbo
POGroup/Sort by Date Year/Month, Partial Select with NHibernate (Projection? Transformation?)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGroup/Sort by Date Year/Month, Partial Select with NHibernate (Projection? Transformation?)
    text
    copied!<p>I'm building an <code>ArchivesController</code> for the <a href="http://www.funnelweblog.com/" rel="nofollow">open source asp.net MVC-3 blog platform FunnelWeb</a>. We have an model called "Entry" which represents a blog entry which has a DateTime property called "Published" for when this entry was published. The purpose of the proposed <code>ArchivesController</code> is to create a wordpress-like archives link table that shows a descending list of all years and months for which we have posts with links to an archive index like '/archive/2011/9' and a count for the number of posts in the year/month.</p> <p>ex:</p> <ul> <li>December 2011 (2 posts) </li> <li>November 2011 (4 posts) </li> <li>October 2011 (1 post)</li> </ul> <p>I'm not experienced with NHibernate and so wrote the initial query using <a href="/questions/tagged/linq" class="post-tag" title="show questions tagged 'linq'" rel="tag">linq</a> like this:</p> <pre><code>public class GetArchiveDatesQuery : IQuery&lt;ArchiveDate&gt; { public System.Collections.Generic.IEnumerable&lt;ArchiveDate&gt; Execute(ISession session, IDatabaseProvider databaseProvider) { var criteria = session.QueryOver&lt;Entry&gt;(); var archiveDates = from entry in criteria.List&lt;Entry&gt;() group entry by new { entry.Published.Year, entry.Published.Month } into entryGroup orderby entryGroup.Key.Year descending, entryGroup.Key.Month descending select new ArchiveDate() { Year = entryGroup.Key.Year, Month = entryGroup.Key.Month, EntryCount = entryGroup.Count() }; return archiveDates; } } </code></pre> <p>Where <code>ArchiveDate</code> is a new model I created to encapsulate the year-month-count information from this query.</p> <p>This works, but I'd prefer to push the work off to SQL instead of doing the grouping and sorting in C#. I imagine on an active blog that has been around for several years with hundreds or thousands of posts would be much better off to do this in SQL so we don't return unnecessary data (like the entry content).</p> <p>My question is how we can accomplish the above LINQ statement in an NHibernate fashion which results in the grouping/sorting occurring in SQL. I imagine it will involve some Criteria->Projection->Transformation sort of process.</p> <p>The part I'm stuck on is accessing the month portion and year portion of the DateTime property for grouping and sorting which is currently accessed by the .Net DateTime object.</p> <p>The blog engine is using NHibernate version 3.2.0.4000.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload