NHibernate 中表之间的复杂关系

Question

我正在为旧版 Oracle 数据库编写 Fluent NHibernate 映射。挑战在于表具有复合主键。如果我有完全的自由，我会重新设计关系并自动生成主键，但其他应用程序必须写入同一个数据库并从中读取，所以我不能这样做。

以下是我将重点关注的两个表格：

示例数据

Trips table:
1, 10:00, 11:00 ...
1, 12:00, 15:00 ...
1, 16:00, 19:00 ...
2, 12:00, 13:00 ...
3, 9:00, 18:00 ...

Faults table:
1, 13:00 ...
1, 23:00 ...
2, 12:30 ...

在本例中，车辆 1 进行了 3 次行程，并且两个错误。第一次故障发生在第二次行程时，第二次故障发生在车辆休息时。 2号车有一次行程，期间出现故障。

约束

同一车辆的行程不会重叠。因此，这些表具有可选的一对多关系，因为每个故障要么在行程期间发生，要么不发生。如果我想在 SQL 中加入它们，我会写：

select ... 
from Faults left outer join Trips
  on Faults.VehicleId = Trips.VehicleId
  and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime

然后我会得到一个数据集，其中每个错误只出现一次（正如我所说的一对多）。

请注意，没有车辆表，我也不需要。但我确实创建了一个包含两个表中所有 VehicleId 的视图，因此我可以将其用作联结表。

我到底在寻找什么？

这些表很大，因为它们涵盖了多年的数据，每次我只需要获取几个小时的范围。

所以我需要一个映射和一个标准来运行下面的 SQL：

select ... 
from Faults left outer join Trips
  on Faults.VehicleId = Trips.VehicleId
  and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime
where Faults.FaultTime between :p0 and :p1

你有什么想法如何实现它吗？

注释 1： 目前应用程序不应写入数据库，因此持久性不是必须的，尽管如果映射支持持久性，则可能在将来的某个时候有所帮助。

注2：我知道这是一个艰难的问题，所以如果您给我一个很好的答案，您将得到适当的奖励:)

感谢您阅读这个长问题，现在我只希望得到最好的结果： ）

Answer 1

您的示例 sql 在语法上与此相同

select ... 
from Faults left join Trips
  on Faults.VehicleId = Trips.VehicleId
where Faults.VehicleId is null or (Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime)

，您可以创建一个常规映射，例如（流畅），

HasMany< Trip >( fault => fault.Trips )
    .KeyColumn( "VehicleId" )
    .Table( "Trips" )
    .LazyLoad( )
    .Cascade.Delete( )
    .AsSet()

然后使用您熟悉的每种查询形式，无论是 hql、icriteria、icriteriaover 还是 linq 来执行您的标准查询带有如上所述的 where 子句。

在 linq 中，这将是：

IList<Trip> results = 
( 
    fault in Session.Query< Entities.Faults > 
    join trip in Session.Query< Entities.Trips > on fault.VehicleId equals trip.VehicleId into trip
    where
    fault.FaultTime > startTime && fault.FaultTime < endTime &&
    // Here is the rest of the join criteria expressed as a where criteria
    (
        trip == null
            || 
        (
            fault.FaultTime > trip.TripStartTime && fault.FaultTime < trip.TripEndTime
        ) 
    )
    select fault
).ToList();

如果需要，我可以在 ICriteria 或 IQueryOver 中给您一个示例。

当然，这只有效，因为您提供的示例语句可以在获得结果的同时重写为 where 子句。如果您现实世界中所需的 sql 更复杂，您需要考虑是否可以在归档相同结果的同时重写所需的 sql。

Answer 2

我对 NH 很陌生，只知道 NH 基础知识，所以当我遇到这样的情况时，我编写了一个存储过程，然后 通过 NH 调用它。最终我会找到一个全 NH 解决方案，然后我将重构代码并消除存储过程的必要性。

另一种可能有效的方法是编写 HQL 你需要。

Answer 3

