Complex criteria for NHibernate query: Linq or ICriteria?

Question

I'm using NH2.1 with FluentNH config and Linq2NH in a basic Repository model. I am attempting to set up a query that will digest various form fields from a winform. The criteria is pretty much a series of textboxes, each with a checkbox stating whether the criteria contains wildcards (only asterisks are supported), so the logic is a series of:

...
&& Field1.IsNullOrBlank() || Field1 == Criteria1 || (Criteria1IsWildCard && Regex.Match(Field1, Criteria1.Replace("*",".*")))
&& Field2.IsNullOrBlank() || Field2 == Criteria2 || (Criteria2IsWildCard && Regex.Match(Field2, Criteria2.Replace("*",".*")))

Now, this would be just great for Linq2Objects, however it's not going to get very far in a Linq2NH query for a number of reasons (custom extension methods, Regex checking, etc).

I created a different search page that has to digest similar data, however that query is executed against a different Repository datasource which requires the query as a string (SalesForce SOQL). Since NHibernate has so many more sophisticated, compiler-checked tools available, I would prefer HQL to be my last option.

Other relevant info:

• Retrieving and caching the entire table's contents in order to filter the results using Linq2Objects is barely feasible (the table is about 15k records), but any option that doesn't have to do this is much preferred.
• The user needs to have as many wildcards as necessary; therefore, it will be infeasible to use the StartsWith()/EndsWith()/Contains() workaround.

So, the question is, how would you set this up to execute against NHibernate?

Answer 1

HQL, ICriteria, Linq-to-NH and any other API are translated to SQL finally. So as MS-SQL Server and others does not support wild characters directly, you can not use wild characters in your criterias.

Answer 2

The answer I arrived at is to use the StartsWith/EndsWith methods to get a LIKE operation, then also replace any asterisks with %. If the criteria begins with an asterisk, use EndsWith, otherwise use StartsWith. This would turn "abc*" into "abc%%", "*abc" into "%%abc", and "*abc*" into "%%abc%" which all evaluate identically to non-duplicated %% wildcarded counterparts in a LIKE clause. The only scenario in which it would not work as specified is if entering criteria that neither begins nor ends with the wildcard; if comparing "a*c" against abc, adc, and abcde, it would evaluate to "a%c%" instead of "a%c", and would return all of these instead of excluding abcde. Close enough for my purposes.