nhibernate:选择父母和最新的孩子 [英] nhibernate: select parents and newest child
问题描述
我有以下实体
public class Vehicle
{
public virtual string InternalNumber { get; set; }
public virtual IList<VehicleDriverHistory> DriverHistory { get; set; }
}
public class VehicleDriverHistory : HistoryEntity
{
public virtual Vehicle Vehicle { get; set; }
public virtual DriverPerson Driver { get; set; }
public virtual DateTime Date { get; set; }
}
现在,我需要根据日期"选择每辆车,如果有的话,选择最新的VehicleDriverHistory-Entry. 但是我有点挣扎,我在sql中没问题,但是到目前为止在nhibernate中都失败了.
Now I need to select every Vehicle and, if present, the newest VehicleDriverHistory-Entry according to "Date". But I'm kinda struggling, I have no problem doing it in sql, but failed in nhibernate so far.
到目前为止我所拥有的:
What I have so far:
VehicleDriverHistory vdHistory = null;
VehicleDriverHistory vdHistory2 = null;
q.Left.JoinAlias(x => x.DriverHistory, () => vdHistory);
q.Left.JoinAlias(x => x.DriverHistory, () => vdHistory2).Where(x => vdHistory2.Date > vdHistory.Date);
q.Where(x => vdHistory2.Id == null);
这不起作用,这只是我尝试将sql查询(产生正确的数据)翻译"为nhibernate.
This doesn't work and it was just my attempt to "translate" the sql query (which yields the correct data) to nhibernate.
所以,我将如何选择父母和最新的孩子(如果没有父母,则不选择)
So, how would I select parents and the newest child (or none if none is present)
谢谢
更新
在Firos的帮助下,我得出了以下结论:
With Firos help I ended up with the following:
VehicleDriverHistory historyAlias = null;
var maxDateQuery = QueryOver.Of<VehicleDriverHistory>()
.Where(h => h.Vehicle == historyAlias.Vehicle)
.Select(Projections.Max<VehicleDriverHistory>(h => h.Date));
var vehiclesWithEntry = DataSession.Current.QueryOver(() => historyAlias)
.WithSubquery.WhereProperty(h => h.Date).Eq(maxDateQuery)
.Fetch(h => h.Vehicle).Eager
.Future();
Vehicle VehicleAlias = null;
var vehiclesWithoutEntry = DataSession.Current.QueryOver<Vehicle>(() => VehicleAlias)
.WithSubquery
.WhereNotExists(QueryOver.Of<VehicleDriverHistory>()
.Where(x => x.Vehicle.Id == VehicleAlias.Id).Select(x => x.Id))
.Future();
return vehiclesWithEntry.Select(h => new PairDTO { Vehicle = h.Vehicle, NewestHistoryEntry = h }).Concat(vehiclesWithoutEntry.Select(v => new PairDTO { Vehicle = v })).ToList();
我不得不用子查询子句替换vehclesWithoutEntry中的Any()语句,因为它引发了异常.
I had to replace the Any() statement in vehclesWithoutEntry with a subquery-clause because it raised an exception.
推荐答案
使用期货仅进行一次往返的其他想法
some other idea using Futures to make only one roundtrip
VehicleDriverHistory historyAlias = null;
var maxDateQuery = QueryOver.Of<VehicleDriverHistory>()
.Where(h => h.Vehicle == historyAlias.Vehicle)
.Select(Projections.Max<VehicleDriverHistory>(h => h.Date));
var vehiclesWithEntry = session.QueryOver(() => historyAlias)
.WithSubquery.WhereProperty(h => h.Date).Eq(maxDateQuery)
.Fetch(h => h.Vehicle).Eager
.Select(h => new PairDTO { Vehicle = h.Vehicle, NewestHistoryEntry = h })
.Future<PairDTO>();
var vehiclesWithoutEntry = session.QueryOver<Vehicle>()
.Where(v => !v.DriverHistory.Any())
.Select(v => new PairDTO{ Vehicle = v })
.Future<PairDTO>();
return vehiclesWithoutEntry.Concat(vehiclesWithEntry); // .ToList() if immediate executing is required
更新:我无法重现异常,但是您可以尝试
Update: i can not reproduce the exception but you could try this
VehicleDriverHistory historyAlias = null;
var maxDateQuery = QueryOver.Of<VehicleDriverHistory>()
.Where(h => h.Vehicle == historyAlias.Vehicle)
.Select(Projections.Max<VehicleDriverHistory>(h => h.Date));
var vehiclesWithEntry = session.QueryOver(() => historyAlias)
.WithSubquery.WhereProperty(h => h.Date).Eq(maxDateQuery)
.Fetch(h => h.Vehicle).Eager
.Future();
var vehiclesWithoutEntry = session.QueryOver<Vehicle>()
.Where(v => !v.DriverHistory.Any())
.Select(v => new PairDTO{ Vehicle = v })
.Future<PairDTO>();
return vehiclesWithoutEntry.Select(h => new PairDTO { Vehicle = h.Vehicle, NewestHistoryEntry = h }).Concat(vehiclesWithEntry);
这篇关于nhibernate:选择父母和最新的孩子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!