GROUP BY和NHibernate的QueryOver HAVING子句 [英] GROUP BY and HAVING clauses in nHibernate QueryOver

问题描述

我试着写NHibernate的QueryOver语言这一特定的SQL查询，对此我不是很熟悉：

I'm trying to write this specific sql query in nHibernate QueryOver language, which I am not very familiar with:

SELECT MessageThreadId FROM MessageThreadAccesses
WHERE ProfileId IN (arr)
GROUP BY MessageThreadId
HAVING COUNT(MessageThreadId) = arr.Count

其中ARR是我传递作为参数，MessageThreadAccess实体整数（用户ID）的阵列看起来像这样：

where arr is a array of integers(user Ids) I'm passing as argument and MessageThreadAccess entity looks like this:

public virtual MessageThread MessageThread { get; set; }
public virtual Profile Profile { get; set; }
....

我得到这个读取多个栈溢出线程和实验后远我查询（试图让MessageThread对象 - 它应该始终只有一个或没有），但它仍然不工作，我真的不知道还有什么尝试。该查询似乎总是返回MessageThreadAccess对象，但阅读时，它的它总是NULL MessageThread属性。

After reading multiple stack overflow threads and experimenting I got this far with my query (trying to get MessageThread object - it should always be just one or none), but it still doesn't work and I'm not really sure what else to try. The query always seems to be returning the MessageThreadAccess object, but when reading it's MessageThread property it's always NULL.

var access = Session.QueryOver<MessageThreadAccess>()
    .WhereRestrictionOn(x => x.Profile).IsIn(participants.ToArray())
    .Select(Projections.ProjectionList()
        .Add(Projections.Group<MessageThreadAccess>(x => x.MessageThread))
    )
    .Where(
        Restrictions.Eq(Projections.Count<MessageThreadAccess>(x => x.MessageThread.Id), participants.Count)
    )
    .TransformUsing(Transformers.AliasToBean<MessageThreadAccess>())
    .SingleOrDefault();

return Session.QueryOver<MessageThread>()
    .Where(x => x.Id == access.MessageThread.Id)
    .SingleOrDefault();

有人能指出我在正确的方向，或解释什么我做错了？

Can someone point me in the right direction, or explain what am I doing wrong?

先谢谢了。

推荐答案

我想你可以尝试使用DTO用于存储结果，而不是试图以适应导致MessageThreadAccess，当它不是一（无资料）

I guess you may try using a DTO for storing the result, instead of trying to fit the result in a MessageThreadAccess, when it is not one (no Profile).

也许你可以试试：

public class MessageThreadCountDTO
{
    public MessageThread Thread { get; set; }
    public int Nb { get; set; }
}

然后

then

var profiles = new int[] { 1,2,3,4 };

MessageThreadCountDTO mtcDto = null;

var myResult = 
  _laSession.QueryOver<MessageThreadAccess>()
     .WhereRestrictionOn(x => x.Profile.Id).IsIn(profiles)
     .SelectList(list =>
         list.SelectGroup(x => x.MessageThread).WithAlias(() => mtcDto.Thread).
         SelectCount(x => x.MessageThread).WithAlias(() => mtcDto.Nb)
         )
     .Where(Restrictions.Eq(Projections.Count<MessageThreadAccess>(x => x.MessageThread), profiles.Count()))
     .TransformUsing(Transformers.AliasToBean<MessageThreadCountDTO>())
     .List<MessageThreadCountDTO>().FirstOrDefault();

将配置文件是一个配置文件[]，而不是一个int []，然后下面一行：

would profiles be a Profile[], and not an int[], then the following line :

.WhereRestrictionOn(x => x.Profile.Id).IsIn(profiles)

应该是：

.WhereRestrictionOn(x => x.Profile).IsIn(profiles)

希望这将有助于

这篇关于GROUP BY和NHibernate的QueryOver HAVING子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！