Geek asks and answers

Group / Sort by date Year / Month, Partial selection using NHibernate (Projection? Transformation?)

I create ArchivesControllerfor open source asp.net MVC-3 FunnelWeb blog . We have a model called "Entry" that represents a blog post that has a DateTime property called "Published" when this publication was published. The aim of the proposed one ArchivesControlleris to create a table of links to files in the form of wordpress, which shows a descending list of all years and months for which we have messages with links to the archive index, such as '/ archive / 2011/9' and count the number of messages in a year / month.

Example:

  • December 2011 (2 posts)
  • November 2011 (4 posts)
  • October 2011 (1 post)

I have no experience with NHibernate, so I wrote the initial request using linq:

public class GetArchiveDatesQuery : IQuery<ArchiveDate>
{
    public System.Collections.Generic.IEnumerable<ArchiveDate> Execute(ISession session, IDatabaseProvider databaseProvider)
    {
        var criteria = session.QueryOver<Entry>();

        var archiveDates = from entry in criteria.List<Entry>()
                                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;
    }
}

Where ArchiveDateis the new model that I created to encapsulate information from year to month from this request.

This works, but I would rather pull out the work in SQL instead of doing grouping and sorting in C #. I believe that in an active blog that has existed for several years, with hundreds or thousands of posts, it would be much better to do this in SQL so that we do not return unnecessary data (for example, the contents of the record).

My question is how can we execute the above LINQ statement in the NHibernate module, which leads to grouping / sorting occurring in SQL. I assume that this will be associated with some of the processes "Criteria-> Projection-> Transformation".

, , - DateTime , .Net DateTime.

NHibernate 3.2.0.4000.

+1
1

Update:

var query = from e in session.Query<Entry>()
            group e by new { e.Published.Year, e.Published.Month } into entryGroup
            select new
            {
                entryGroup.First().Published,
                EntryCount = entryGroup.Count()
            };

var archiveDates = from a in query.AsEnumerable()
                   orderby a.Published.Year descending, a.Published.Month descending
                   select new
                   {
                       Year = a.Published.Year,
                       Month = a.Published.Month,
                       EntryCount = a.EntryCount,
                   };

:

NHibernates LINQ-provider ?

using NHibernate.Linq;


var archiveDates = from entry in session.Query<Entry>()
                   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()
                   };
0

More articles:


All Articles