我会提出一个建议，如果您使用NHibernate 3，请尝试Linq to NH。使用 Linq，您可以为一次性执行指定手动/任意关系，或者如果您认为它将被重复使用，则使用管道（或者如果您想做左/右连接，则需要指定它，如果是 linq）发布者连接（isser join），您根本不需要指定连接，它都是从映射中推断出来的），并且是业务逻辑而不是持久性逻辑。

作为一个简单的例子，它会是这样的：

var result = ( 
fault in Session.Query< Entities.Faults > 
join trip in Session.Query< Entities.Trips > on fault.VehicleId equals trip.VehicleId into trip
where 
fault.FaultTime > startTime && fault.FaultTime < endTime &&
fault.FaultTime > trip.TripStartTime && fault.FaultTime < trip.TripEndTime
select fault
).ToList();

我手写了这个，所以它可能不完美，但足够接近。这应该完全满足您的需要，并允许您根据需要更改它，而无需更改映射。

Answer 4

如果您已经知道希望数据库执行什么查询，为什么不直接使用您自己的自定义 DAO 类来执行查询呢？如果 NHibernate 抽象只是妨碍的话，为什么还要费心去处理它呢？

Answer 5

当前建议

鉴于评论中的附加信息，我现在建议尝试以下类映射，而不是使用此答案中进一步提到的任何自定义 SQL 解决方案：

<class name="Fault" table="Faults">
  <composite-id>
    <key-property name="VehicleId" />
    <key-property name="FaultTime" />
    <key-property name="FaultType" />
    <generator class="assigned" />
  </id> 
  <many-to-one name="Trip" class="Trip">
    <!-- Composite Key of Trip is calculated on the fly -->
    <formula>VehicleId</formula>
    <formula>
      ( SELECT  TripStartTime 
        FROM    Trips t 
        WHERE   VehicleId = t.VehicleId 
        AND     FaultTime BETWEEN t.TripStartTime AND t.TripEndTime
      )
    </formula>
  </many-to-one>
  ...
</class> 

<class name="Trip" table="Trips">
  <composite-id>
    <key-property name="VehicleId" />
    <key-property name="TripStartTime" />
  </composite-id> 
  ...
</class>

使用此映射，您可以根据需要加载和查询故障实体。

过时的建议

我最初在这里考虑了一个（命名的）自定义 SQL 查询。您可以在映射文件中输入以下查询来加载给定车辆的故障对象：

<sql-query name="LoadFaultsAndTrips" xml:space="preserve">
  <return class="Fault" alias="f"/>
  <return-join alias="t" property="f.Trip"/>
  SELECT  {f.*}
      ,   {t.*}
  FROM    Faults f
  LEFT OUTER JOIN Trips t 
      ON f.VehicleId = t.VehicleId
      AND f.FaultTime BETWEEN t.TripStartTime AND t.TripEndTime
  WHERE f.VehicleId = ?
</sql-query>

如果您需要在没有显式查询的情况下加载车辆对象上的故障集合，您可以尝试 XML 中的以下映射构造：

<class name="Vehicle">
   <id name="VehicleId" type="...">
     <generator class="..." />
   </id>
   ...
   <bag name="Faults" table="Faults" inverse="true">
     <key column="VehicleId" />
     <loader query-ref="VehicleFaultsLoader" />
   </bag>
   ...
</class>

<sql-query name="VehicleFaultsLoader" xml:space="preserve">
  <load-collection role="Vehicle.Faults" alias="f" />
  <return-join alias="t" property="f.Trip"/>
  SELECT  {f.*}
      ,   {t.*}
  FROM    Faults f
  LEFT OUTER JOIN Trips t 
      ON f.VehicleId = t.VehicleId
      AND f.FaultTime BETWEEN t.TripStartTime AND t.TripEndTime
  WHERE f.VehicleId = ?
</sql-query>

这里的关键是为Vehicle 类上的Faults 集合定义一个自定义集合加载器，并定义一个接收Vehicle 主键作为参数的自定义SQL 查询。我自己还没有使用过流畅的 NHibernate，所以恐怕我无法帮助你解决这部分问题。

干杯，
格尔克。