title: NHibernate: Where clause containing child collection as subquery building improper T-SQL statements
body: <p>I am using NHibernate 3.x, along with Fluent NHibernate, and have not had any issues constructing database queries until now.</p>
  
  <p>To simplify my objects for the purposes of this post, I've included a subset of my object and mapping structures below:</p>
  
  <p><strong><code>IssueItem</code></strong> POCO entity class:</p>
  
  <pre><code>public class IssueItem : DomainEntity, IKeyed&lt;Guid&gt; {
      public virtual Guid ID { get; set; }
      public virtual string Subject { get; set; }
      public virtual string Description { get; set; }
      public virtual IList&lt;IssueLocation&gt; Locations { get; set; }
  }
  </code></pre>
  
  <p><strong><code>Location</code></strong> POCO entity class:</p>
  
  <pre><code>public class Location : DomainEntity, IKeyed&lt;Guid&gt; {
      public virtual Guid ID { get; set; }
      public virtual string City { get; set; }
      public virtual string State { get; set; }
      public virtual string Zip { get; set; }
      public virtual string Organization { get; set; }
      public virtual IssueItem Issue { get; set; }
  }
  </code></pre>
  
  <p><strong><code>IssueItem</code></strong> Fluent NHibernate map:</p>
  
  <pre><code>public class IssueItemMap : DomainEntityMapping&lt;IssueItem&gt; {
      public IssueItemMap()
      {
          Table("IssueItem");
          LazyLoad();
          Map(x =&gt; x.ID).Column("ID");
          Map(x =&gt; x.Subject).Column("Subject");
          Map(x =&gt; x.Description).Column("Description");
          HasMany(x =&gt; x.Locations).KeyColumn("IssueItemID").LazyLoad().ReadOnly().Inverse();
      }
  }
  </code></pre>
  
  <p><strong><code>Location</code></strong> Fluent NHibernate map:</p>
  
  <pre><code>public class LocationMap : DomainEntityMapping&lt;Location&gt; {
      public LocationMap()
      {
          Table("Location");
          LazyLoad();
          Map(x =&gt; x.ID).Column("ID");
          Map(x =&gt; x.City).Column("City");
          Map(x =&gt; x.State).Column("State");
          Map(x =&gt; x.Zip).Column("Zip");
          Map(x =&gt; x.Organization).Column("Organization");
          References(x =&gt; x.IssueItem).ForeignKey("IssueItemID").LazyLoad().ReadOnly();
      }
  }
  </code></pre>
  
  <p>Now, I'm using a Unit of Work and Service/Repository pattern in my MVC app. Therefore, I have a domain layer of my project that contains my basic POCO entities, as well as validators and services. In my data layer, I've got my NHibernate-related stuff, such as my repositories that my domain layer access from my services. This is where my NHibernate maps live as well.</p>
  
  <p>In order to ensure that no NHibernate-specific logic creeps into my domain layer (in case I want to use a different ORM in the future), I perform my LINQ statements in my services within my domain layer against IQueryable objects returned from the repositories in my data layer. Therefore, when I write my queries, I am using System.Linq and System.Linq.Expressions instead of the NHibernate.Linq class.</p>
  
  <p>That said, here's my LINQ query I'm having issues with from within one of my service classes in my domain layer:</p>
  
  <pre><code>var issues = _issueRepo.All();
  if (!string.IsNullOrWhiteSpace(searchWords)) {
      issues = issues.Where(i =&gt; i.Subject.Contains(searchWords)
          || i.Description.Contains(searchWords)
          || i.Locations.Where(l =&gt; l.Organization.Contains(searchWords)
              || l.City.Contains(searchWords))
              .Select(x =&gt; x.IssueItemID).Contains(i.ID)
          );
  }
  </code></pre>
  
  <p>Now, the <strong><code>IssueItems</code></strong> are queried just fine. However, the one-to-many table (Locations) is not properly queried. This is what I mean...</p>
  
  <p>The generated T-SQL statement is perfect except for the very end of it. Example:</p>
  
  <pre><code>select TOP(100) issueitem0_.ID as ID2_, issueitem0_.Subject as Subject2_, issueitem0_.Description as Description2_ 
  from IssueItem issueitem0_ 
  where issueitem0_.Subject like ('%test%') or issueitem0_.Description like ('%test%')
  or exists (select location1_.IssueItemID from Location location1_ where
  issueitem0_.ID=location1_.IssueItemID and (location1_.Organization like ('%test%') 
  or location1_.City like ('%test%')) and location1_.ID=issueitem0_.ID)
  </code></pre>
  
  <p>See that last bit? It throws in that last "and" statement (and location1_.ID=issueitem0_.ID) that throws a wrench in the whole system. I have tweaked every configuration parameter I could think of with my mapping and have tried many different LINQ statements and I cannot get rid of that last part. I don't know why it adds it.</p>
  
  <p>If I construct the same LINQ statement in LINQPad, it properly generates the T-SQL statement without the last part (and location1_.ID=issueitem0_.ID).</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks!
  Joel</p>