CODEWITHSUNDEEP
c#.netsqllinqnhibernate
davymartu
davymartu

Reputation: 1443

Using a native SQL Server function in Nhibernate?

I'm building a WPF application that queries a database through Nhibernate.

I have a SQL Server database with a table xuser where there is a flag (xflag) that stores the user's permissions.

Now I have this SQL query to extract superuser or administrators:

select * 
from xuser 
where (CONVERT(int,xflag) & 2 > 0 ) or (CONVERT(int,xflag) & 1 > 0 )

where 2 = flag_administrators and 1 = flag_superusers

Without the SQL function CONVERT, this query doesn't work with error:

The data types numeric and int are incompatible in the '&' operator.

Now in my WPF c# nhibnernate app, I query with:

var xadmins= session.Query<Xuser>()
                    .Where(p => (p.Xflag & (int)Flag.FLAG_SUPERUSER)>0).ToList<string>();

This query doesn't work with the same SQL error.

I want to use T-SQL function in this Linq2Nhibernate query. How can I do that?

The big problem is that this application have to works with MsSql2008 or Oracle dialect. If I use Convert function here, when I'll change db to Oracle, this function go to error. What is the best pratices?

Upvotes: 0

Views: 1364

Answers (1)

mickfold
mickfold

Reputation: 2003

You can specify the mapping for xflag as a formula so it is converted to an int, i.e.

<property name="Xflag" formula="CONVERT(int,xflag)"/>

Or for Fluent NHibernate

Map(x => x.Xflag).Formula("CONVERT(int,xflag)");

You can then use your expression to get all super users without the cast:

var xadmins= session.Query<Xuser>()
                .Where(p => (p.Xflag & Flag.FLAG_SUPERUSER)>0).ToList<string>();

Of course this has the disadvantage of being SQL Server specific. For this to work for Oracle you would need to use the mapping:

<property name="Xflag" formula="to_number(xflag)"/>

To get around this issue to could create a User Defined Function in both DBs that would perform the conversion so the mapping would then become:

<property name="Xflag" formula="xflag_conversion(xflag)"/>

With the Oracle UDF being something like the following.

CREATE FUNCTION xflag_conversion(xflag IN CHAR) 
  RETURN NUMBER 
   BEGIN  
  RETURN(to_number(xflag)); 
END;

The SQL Server UDF would be:

CREATE FUNCTION xflag_conversion (@xflag CHAR)
  RETURNS INT
  AS
  BEGIN
    RETURN CONVERT(int,xflag)
  END

No guarantees about the accuracy of the UDFs though, as I have not tested them.

Finally here is an related question about querying flag information which may be of interest to you: How to query flags stored as enum in NHibernate.

Upvotes: 2

Related Questions