c# - 如何使用 nHibernate 中的子集之一过滤项目

Question

我有一个如下所示的对象......

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping namespace="test" assembly="test" xmlns="urn:nhibernate-mapping-2.2">

<class name="Person" table="`Person`">
        <id name="ItemId" access="property" column="`ItemId`">
            <generator class="native" />
        </id>
        <property name="Name" column="`A`" />
        <property name="Age" column="`B`" />
        <set name="Email" inverse="true">
            <key column="`Name`" />
            <one-to-many class="EmailClass" />
        </set>
    </class>
</hibernate-mapping>

我想做一个 nHibernate 查询来通过他们的电子邮件地址过滤这些记录

我已经尝试过类似的方法..

var childCriteria = QueryOver.Of<EmailClass>().Where(c => c.EmailAddress.ToString().IsLike("%" + strSearch + "%")).Select(c => c.EmailID);

var query = session.QueryOver<Person>().WithSubquery.WhereExists(childCriteria).Future();

我显然做错了什么。问题是，是否可以搜索数据对象的子集？还是直接搜索子表然后找到它的父表对我来说更好？

Answer 1

我会说，你快到了。

我。首先映射的一些注意事项...

首先，我假设 EmailClass与 Person 有关还有(因为在数据库级别这是双向关系)。

public class EmailClass 
{
    ...
    public virtual Person Person { get; set; } // inverse mapping
}

这是合乎逻辑的，因为这个 DB 关系无论如何都存在。它已经表示为一个人的 <set>名为 Email .并且应该也是必须的，因为我们使用了 inverse="true" ，期望双向映射...

这也让我对映射提出异议。您确定此映射正确吗:

<class name="Person" ...
    // KEY column of this entity is ItemId (seems to be int)
    <id name="ItemId" column="`ItemId`" ...
    ...
    <set name="Email" inverse="true">
        // this mapping says: NHibernate, try to find the value
        // of the ItemId in the Person table
        // in the column Name of the table EmailClass ... ?
        <key column="`Name`" />
        ...
        // would expect
        <key column="ItemId" /> // column inside of EmailClass table

我想说的是，通常我们可以看到，<id>用作根实体 (Person) 键的列名称与 the <key> 相同用于集合映射的列。

所以，我希望 EmailClass表格应包含列，如"ItemId"或 "Person_ID"这将保留对 Person 的引用表。

二。子查询过滤

现在让我们继续查询，假设以上内容为真。

// An Alias, to be used later
Person person = null;

var childCriteria = QueryOver
    .Of<EmailClass>()
    // more QueryOver native style of a LIKE expression
    .WhereRestrictionOn(c => c.EmailAddress).IsLike(strSearch, MatchMode.Anywhere)
    // trick here
    // if we want to use the EXISTS later
    // we need to join outer and inner query here
    // and that's a place for outer query ALIAS
    .Where(c => c.PersonId == person.ItemId)
    .Select(c => c.PersonId); // must select something...

var query = session
    // ALIAS expressing the outer query in action again
    .QueryOver<Person>(() => person)
    .WithSubquery
    .WhereExists(childCriteria)
    .Future();

所以，正如我们所看到的，差不多了...只是有点 QueryOver-ish LIKE 风格，以及基本的 WHERE 子句 Person = EmailAddress.Person

三。映射...建议

请让我也提供一些我期望/建议的映射草稿:

public class Person
{
    public virtual int ItemId { get; set;}
    public virtual IList<EmailClass> Emails { get; set; } // plural Emails
}
public class EmailClass 
{
    ...
    public virtual Person Person { get; set; } // inverse mapping
}

映射:

<class name="Person" ...
    ...
    <set name="Emails"        // plural
      cascade="all-delete-orphan" // usually makes sense to rely on NHibernate cascade
      batch-size="25"         // great feature improving 1 + N issue
      inverse="true"  >       // already used, improves WRITE operations sequence
      <key column="ItemId" /> // ItemId should column in the EmailClass table
        <one-to-many class="EmailClass" />
    </set>
</class>
<class name="EmailClass">
   <id ... 
   <many-to-one name="Person" column="ItemId" /> // the same column as in above <set
   ...