Trouble formulating inner joins using NHibernate Linq query

Question

Using NHibernate 3.3.1.400, I'm having problems expressing what is a simple SQL statment using NHibernate's Linq provider.

My domain model looks like this:

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 is supposed to return a list of matching Item elements:

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

The joins connecting Site -> Filer -> Filing -> Item are working great through my mappings, but the problems start when I try to join the NameLinkToFiler or NameLinkToItem classes based on user input.

If the user wants to filter the Query results with a filer 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 my list of returned items is reduced.

Note: The 'Contains' keyword above is a full-text index search I'm using as described here. It's working fine, and let's just assume the filerNameQuery is an IQueryable<NameLinkToFiler>.

It's pretty easy to do this in straight 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 don't want to lose the compile-time checks for this sort of query.

Thanks.

Sear

Answer 1

Apparently, the answer is to not use lambda syntax.

This works fine:

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;

Sear

@RadimKöhler - Hey, I'm a sharing kind of guy... ;) – Sear

Seriously, I do enjoy what you do. It is inspiring... I was thinking about more intensive full-text usage as well. nice ;) – Cuttle