title: NHibernate: Select item with entry in element bag
body: <p>I have a class with property of list.</p>
  
  <pre><code>public class Paperboy{
       private int _id;
       private string _lastname;
       private string _firstname;
       private string _mobile;
       private string _phone;
  
       private IList&lt;string&gt; _additionalPhoneNumbers;
  }
  </code></pre>
  
  <p>The List is mapped as bag with key and element.</p>
  
  <pre><code>&lt;class name="Paperboy" table="tblPaperboy" lazy="false"&gt;
   &lt;id name="_id" column="Id" access="field" &gt;
    &lt;generator class="assigned"/&gt;
   &lt;/id&gt;
   &lt;property name ="_lastname" column="Lastname" access ="field" /&gt;
   &lt;property name ="_firstname" column="Firstname" access ="field" /&gt;
   &lt;property name ="_phone" column="Phone" access ="field" /&gt;
   &lt;property name ="_mobile" column="Mobile" access ="field" /&gt;
  
   &lt;bag name="_additionalPhoneNumbers" access="field" fetch="subselect" lazy="false" table="tblPaperboyAdditionalPhoneNumbers"&gt;
    &lt;key column="PaperboyId"/&gt;
    &lt;element column="PhoneNumber" type="string"/&gt;     
   &lt;/bag&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p>I tried with criteria api and with query over. </p>
  
  <p>Criteria Api does not know the property AdditionalPhoneNumbers</p>
  
  <pre><code>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&lt;Paperboy&gt;();
  </code></pre>
  
  <p>Query Over does not know Contains:</p>
  
  <pre><code> return query.Where(p =&gt; p.Mobile == number || p.Phone == number p.AdditionalPhoneNumbers.Contains(number)).Take(1).SingleOrDefault();
  </code></pre>
  
  <p>Could someone help me out?</p>
  
  <hr>
  
  <p>UPDATE:</p>
  
  <p>I was pointed to the elements-keyword. After implementing I get an SQL-Exception.</p>
  
  <pre><code> 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&lt;Paperboy&gt;();
  </code></pre>
  
  <p>Exception like this.</p>
  
  <p><code>System.Data.SqlClient.SqlException: Der mehrteilige Bezeichner 'apn1_.PhoneNumber' konnte nicht gebunden werden.</code></p>
  
  <p>Problem is the alias for join is not used in the where clause for the property.
  apn1_ (where) vs. additiona4_ (join).</p>
  
  <p>Produced SQL:</p>
  
  <p><code>[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)]</code></p>