开发者

Linq to NHibernate: Select multiple sums at once

Is there a way to select multiple sums at once using Linq to NHibernate?

Now I have

int? wordCount = (from translation in session.Query<TmTranslation>()
                  where translation.SatisfiesCondition
                  select translation.TranslationUnit)
                 .Sum(x => (int?)(x.WordCount + x.NumberCount)) ?? 0;

int? tagCount = (from translation in session.Query<TmTranslation>()
                 where translation.SatisfiesCondition
                 select translation.TranslationUnit)
                .Sum(x => (int?)(x.TagCount)) ?? 0;

int? characterCount = (from translation in session.Query<TmTranslation>()
                       where translation.SatisfiesCondition
                       select translation.TranslationUnit)
                      .Sum(x => (int?)(x.CharacterCount)) ?? 0;

which generates three 开发者_运维知识库different SQL queries. In SQL I can grab them all three at once, but is there a way to do this in Linq to NHibernate?

Thank you.


this should help get you started with QueryOver way...

ResultDTO dtoAlias = null; //placeholder alias variable

var dto = session.OueryOver<TmTranslation>()
    .Where(x => x.SatisfiesCondition)
    //Change this to the actual type of Translation property
    .JoinQueryOver<Translation>(x => x.Translation)
    .SelectList(list => list
        //we can sum these columns individually to keep query simple,...add them together later
        .SelectSum(x => x.WordCount).WithAlias(() => dtoAlias.WordCountTotal)
        .SelectSum(x => x.NumberCount).WithAlias(() => dtoAlias.NumberCountTotal)
        //add more select sums to the select list
        )
    .TransformUsing(Transformers.AliasToBean<ResultDTO>())
    .SingleOrDefault<ResultDTO>();


Having multiple aggregate functions within a Select() call works and results in a single SQL command sent to the database. For example:

var result = session.Query<TmAssignment>()
                    .Select(a => new
                    {
                       Words = session.Query<TmTranslation>().Where(s => s.Assignment == a).Sum(u => (int?) u.WordCount) ?? 0,
                       Others = session.Query<TmTranslation>().Where(s => s.Assignment == a).Sum(u => (int?)(u.TagCount + u.NumberCount)) ?? 0,
                    }).ToList();


A potentially simpler solution I use is to do an artificial GroupBy then project to an anonymous object:

eg.

session.Query<TmTranslation>()
  .Where(o => o.SatisfiesCondition)
  .Select(o => o.TranslationUnit)
  .GroupBy(o => 1)
  .Select(o => new 
   {
     WordCount = o.Sum(x => (int?)(x.WordCount + x.NumberCount)) ?? 0,
     TagCount = o.Sum(x => (int?)(x.TagCount)) ?? 0,
     CharacterCount = o.Sum(x => (int?)(x.CharacterCount)) ?? 0
   })
  .Single(); 

This also produces just a single SQL statement and reduces the duplication nicely.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