NHibernate: Where clause containing child collection as subquery building improper T-SQL statements

Question

I am using NHibernate 3.x, along with Fluent NHibernate, and have not had any issues constructing database queries until now.

To simplify my objects for the purposes of this post, I've included a subset of my object and mapping structures below:

IssueItem POCO entity class:

public class IssueItem : DomainEntity, IKeyed<Guid> {
    public virtual Guid ID { get; set; }
    public virtual string Subject { get; set; }
    public virtual string Description { get; set; }
    public virtual IList<IssueLocation> Locations { get; set; }
}

Location POCO entity class:

public class Location : DomainEntity, IKeyed<Guid> {
    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; }
}

IssueItem Fluent NHibernate map:

public class IssueItemMap : DomainEntityMapping<IssueItem> {
    public IssueItemMap()
    {
        Table("IssueItem");
        LazyLoad();
        Map(x => x.ID).Column("ID");
        Map(x => x.Subject).Column("Subject");
        Map(x => x.Description).Column("Description");
        HasMany(x => x.Locations).KeyColumn("IssueItemID").LazyLoad().ReadOnly().Inverse();
    }
}

Location Fluent NHibernate map:

public class LocationMap : DomainEntityMapping<Location> {
    public LocationMap()
    {
        Table("Location");
        LazyLoad();
        Map(x => x.ID).Column("ID");
        Map(x => x.City).Column("City");
        Map(x => x.State).Column("State");
        Map(x => x.Zip).Column("Zip");
        Map(x => x.Organization).Column("Organization");
        References(x => x.IssueItem).ForeignKey("IssueItemID").LazyLoad().ReadOnly();
    }
}

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.

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.

That said, here's my LINQ query I'm having issues with from within one of my service classes in my domain layer:

var issues = _issueRepo.All();
if (!string.IsNullOrWhiteSpace(searchWords)) {
    issues = issues.Where(i => i.Subject.Contains(searchWords)
        || i.Description.Contains(searchWords)
        || i.Locations.Where(l => l.Organization.Contains(searchWords)
            || l.City.Contains(searchWords))
            .Select(x => x.IssueItemID).Contains(i.ID)
        );
}

Now, the IssueItems are queried just fine. However, the one-to-many table (Locations) is not properly queried. This is what I mean...

The generated T-SQL statement is perfect except for the very end of it. Example:

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)

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.

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

Any ideas?

Thanks! Joel

Ufuk Hacıoğulları Ufuk Hacıoğulları · Accepted Answer · 2011-09-15T20:01:41

Add Any() when you query locations. It will come true if any location property contains what you are looking for. You are trying to select in where clause, then trying to get IssueID from there. I think you will see this query is clearer.

var issues = _issueRepo.All();
if (!string.IsNullOrWhiteSpace(searchWords)) 
{
    issues = issues.Where(i => i.Subject.Contains(searchWords)
                            || i.Description.Contains(searchWords)
                            || i.Locations.Any(l => l.Organization.Contains(searchWords))
                            || i.Locations.Any(l => l.City.Contains(searchWords)) )
}

NHibernate: Where clause containing child collection as subquery building improper T-SQL statements

1 Answers