To take advantage of Full text indexing on MariaDB 10, I need to use this new "MATCH AGAINST" syntax in the sql string.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match
I think it would be really cool if, for certain columns only, I could override linq-to-nhibernate to change the sql it generates when I use
.Where(x => FullTextIndexedStringProperty.Contains("Some word")).ToList().
Who can give me some general directions on how to get started?
This will get you a very simple
MATCH ... AGAINST
clause. If you want to get more complex (more arguments, specifying the search modifier), you'll have to make some bigger changes. Hopefully this will get you started though:Create a new dialect and register a simple
MATCH (...) AGAINST (...)
function:Create a static extension method on
string
that you'll use in LINQ statements:Create a new LINQ to HQL generator class that associates the method with the SQL function we registered in the custom dialect:
Create a custom LinqToHqlGeneratorsRegistry:
Use your custom dialect, and Linq to HQL registry either in your cfg.xml file or in code:
Finally, use your extension method in a LINQ-to-NHibernate query:
This will generate SQL that looks like this:
Again, this won't help if you have more complicated requirements. But hopefully this is at least a good starting point.
In case anyone is curious about how to make this support more complex scenarios, here are the problems I think you'd run into:
MATCH
from those toAGAINST
.