translate a mysql query into nhibernate

Question

Good day! Please, can you help me to translate such a mysql query into nhibernate

SELECT sales_id, service_id,dayofyear(dt), max(dt) FROM clients.statistics group by sales_id, service_id,dayofyear(dt);

Answer 1

sales_id is a property of Sales class, service_id is a property of Service class

Answer 2

If you have a working SQL query, you can use that with the Hibernate directly. Hibernate can execute those queries, and wrap the query result as objects.

See this doc about mapping a entity class on SQL query. That is from normal Hibernate (not NHibernate) but something similar should work in your case.

First make a class that holds the result from query (eg Result) and contains references to required entities (Sales and Service)

String sql = "SELECT sales_id, service_id,dayofyear(dt), max(dt) "
    "FROM clients.statistics " +
    "group by sales_id, service_id, dayofyear(dt)";
sess.createSQLQuery(sql).addEntity("result", Result.class)
    .addJoin("result.sales").addJoin("result.services");