CODEWITHSUNDEEP
oracle-databasenhibernatemappingfluent
oFce
oFce

Reputation: 15

How to use SQL function in reference in NHibernate?

I'm using Fluent NHibernate on Oracle and my problem is I have to apply lower() function on every string in where conditions. I made my own dialect that instead Oracle lower function is being used nls_lower. Database is primarly used by Microsoft Dynamics AX and this function improve performance. In standard query like this everything works fine:

session.QueryOver<User>()
    .Where(x => x.Name.lower() == userName.lower())
    .SingleOrDefualt<User>();

But how can I apply this lower() function in references? I can't find anything suitable for this and I expect It can be done somehow. I would expect something like this in mapping class but I can't find it:

References<Settings>(x => x.Settings)
    .Column("SettingId").lower();

I don't want to convert my string immediately to lowercase but I really need generate query like this:

select * from User where nls_lower(Name) == nls_lower("somename");

Thank you!

Upvotes: 1

Views: 1021

Answers (2)

cremor
cremor

Reputation: 6876

You can define custom SQL to load entities. See here.

Upvotes: 0

LeftyX
LeftyX

Reputation: 35587

nls_lower is not a registered function in the Oracle dialect.
You can have a look in the code base in the Oracle8iDialect class. In this situation you have to register your own extension. Have a look here.

When you've registered your dialect extension you should be able to call it this way:

var filter1 = Restrictions.Eq(
    Projections.SqlFunction("nls_lower", NHibernateUtil.String,
    Projections.Property<User>(x => x.Name)), userName.ToLower());

var user = session.QueryOver<User>();
user.Where(myFilter)
    .SingleOrDefualt<User>();

Upvotes: 1

Related Questions