Problems with internal connections using NHibernate Linq query

Question

Using NHibernate 3.3.1.400, I am having trouble expressing what is the simple status of SQL using the NHibernate Linq provider.

My domain model is as follows:

public class Site { public virtual Guid Id {get; set;} public virtual string Name {get; set;} } // a site has many filers public class Filer { public virtual Guid Id {get set;} public virtual Site Site {get; set;} public virtual string Name {get; set;} } // a filer has many filings public class Filing { public virtual Guid Id {get set;} public virtual Filer Filer {get; set;} public virtual DateTime FilingDate {get; set;} } //a filing has many items public class Item { public virtual Guid Id {get set;} public virtual Filing Filing {get; set;} public virtual DateTime Date {get; set;} public virtual decimal Amount {get; set;} } public class SearchName { public virtual Guid Id {get set;} public virtual string Name {get; set;} } // there are potentially many NameLink objects tied to a single search name public abstract class NameLink { public virtual Guid Id {get set;} public virtual SearchName SearchName {get; set;} } public class NameLinkToFiler: NameLink { public virtual Filer Filer {get; set;} } public class NameLinkToItem: NameLink { public virtual Item Item {get; set;} } 

My query should return a list of matching element elements:

 var query = session.Query<Item>() .Where(x => x.Filing.Filer.Site == mySite); 

The connections connecting the site → Filer → Filing → Item work fine with my mappings, but problems arise when I try to join the NameLinkToFiler or NameLinkToItem classes based on user input.

If the user wants to filter the query results with the filter name, I want to join the results of the Item query with the results of this query:

 var filerNameQuery = session.Query<NameLinkToFiler>() .Where(q=>q.SearchName.Contains('some name')); 

I want the results of the NameLinkToFiler.Filer property to join the Item.Filing.Filer property, so the list of returned items is reduced.

Note. The keyword “Contains” is a full-text index search, which I use as described here . It works fine, and suppose filerNameQuery is an IQueryable<NameLinkToFiler> .

This is pretty easy to do in direct SQL:

 select filer.Name, filing.FilingDate, filer.Name, item.Date, item.Amount from Search_Name searchNameForFiler, Search_Name searchNameForItem, Name_Link_Filer nameLinkFiler, Name_Link_Item nameLinkItem, Item item, Filing filing, Filer filer, Site s where contains(searchNameForFiler.Name, :filerName) and searchNameForFiler.Id = nameLinkFiler.SearchNameId and nameLinkFiler.FilerId = filer.Id and contains(searchNameForItem.Name, :itemName) and searchNameForItem.Id = nameLinkItem.SearchNameId and nameLinkItem.ItemId = item.Id and item.FilingId = filing.Id and filing.FilerId = filer.Id and filing.SiteId = :site 

... but I do not want to lose compile-time checks for such a request.

Thanks.

Answer 1

Apparently, the answer is to not use lambda syntax.

This works great:

 query = from t in parentItemQuery join l in Session.Query<NameLinkToFiler>() on t.Filing.Filer.Id equals l.Filer.Id join n in Session.Query<SearchName>() on l.SearchName.Id equals n.Id where sn.Contains(request.FilerName) select t;