How to use subquery as a join in NHibernate?

Question

I have the following SQL code:

SELECT err.*,tmp.counted FROM sm_database.error err
LEFT JOIN (
    SELECT sme_Hash, COUNT(*) as counted FROM sm_database.error GROUP BY sme_Hash
) tmp
ON tmp.sme_Hash = err.sme_Hash
WHERE sme_Id = 197

Above will get me a extra column counted that should set into my Count property below.

the id 197 will be parameter I will use in a method.

My class name is:

public class Error {

    public virtual int Count { get; set; }

    public virtual DateTime Date { get; set; }

    public virtual int Id { get; set; }

    public virtual string Hash { get; set; } 

    -----------------
    //more property's 

}

I want to "convert" this into NHibernate with Query(or QueryOver)

Anyone know how to do this properly or could point me in the right direction?

EDIT

I got it with the following:

 string query = @"SELECT err.* ,tmp.Count 
                      FROM sm_database.error err 
                      LEFT JOIN ( 
                      SELECT sme_Hash as Hash, COUNT(*) as Count FROM sm_database.error GROUP BY sme_Hash
                      ) tmp
                      ON tmp.Hash = err.sme_Hash
                      WHERE sme_Id = :id";

        var result = session.CreateSQLQuery(query)
                   .AddEntity(typeof(Error))

                  .SetParameter("id", id).List<Error>().SingleOrDefault();

It works fine, everthing is pulled except the Count property.

The Count property in my Error class isn't mapped cause we don't have a column in the database because we calculated the count in the query.

Is there a way to get the tmp.Count value in my Error.Count property?

Thanks in advance.

score 3 · Answer 1 · edited May 23 '17 at 12:04

There is no easy answer, no built in solution for this kind of SQL construct.

The options how to go around are:

1) Create special entity and map it to this view: "SELECT sme_Hash, COUNT(*) as counted FROM sm_database.error GROUP BY sme_Hash" It could be either DB view or mapping with subselect (see Mapping native sql to Entity with NHibernate)

2) use native SQL with CreateSQLQuery

Because NHibernate provides querying on top of Entity model. I.e. the FROM is always coming from mapping. No way how to create that as above

How to use subquery as a join in NHibernate?

1 Answers1