Convert SQL statement to NHibernate Query

Question

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.

I took a look at your previously asked question ( stackoverflow.com/questions/13457735/… ) to try to get a better sense of what you are trying to achieve but it is still a bit unclear as you mention that the where clause is now using NHibernate Criteria which throws me off. Are you saying that you are using the ICriteria query API to build up the WHERE clause? Additionally, when you state that you are allowing the user to select a particular column, is that column also used in the WHERE clause or only in the SELECT? — Randy Burden
– Randy Burden, Commented Nov 20, 2012 at 22:30
Hi Randy - Yes, we are building a new system which has to emulate the functionality of the existing system. The WHERE clause could contain the selected field - in a sense, it's irrelevant what the WHERE clause is. The new system uses the ICriteria query API to build the WHERE clause. — Matt
– Matt, Commented Nov 21, 2012 at 8:40

Community · Accepted Answer · 2017-05-23 12:23:31Z

3

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

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Nov 21, 2012 at 16:49

Randy Burden

2,6551 gold badge29 silver badges36 bronze badges

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

Comments

Owen Pauling · Accepted Answer · 2016-01-10 19:39:46Z

0

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.

edited Jan 10, 2016 at 19:39

Owen Pauling

12.1k20 gold badges58 silver badges67 bronze badges

answered Nov 20, 2012 at 12:19

Oskar Berggren

5,6561 gold badge21 silver badges37 bronze badges

Collectives™ on Stack Overflow

Convert SQL statement to NHibernate Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related