nhibernate按联接分组查询 [英] nhibernate group by join query

问题描述

我有以下实体:

public class Shift
{
    public virtual int ShiftId { get; set; }
    public virtual string ShiftDesc { get; set; }

    public virtual IList<ShiftHistory> ShiftHistory { get; set; }
}

public class ShiftHistory
{
    public virtual System.DateTime ShiftStartLocal { get; set; }
    public virtual System.DateTime ShiftEndLocal { get; set; }

    public virtual Zone Zone { get; set; }
    public virtual Shift Shift { get; set; }

    public virtual int RowId { get; set; }
}

public class Zone
{
    public virtual int EntId { get; set; }
    public virtual string EntName { get; set; }
}

班次是班次的定义，区域是人们工作的地方，而班次历史记录是在某个区域进行班次的历史数据.

A shift is a shift definition, zone is a place where people work and shift history is history data of when a shift was run at a zone.

我需要的是上一次对所有区域运行一次班次.

/*对不起，我含糊.每个班次可以有多个区域的历史记录.我需要的是任何NH查询类型中每个班次的最新历史记录，而不论区域. */

/* Sorry, I was vague. Every shift can have a history at multiple zones. What I need is the latest history irrespective of the zone, for each shift, in any NH query type. */

但这不是那么简单，至少现在不适合我，而且我花了太多时间在此上，并最终求助于执行sql查询:

But this is not so simple, at least not for me now, and I've spend too much time on this, and have finally resorted to doing a sql query:

public class GetAllShiftSchedules : AbstractQueryObject<IList<ShiftScheduleResult>>
{
    public override IList<ShiftScheduleResult> GetResult()
    {   
        //use distinct because of the zones - if a shift is run at multiple zones, it ends at the same time, no? :)
        var sql =
            @"
            select distinct
                sh.ShiftId, s.ShiftDesc, sh.ShiftStartLocal, sh_inner.LatestEndTimeLocal
                from ShiftHistory sh
                  inner join Shift s on s.ShiftId = sh.ShiftId
                  inner join 
                  (
                    select ShiftId, max(ShiftEndLocal) as LatestEndTimeLocal from ShiftHistory group by ShiftId
                  )  
                   sh_inner on sh_inner.ShiftId = sh.ShiftId and sh_inner.LatestEndTimeLocal = sh.ShiftEndLocal
            ";

        var res = this.Session.CreateSQLQuery(sql).List().OfType<object[]>().Select(p => new ShiftScheduleResult(p)).ToList();

        return res;
    }
}

public class ShiftScheduleResult
{
    public int ShiftId { get; private set; }
    public string ShiftDesc { get; private set; }
    public DateTime LastShiftStartLocal { get; private set; }
    public DateTime LastShiftEndLocal { get; private set; }

    internal ShiftScheduleResult(object[] data)
    {
        this.ShiftId = (int)data[0];
        this.ShiftDesc = (string)data[1];
        this.LastShiftStartLocal = (DateTime)data[2];
        this.LastShiftEndLocal = (DateTime)data[3];
    }
}

我最接近的是:

        var innerSubquery =
            QueryOver.Of<ShiftHistory>()
                .Select(
                    Projections.Group<ShiftHistory>(e => e.Shift),
                    Projections.Max<ShiftHistory>(e => e.ShiftEndLocal)); 

        IList<Shift> shifts =
            this.Session.QueryOver<Shift>()
                .JoinQueryOver<ShiftHistory>(p => p.ShiftHistory)
                .Where(Subqueries.WhereProperty<ShiftHistory>(p => p.ShiftEndLocal).Eq(innerSubquery)).List();  

哪个生成了以下sql:

Which generated the following sql:

SELECT
...
 FROM
  Shift this_
   inner join ShiftHistory shifthisto1_ on this_.ShiftId=shifthisto1_.ShiftId
 WHERE
  shifthisto1_.ShiftEndLocal = 
  (
  SELECT this_0_.ShiftId as y0_, max(this_0_.ShiftEndLocal) as y1_ FROM ShiftHistory this_0_ GROUP BY this_0_.ShiftId
  )

这当然会失败，因为group by select返回了多个值，我们无法将其与ShEndLoc进行比较. 如果我可以得到分组投影以仅返回最大日期，那么这实际上对我有用.但是，要使其完整，我们还应该能够按班次ID进行比较.

Which of course fails because the group by select returns multiple values and we can't compare this to ShEndLoc. This would actually work for me, if I could get the grouping projection to return only the max date. However, to make it complete, we should also be able to compare by shift id.

我很高兴返回DTO，但是拥有实际的班次或包含班次的历史记录将很酷.

I am fine with returning the DTO but having an actual shift or a shift history included would be cool.

感谢阅读.

推荐答案

        var list = 
            this.Session.Query<Shift>().Select(
                p => new
                {
                    Shift = p,
                    LastShiftHistory =
                        this.Session.Query<ShiftHistory>()
                            .Where(sh => sh.Shift == p)
                            .OrderByDescending(sh => sh.ShiftEndLocal)
                            .Select(sh => sh)
                            .FirstOrDefault()
                })
                .ToList();

应该工作.最好将项目投影到DTO，因为子查询将延迟加载.

should work. It would be better though to project to a DTO, since the subquery will lazy load.

这篇关于nhibernate按联接分组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！