How to use SQL function in reference in NHibernate?

Question

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!

LeftyX · Accepted Answer · 2011-09-10 08:24:32Z

1

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

answered Sep 10, 2011 at 8:24

LeftyX

35.6k21 gold badges140 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

oFce Over a year ago

I know it's not registered function. Like I said I made subclass of Oracle10gDialect where I registered this function and override the standard lower function. This works for standard query. Question is how can I use sql function (in this case nls_lower) for generated query from references.

LeftyX Over a year ago

@oFce: sorry for my late reply. In my answer I've shown you the way you can use it with QueryOver. Haven't tried it but it should work.

oFce Over a year ago

Thanks for response. This will generate apply nls_lower only for property Name. I want it for all string properties. For example I have Settings entity referenced to entity User. For this 2 SQL query will be generated - for entity User and for entity Settings constrained by ID from User. And I want apply nls_lower in condition for entity Settings.

cremor · Accepted Answer · 2011-09-12 12:03:23Z

0

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

answered Sep 12, 2011 at 12:03

cremor

6,9351 gold badge34 silver badges78 bronze badges

Collectives™ on Stack Overflow

How to use SQL function in reference in NHibernate?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related