NHibernate Query

1) Sql Group by ….

 之前是这么写的,因为DateTime是YYYY-MM-DD HH:mm:SS 模式,我只想group 日期。这种写法再mysql,sqlserver、oracle都没问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
var startTime = new DateTime(start.Year, start.Month, start.Day);
          var endTime = new DateTime(end.Year, end.Month, end.Day, 23, 59, 59, 999);
          var count = DetachedCriteria.For<User>("user")
              .SetProjection(
                  Projections.ProjectionList()
                  .Add(Projections.SqlGroupProjection("year(CreateTime)", "year(CreateTime)", new[] { "year" }, new IType[] { NHibernateUtil.Int32 }))
                  .Add(Projections.SqlGroupProjection("Month(CreateTime)", "Month(CreateTime)", new[] { "Month" }, new IType[] { NHibernateUtil.Int32 }))
                  .Add(Projections.SqlGroupProjection("day(CreateTime)", "day(CreateTime)", new[] { "day" }, new IType[] { NHibernateUtil.Int32 }))
                  .Add(Projections.RowCount())
                  )
              .Add(Restrictions.Gt(Projections.Property<User>(s => s.CreateTime), startTime))
              .Add(Restrictions.Le(Projections.Property<User>(s => s.CreateTime), endTime))
              .GetExecutableCriteria(this.CurrentSession)
              .List();
          Dictionary<DateTime, int> dictionary = new Dictionary<DateTime, int>();
          foreach (object[] objects in count)
          {
              dictionary.Add(new DateTime((int) objects[0], (int) objects[1], (int) objects[2]), (int) objects[3]);
          }
          return dictionary;

后来发现还有个可以做就是 date 方法

Projections.SqlFunction("date", NHibernateUtil.Date, Projections.Property(_=>_.CreateTime);

 

2) 一对多查询

例如 

标签: NHibernate, sql, critira, group

绿色通道:
好文要顶
关注我
收藏该文与我联系

沉默的糕点

关注 – 1

粉丝 – 11

+加关注

0

0

(请您对文章做出评价)

« 上一篇:Ayende关于S#arp的看法(Taking a look at S#arp Lite)只是连接
» 下一篇:asp.net报错“尝试读取或写入受保护的内存。这通常指示其他内存已损坏”的解决办法

posted @ 2013-06-25 10:56 沉默的糕点 阅读(50) 评论(0) 编辑 收藏

发表评论

您的电子邮箱地址不会被公开。