searching using fluent nHibernate using LINQ where property may reside in numerious locations in derived classes

Question

I have a class structure as follows (pseudo code):

CompanyName (id, name) // Other information about the company  

IUsefulSearchField // A field that can appear at any depth in the below
IUsefulSearchField2 // A field that can appear at any depth in the below

BaseClass (id) // Some other values needed in class & This class will be inherited by everything below

Class1: BaseClass (IUsefulSearchField, CompanyName) // Extra values not in the search

Class2: BaseClass (IUsefulSearchField, IList<CompanyName>, IUsefulSearchField2) // Extra values not in the search

Class3: BaseClass (CompanyName, IUsefulSearchField2)

Class4: BaseClass (IList<IUsefulSearchField>)

The above is repeated with multiple Search fields scattered about (they can't be contained in the base class as there may be one or many in the subclass)

I need to search these common fields and company names, I also need to limit by 1 or many derived types e.g. (Pseudo code)

SELECT Object.ID, Object.Type, Object.CompanyName, Object.UsefulSearchField1,  
null AS Object.UsefuleSearchField2 
FROM Class1 UNION ALL  (SELECT Object.ID, Object.Type, Object.CompanyName, 
Object.UsefulSearchField1,  Object.UsefuleSearchField2 
FROM Class2 Inner Join IList<RelationshipToGet Companies> ) etc…. 

WHERE Object.Type in ('Class1', 'Class2', 'Class4') AND CompanyName Like 'Peps%' 
AND (UsefulSearchField1 = 'Bond' OR UsefulSearchField1 IS NULL) AND 
(UsefulSearchField2 > 1000 OR UsefulSearchField2 IS NULL)

The tables will contain +10m rows each so there is a reasonable amount of data to get through

My question is: Should I create a denormalised indexed view and a correlated search object which I query, returning the object type and ID so that I can rebuild the object when the user clicks edit on the search results -- OR -- Should I put an interface against each object which creates the individual parts of the LINQ query - we are currently doing this and it is proving to be very slow as it appear to be possible to optimise so that only the terms that are present are search (i.e. if only Class1 is selected the other classes do not need to be queried)

Many thanks for taking the time to read will append if more info needed

Answer 1

It depends on your willingness to maintain search objects. Performance wise the indexed view is the best option and any DBA would recommend that solution. However Lucene.net was ported from java to meet at least some of your requirements. There are a few blog series about lucene and tekpub has a whole episode about searching.

Personally I have not used NHibernate Search for anything advanced so I am not sure it will meet your requirements completely. It could be worth having a look at though. If you want my recommendation then the indexed view. It's darn quick to get up and running!

EDIT: I would chose either the brand new QueryOver api or Criteria. You will most certainly run into some limitations if you are using the "NHibernate Contrib" Linq provider. It's just 80% compatible but you can do amazing things with Criteria and QueryOver. If you read ayendes post you can easily convert that to QueryOver for some compile time checking instead of the magic strings if that bothers you.

Answer 2

Be very wary of the nHibernate LINQ provider v1 - limited Join support, and a myriad of bugs - many LINQ operations are supported. We're waiting to move to nHIbernate v3 which has a re-written LINQ that uses Expression Trees. Just a warning mate.