我有以下实体
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入境根据“日期”。 但我有点挣扎,我没有问题,在SQL这样做,但在NHibernate的失败为止。
我到目前为止有:
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的。
所以,我怎么会选择父母和孩子最新的(或无如果没有存在)
谢谢
UPDATE
随着Firos帮助我结束了以下内容:
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因为它提出了一个例外。