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 some 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>();