3

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;
}

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>

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.

I tried with criteria api and with query over.

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 does not know Contains:

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

Could someone help me out?

UPDATE:

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>();

Exception like this.

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

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

Produced 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 as Street3_1_, this_.Zip as Zip3_1_, this_.City as City3_1_, this_.Phone as Phone3_1_, this_.Mobile as Mobile3_1_, this_.Comment as Comment3_1_, this_.StaffId as StaffId3_1_, this_.IsActive as IsActive3_1_, agency3_.Id as Id1_0_, agency3_.BusinessId as BusinessId1_0_, agency3_.RegionKey as RegionKey1_0_, agency3_.Shorttext as Shorttext1_0_, agency3_.Longtext as Longtext1_0_, agency3_.CompanyCodeId as CompanyC6_1_0_ FROM tblPaperboy this_ left outer join tblAgency agency3_ on this_.AgencyKey=agency3_.Id inner join tblPaperboyAdditionalPhoneNumbers additiona4_ on this_.Id=additiona4_.PaperboyId WHERE (apn1_.PhoneNumber = @p1)]

Tobias
  • 2,945
  • 5
  • 41
  • 59

2 Answers2

1

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

Based on this Q & A: NHibernate How do I query against an IList property?

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

17.1.4.1. Alias and property references

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));

And that will support filtering IList<string>

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • Thanks a lot. But I get an SQL-Error, because the join alias (... inner join tblPaperboyAdditionalPhoneNumbers additiona4_ on ...) does not match the used property prefix (additional1_.PhoneNumber). Is it a bug in NHibernate? In the sample some many-to-ones are left out in the mapping. – Tobias Jan 15 '15 at 11:49
  • That is weird, because if you used alias `criteria.CreateAlias("_additionalPhoneNumbers", "AdditionalPhoneNumbers");` it should now be working with restriction `(Restrictions.Eq("AdditionalPhoneNumbers.elements", number)`. I did test that locally. Could you show me your latest code? and add the exception full stack trace? I am sure we will make it. – Radim Köhler Jan 15 '15 at 12:08
  • Added current code to the post. As I said, the original Paperboy-class has another property mapped as many-to-one. – Tobias Jan 15 '15 at 12:14
  • will check give me few mintues – Radim Köhler Jan 15 '15 at 12:17
  • I am using NHibernate 4.0.2 and I followed your model and that is my query by NHibernate `exec sp_executesql N'SELECT this_.Id as Paperboy1_39_1_ FROM tblPaperboy this_ inner join tblPaperboyAdditionalPhoneNumbers apn1_ on this_.PaperboyId=apn1_.PaperboyId WHERE (apn1_.PhoneNumber = @p0) ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST @p1 ROWS ONLY',N'@p0 nvarchar(4000),@p1 int',@p0=N'abc',@p1=1` is this working wheny ou try to execute that - **because for me it is working** – Radim Köhler Jan 15 '15 at 12:32
  • I am (shockingly) using NHibernate 3.0. I will update to 3.2 and check if it still doesnt work. By the way, I added the produced sql to the post. Exception is still the same. – Tobias Jan 15 '15 at 13:44
  • We do have a progress ;) and soon we will have solution ;) BTW, important note - I migrated from 3 to 4.0 without any issues. Maybe - you should do the same. Really – Radim Köhler Jan 15 '15 at 13:47
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68882/discussion-between-tobias-and-radim-kohler). – Tobias Jan 15 '15 at 13:51
1

Seems like NHibernate < 4 has a bug with creating the join. With the help of Radim Köhler and firo (see other Post) I ended up using HQL:

select
   paperboy
from
   Paperboy as paperboy 
left join 
   paperboy._additionalPhoneNumbers number
where
   paperboy._mobile = :nr or
   paperboy._phone = :nr or
   number = :nr

It is also possible to avoid the join by using :nr in elements(paperboy._additionalPhoneNumbers) but than I could not replace some characters in the additional number.

Community
  • 1
  • 1
Tobias
  • 2,945
  • 5
  • 41
  • 59