custom sql functions with fluent nhibernate

How can I use some my SQL function with Fluent NHibernate?

I have some SQL function, for example:

CREATE FUNCTION [dbo].[TestFunction] 
(
    @value1 int,
    @vlaue2 int 
)
RETURNS int
AS
BEGIN
    RETURN @value1 + @value2
END

And I whant use this function in so开发者_C百科me of my criteria queries. Can I do this and how?

There are a few ways to do this the easiest that I found was to provide a custom SQL Dialect that registered my function and then used Session.CreateSQLQueryto execute it.

Here is a sample custom Dialect:

public class CustomMsSql2012Dialect : MsSql2012Dialect
{
    public CustomMsSql2012Dialect ()
    {          
        RegisterFunction("dbo.testfunction", new SQLFunctionTemplate(NHibernateUtil.Int32, "dbo.TestFunction(?1, ?2)"));
    }
}

Note that the function name in the first string passed to RegisterFunction is all lowercase, in my research I came across someone that said this was required but I can't find that article to cite now unfortunately.

How to use the dialect:

MsSqlConfiguration.MsSql2012.ConnectionString("connectionStringHere")
    .Dialect<EkaMsSql2012Dialect>();

How to call the function:

Session.CreateSQLQuery("SELECT dbo.TestFunction( :value1, :value2 )")
     .SetString("value1", 5)
     .SetString("value2", 6)
     .UniqueResult<int>();