Adding dynamic SQL query to QueryOver

Question

I have the following Query which works for me:

IList<info> result = 
QueryOver<info>()
.Where(row => row.Timestamp >= fromDate)
.And(row => row.Timestamp <= toDate)
.OrderBy(row => row.Timestamp).Asc
.Skip(startRow).Take(count).List();

I need to extends it by getting from a client an addition SQL query string and adding it to my query as follows:

    IList<info> result = 
        QueryOver<info>()
        .Where(row => row.Timestamp >= fromDate)
        .And(queryString)
        .And(row => row.Timestamp <= toDate)
        .OrderBy(row => row.Timestamp).Asc
        .Skip(startRow).Take(count).List();

string queryString = "AND name='haim' And number=1"

Is it possible to add QueryOver a dynamic query string?

1

Why do you want to do this? – Phill Apr 21 '13 at 10:09

score 2 · Answer 1 · answered Mar 22 '14 at 16:56

The short answer is you can:

string queryString = "name='haim' And number=1";
var query = Session.QueryOver<info>().And(NHibernate.Criterion.Expression.Sql(queryString));

as long as you keep the injected queryStrings short and simple you will be ok. If you want this injected string to also alter the query semantics (like for example necessitate a join) i would advise against it; it's not worth the hassle.

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

I doubt this is possible out of the box. But with some tweaking you can do this

Get the SQL out of your QueryOver<>. Follow the accepted answer to this question
The concatenate this with your sql send by the client
Use NH to run the plain SQL. Refer to the accepted answer of this question on how to do this

edited May 23 '17 at 11:57

Community

1
1

answered Apr 21 '13 at 10:04

Suhas

7,919
5
34
54

score 0 · Answer 3 · answered Mar 21 '14 at 15:58

I would add here that is not a good practice to generate the sql somewhere else and send it to be executed. I know it can be hard to abstract the WHERE scenarios in classes but with NET4 is much faster to do with dynamic classes.

So I say this is bad practice because you are not encapsulating your query in a single layer. You should.

The awful consequence is obviously the lack of a strong relationship between the two parts causing this to be a very fragile solution. If one changes, you need to be aware that the other needs to be updated, and so on. One or two developer teams is ok, but big teams would be hurt by this.

Just my two cents.

Adding dynamic SQL query to QueryOver

3 Answers3