NHibernate:使用条件对值列表进行查询过滤 [英] NHibernate: Query filtering on a list of values using criteria

问题描述

我正在尝试使用条件API按值列表进行过滤.我怀疑这是不可能的，我只是在这里要确保.

class Entity
{
  int id { get; set; }
  IList<Guid> Guids { get; set; }
}

映射:

<class name="Entity">
  <id ...></id>
  <bag name="Guids" table="Entity_Guids">
    <key column="Entity_FK"/>
    <element column="Guid"/>
  </bag>
</class>

假设我有一个Guid列表(实际上，这是另一个子查询).我要过滤所有在Guid列表中至少有一个guid的实体.

Sql看起来像这样:

SELECT * 
FROM Entity e 
  inner join Entity_Guids eg 
    on  e.id = eg.Entity_FK
WHERE 
  eg.Guid in (subquery)

使用Criteria API，这似乎是不可能的.

ICriteria query = session
  .CreateCriteria(typeof(Entity), "e")
  .Add(Subqueries.In("e.Guids", subquery))

引发异常.

解决方案

您的查询将不起作用，因为传递给子查询的e.Guids属性不是单个值.为此，您实际上是在尝试执行交集并检查该交集是否为空，但不幸的是，条件api中不存在该交集，尽管您可能可以使用Linq进行此操作.

如果您的Guid是具有适当属性的实体(Value属性包含实际的Guid)，并且存在双向关系，您可能仍然可以这样做:

var subquery2 = DetachedCriteria.For<GuidEntity>()
  .Add(Subqueries.In("Value", subquery))
  .SetProjection("Entity_FK");

ICriteria query = session.CreateCriteria(typeof (Entity))
  .Add(Subqueries.In("Id", subquery2));

I'm trying to filter by a list of values using the criteria API. I suspect that this is not possible, I'm just asking here to be sure.

class Entity
{
  int id { get; set; }
  IList<Guid> Guids { get; set; }
}

The mapping:

<class name="Entity">
  <id ...></id>
  <bag name="Guids" table="Entity_Guids">
    <key column="Entity_FK"/>
    <element column="Guid"/>
  </bag>
</class>

Assumed I have a list of Guids (actually these is another subquery). I want to filter all Entities where at least one guid is in the list of Guids.

Sql would look like this:

SELECT * 
FROM Entity e 
  inner join Entity_Guids eg 
    on  e.id = eg.Entity_FK
WHERE 
  eg.Guid in (subquery)

With Criteria API, this seems to be impossible.

ICriteria query = session
  .CreateCriteria(typeof(Entity), "e")
  .Add(Subqueries.In("e.Guids", subquery))

Throws an exception.

解决方案

Your query will not work because the e.Guids property passed to the subquery is not a single value. To do it this way you are actually trying to perform an intersection and check that that intersection is not empty, which unfortunately does not exist in the criteria api, although you could probably do this with Linq.

You could probably still do this if your Guids were entities with the appropriate properties (the Value property contains the actual Guid) and there was a bidirectional relationship:

var subquery2 = DetachedCriteria.For<GuidEntity>()
  .Add(Subqueries.In("Value", subquery))
  .SetProjection("Entity_FK");

ICriteria query = session.CreateCriteria(typeof (Entity))
  .Add(Subqueries.In("Id", subquery2));

这篇关于NHibernate:使用条件对值列表进行查询过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！