按日期年/月分组/排序，使用NHibernate进行部分选择(投影?转换?) [英] Group/Sort by Date Year/Month, Partial Select with NHibernate (Projection? Transformation?)

问题描述

我正在为开源asp.net MVC-3博客平台FunnelWeb 构建ArchivesController .我们有一个名为"Entry"的模型，该模型表示一个博客条目，该博客条目的DateTime属性称为"Published"，用于发布该条目.建议的ArchivesController的目的是创建一个类似于wordpress的档案链接表，该表显示了所有年份和月份的降序列表，在这些表中，我们发布了带有指向诸如"/archive/2011/9"的档案索引和一个链接的链接.计算年/月中的帖子数.

I'm building an ArchivesController for the open source asp.net MVC-3 blog platform FunnelWeb. 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 ArchivesController 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.

例如:

• 2011年12月(2个帖子)
• 2011年11月(4个帖子)
• 2011年10月(1个帖子)

我不熟悉NHibernate，因此使用 linq 像这样:

I'm not experienced with NHibernate and so wrote the initial query using linq like this:

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;
    }
}

其中ArchiveDate是我创建的新模型，用于封装此查询中的年月数信息.

Where ArchiveDate is a new model I created to encapsulate the year-month-count information from this query.

这可行，但是我更愿意将工作推到SQL，而不是在C#中进行分组和排序.我想在一个活跃的博客上写了好几百年或数千年的博客，在SQL中执行此操作会更好，这样我们就不会返回不必要的数据(如条目内容).

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).

我的问题是我们如何以NHibernate的方式完成上述LINQ语句，从而导致在SQL中发生分组/排序.我想它将涉及一些Criteria-> Projection-> Transformation这类过程.

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.

我遇到的问题是访问DateTime属性的月份部分和年份部分以进行分组和排序，这是当前由.Net DateTime对象访问的.

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.

博客引擎正在使用NHibernate版本3.2.0.4000.

The blog engine is using NHibernate version 3.2.0.4000.

推荐答案

更新:

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提供程序并发布错误吗?

could you try NHibernates LINQ-provider first and post the errors?

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()
                   };

这篇关于按日期年/月分组/排序，使用NHibernate进行部分选择(投影?转换?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！