NHibernate:选择元素包中的条目 [英] NHibernate: Select item with entry in element bag

问题描述

我有一个具有列表属性的类.

I have a class with property of list.

public class Paperboy{
     private int _id;
     private string _lastname;
     private string _firstname;
     private string _mobile;
     private string _phone;

     private IList<string> _additionalPhoneNumbers;
}

List 被映射为带有键和元素的包.

The List is mapped as bag with key and element.

<class name="Paperboy" table="tblPaperboy" lazy="false">
 <id name="_id" column="Id" access="field" >
  <generator class="assigned"/>
 </id>
 <property name ="_lastname" column="Lastname" access ="field" />
 <property name ="_firstname" column="Firstname" access ="field" />
 <property name ="_phone" column="Phone" access ="field" />
 <property name ="_mobile" column="Mobile" access ="field" />

 <bag name="_additionalPhoneNumbers" access="field" fetch="subselect" lazy="false" table="tblPaperboyAdditionalPhoneNumbers">
  <key column="PaperboyId"/>
  <element column="PhoneNumber" type="string"/>     
 </bag>
</class>

现在我尝试通过电话号码(_phone、_mobile 或 _additionalPhoneNumbers)来选择 paberboys._phone 和 _mobile 没问题，但我被其他号码卡住了.

Now I try to select paberboys by their phone number (_phone, _mobile or in _additionalPhoneNumbers). _phone and _mobile are no problem but I am stuck with the additional numbers.

我尝试了标准 api 和查询.

I tried with criteria api and with query over.

Criteria Api 不知道属性 AdditionalPhoneNumbers

Criteria Api does not know the property AdditionalPhoneNumbers

ICriteria criteria = session.CreateCriteria(typeof(Paperboy));
criteria.CreateAlias("_additionalPhoneNumbers", "AdditionalPhoneNumbers");

Disjunction or = Restrictions.Disjunction();
or.Add(Restrictions.Eq("_phone", number));
or.Add(Restrictions.Eq("_mobile", number));
or.Add(Restrictions.Eq("AdditionalPhoneNumbers", number));

criteria.Add(or);
criteria.SetMaxResults(1);
return criteria.UniqueResult<Paperboy>();

Query Over 不知道包含:

Query Over does not know Contains:

 return query.Where(p => p.Mobile == number || p.Phone == number p.AdditionalPhoneNumbers.Contains(number)).Take(1).SingleOrDefault();

有人可以帮我吗?

更新:

我被指向了元素关键字.实施后，我得到一个 SQL 异常.

I was pointed to the elements-keyword. After implementing I get an SQL-Exception.

 ICriteria criteria = session.CreateCriteria(typeof(Paperboy));
 criteria.CreateAlias("_additionalPhoneNumbers", "APN");

 Disjunction or = Restrictions.Disjunction();
 or.Add(Restrictions.Eq("APN.elements", number));

 criteria.Add(or);
 criteria.SetMaxResults(1);
 return criteria.UniqueResult<Paperboy>();

像这样的异常.

System.Data.SqlClient.SqlException: Der mehrteilige Bezeichner 'apn1_.PhoneNumber' konnte nicht gebunden werden.

问题是在属性的 where 子句中没有使用 join 的别名.apn1_(其中)与additionala4_(加入).

Problem is the alias for join is not used in the where clause for the property. apn1_ (where) vs. additiona4_ (join).

生成的 SQL:

[SELECT TOP (@p0) this_.Id as Id3_1_, this_.BusinessId as BusinessId3_1_, this_.AgencyKey as AgencyKey3_1_, this_.Lastname as Lastname3_1_, this_.Firstname as Firstname3_1_, this_.Title as Title3_1_, this_.Street 为 Street3_1_，this_.Zip 为 Zip3_1_，this_.City 为 City3_1_，this_.Phone 为 Phone3_1_，this_.Mobile 为 Mobile3_1_，this_.Comment 为 Comment3_1_，this_.StaffId 为 StaffId3_1_，this_.IsActive 为 IsActive3_1_Idas Id1_0_，agency3_.BusinessId 作为BusinessId1_0_，agency3_.RegionKey 作为RegionKey1_0_，agency3_.Shorttext 作为Shorttext1_0_，agency3_.Longtext 作为Longtext1_0_，agency3_.CompanyCodeId 作为CompanyC6_1_0_ FROM tblPaperboy this_left external connectortblPaperboyAdditionalPhoneNumbers additionala4_ on this_.Id=additiona4_.PaperboyId WHERE (apn1_.PhoneNumber = @p1)]

推荐答案

几乎与我刚刚对之前的 NHibernate 问题给出的答案相同:QueryOverIList<字符串>财产

Almost the same answer I just gave to previous NHibernate question: QueryOver IList<string> property

基于这个问答A: NHibernate 如何查询 IList 属性?

我试图证明(如文档中提到的)我们可以使用神奇的词".elements":

Where I tried to show that (as mentioned in the documentation) we can use magical word ".elements":

因此查询将触及您案例中的字符串元素

So the query which will touch the string elements in your case

//or.Add(Restrictions.Eq("AdditionalPhoneNumbers", number));
or.Add(Restrictions.Eq("AdditionalPhoneNumbers.elements", number));

这将支持过滤IList

这篇关于NHibernate:选择元素包中的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！