CODEWITHSUNDEEP
c#nhibernateasp.net-mvc-4
Matt
Matt

Reputation: 317

Convert SQL statement to NHibernate Query

I tried to ask this question yesterday, but I don't think my wording was very clear.

Therefore, I've tried to reword the question below.

We have an existing system which we are rewriting in MVC4, and we are using NHibernate 3.2 in our new system.

In the old system, we build up a SQL statement similar to this:

SELECT myField as series, 
    pstatus, 
    Year(acc_date) AS year, 
    Month(acc_date) AS month, 
    COUNT(CAST(reportable AS INT)) AS totalreportable, 
    SUM(CAST(reportable AS INT)) AS riddorreportable, 
    SUM(CAST(lt_acc AS INT)) AS losttime, 
    SUM(CAST(acc_losttime AS INT)) AS totaldayslost, 
    SUM(CAST(nearmiss AS INT)) AS nearmiss 
    FROM incident 
    WHERE 1=1

In the above, we will be replacing "WHERE 1=1" with nHibernate criteria (which we already have in the system to filter data).

myField in the above SQL statement is a field which is selectable by the user e.g. he could choose Department, City etc.

What I need, therefore, is information on how to use nHibernate to achieve the same result as the above SQL statement.

Upvotes: 1

Views: 1936

Answers (2)

Oskar Berggren
Oskar Berggren

Reputation: 5647

Chapter 14, 15, 16, and 17 in the documentation in about the different query APIs: http://nhibernate.info/doc/nh/en/index.html

In addition to those there is also support for LINQ, which nowadays should probably be your preferred query API unless you hit its limitations. If you are building a new system, you should start with NH 3.3.2 to have access to the latest LINQ improvements.

All the query APIs can be made to select (project) different properties based on some user input. I think you may need to be more specific with what you have tried or what problem you experience for a more detailed response.

Upvotes: 0

Randy Burden
Randy Burden

Reputation: 2661

Since you are already using the ICriteria API to construct the WHERE clause, you'll need to use the ICriteria's SetProjection() method in order to build up your dynamic SELECT clause.

This will allow you to use your user's selected field in a dynamic way by using strings.

I was going to write a code example but I felt that someone had already done a very good job of it on this other StackOverflow question: https://stackoverflow.com/a/696020/670028

Official NHibernate documentation on ICriteria Projections: http://nhibernate.info/doc/nh/en/index.html#querycriteria-projection

Upvotes: 3

Related Questions