How to use NHibernate ICriteria to group, collect associations and T-SQL functions

Question

I want to create the following T-SQL statement:

SELECT SUM (sa.Amount) as 'SumAmount', SUM(sa.Cost) as 'SumCost', gg.[Description] as 'Goodsgroup', Month(sa.[Date]) as 'Month' FROM SalesmanArticle sa INNER JOIN Article a ON a.ArticleId = sa.ArticleId INNER JOIN GoodsGroup gg ON gg.GoodsGroupId = a.GoodsGroupId GROUP BY gg.[Description], Month(sa.[Date]) ORDER BY 'Month', 'Goodsgroup' 

Is this possible with NHibernates ICriteria?

How can I use the month-T-SQL function?

Do I need to manually connect or does the ICriteria API know that when I use propetyName 'SalesmanArticle.Agticle.Goodsgroup.Description', should it join the article and product group?

EDIT:

Now I wrote this code here:

 // typesafe properties string article = typeof(Article).Name; string goodsGroup = typeof(GoodsGroup).Name; string salesmanArticle = typeof(SalesmanArticle).Name; string amount = Reflector.GetPropertyName<SalesmanArticle>(x => x.Amount); string cost = Reflector.GetPropertyName<SalesmanArticle>(x => x.Cost); string description = string.Format("{0}.{1}", goodsGroup, Reflector.GetPropertyName<SalesmanArticle>(x => x.Article.GoodsGroup.Description)); string date = Reflector.GetPropertyName<SalesmanArticle>(x => x.Date); string formatedDate = string.Format("MONTH([{0}])", date); return GetSession() // FROM .CreateCriteria(typeof(SalesmanArticle), salesmanArticle) // JOIN .CreateCriteria(article, article, JoinType.InnerJoin) .CreateCriteria(goodsGroup, goodsGroup, JoinType.InnerJoin) // SELECT .SetProjection(Projections.ProjectionList() .Add(Projections.Sum(amount)) .Add(Projections.Sum(cost)) // GROUP BY .Add(Projections.GroupProperty(description)) .Add(Projections.SqlGroupProjection(formatedDate, formatedDate, new[]{"MyDate"} , new[] { NHibernateUtil.Int32 }))) .List(); 

But an AdoException exception:

Failed to execute the query [SELECT sum (this_.Amount) as y0_, sum (this_.Cost) as y1_, goodsgroup2_.Description as y2_, MONTH ([Date]) FROM [Seller] this_ internal join [Article] article1_ on this_. ArticleId = article1_.ArticleId internal union [GoodsGroup] productgroup2_ on article1_.GoodsGroupId = goodsgroup2_.GoodsGroupId GROUP BY goodsgroup2_.Description, MONTH ([Date])]

[SQL: SELECT sum (this_.Amount) as y0_, sum (this_.Cost) as y1_, goodsgroup2_.Description as y2_, MONTH ([Date]) FROM [Seller] this_ internal join [Article] article1_ on this_.ArticleId = article1_.ArticleId internal union [GoodsGroup] product group2_ on article1_.GoodsGroupId = goodsgroup2_.GoodsGroupId GROUP BY goodsgroup2_.Description, MONTH ([Date])]

Is it strange that NHibernate is trying to create 2 queries ?!

And BOTH of them are true!

Instead of a code line

 .Add(Projections.SqlGroupProjection(formatedDate, formatedDate, new[]{"MyDate"} , new[] { NHibernateUtil.Int32 }))) 

I used

 .Add(Projections.SqlFunction("MONTH", NHibernateUtil.Int32, Projections.GroupProperty(date)))) 

The problem with SqlFunction is that it creates GROUP BY sa.Date instead of MONTH (sa.Date). But this method worked syntactically correctly.

So, I switched to the SqlGroupProjection method.

But in any case, this will not work.

Can someone help me?

Answer 1

I solved it. Here is the correct code:

 public class SalesmanArticleRepository : Repository<SalesmanArticle>, ISalesmanArticleRepository { public IList GetAllAll() { // typesafe properties string article = typeof(Article).Name; string goodsGroup = typeof(GoodsGroup).Name; string salesmanArticle = typeof(SalesmanArticle).Name; string amount = Reflector.GetPropertyName<SalesmanArticle>(x => x.Amount); string cost = Reflector.GetPropertyName<SalesmanArticle>(x => x.Cost); string description = string.Format("{0}.{1}", goodsGroup, Reflector.GetPropertyName<SalesmanArticle>(x => x.Article.GoodsGroup.Description)); string date = Reflector.GetPropertyName<SalesmanArticle>(x => x.Date); string formatedDateSql = string.Format("month({{alias}}.[{0}]) as mydate", date); string formatedDateGroupBy = string.Format("month({{alias}}.[{0}])", date); return GetSession() // FROM .CreateCriteria(typeof(SalesmanArticle), salesmanArticle) // JOIN .CreateCriteria(article, article, JoinType.InnerJoin) .CreateCriteria(goodsGroup, goodsGroup, JoinType.InnerJoin) // SELECT .SetProjection(Projections.ProjectionList() .Add(Projections.Sum(amount)) .Add(Projections.Sum(cost)) // GROUP BY .Add(Projections.GroupProperty(description)) .Add(Projections.SqlGroupProjection(formatedDateSql, formatedDateGroupBy, new[] { "mydate" }, new[] { NHibernateUtil.Int32 }))) .List(); } }