CODEWITHSUNDEEP
nhibernatequeryoverdynamic-queries
Haimon
Haimon

Reputation: 227

Adding dynamic SQL query to QueryOver

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?

Upvotes: 1

Views: 1831

Answers (3)

Jaguar
Jaguar

Reputation: 5958

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.

Upvotes: 3

Kat Lim Ruiz
Kat Lim Ruiz

Reputation: 2562

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.

Upvotes: 0

Suhas
Suhas

Reputation: 8458

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

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

Upvotes: 1

Related Questions