NHibernate Nested query in FROM clause

Question

I’m having a problem with translating T-SQL query into Nhibernate query – or writing query that will return same results but will be properly written in NH query language (HQL, Criteria, QueryOver, LINQ – I don’t truly care).

I would like to execute similar query from NHibernate:

SELECT
  lic.RegNo,
  lic.ReviewStatus
FROM
  Licence lic
WHERE
  lic.RegNo IN
  (
    SELECT
      grouped.RegNo
    FROM
    (
      SELECT
        g.[Type],
        g.Number,
        MAX(g.Iteration) AS [Iteration],
        MAX(g.RegNo) AS [RegNo]
      FROM
        Licence g
      GROUP BY
        g.[Type],
        g.Number
    ) as grouped
  )
ORDER BY
  lic.RegNo desc

It returns the top most licenses and fetch their review status if exists. RegNo is created from Type, Number and Iteration (pattern: {0}{1:0000}-{2:00}). Each license can have multiply iterations and some of them can contains ReviewStatus, for instance:

W0004-01    NULL
W0001-03    1
P0004-02    3
P0001-02    4

If iteration part is greater than 1 it means that there are multiply iterations (n) for specific licence.

I’ve manage to create NH query by going twice to database:

LicenceInfoViewModel c = null;
var grouped = session.QueryOver<Licence>()
    .SelectList(l => l
    .SelectGroup(x => x.Type)
    .SelectGroup(x => x.Number)
    .SelectMax(x => x.Iteration)
    .SelectMax(x => x.RegNo).WithAlias(() => c.RegNo)
).TransformUsing(Transformers.AliasToBean<LicenceInfoViewModel>())
.Future<LicenceInfoViewModel>();

var proper = session.QueryOver<Licence>()
    .Select(x => x.RegNo, x => x.ReviewStatus)
    .WhereRestrictionOn(x => x.RegNo)
    .IsIn(grouped.Select(x => x.RegNo).ToArray())
    .TransformUsing(Transformers.AliasToBean<LicenceInfoViewModel>())
    .List<LicenceInfoViewModel>();

// ...

public class LicenceInfoViewModel
{
  public string RegNo { get; set; }
  public LicReviewStatus? ReviewStatus { get; set; }
}

public enum LicReviewStatus
{
  InProgress,
  Submitted,
  Validated,
  RequestForInformation,
  DecissionIssued
}

However this solution is not good as it require to download all grouped licences from database, and there could be a thousands of them.

Is there a better way to write this query or is there a way to translate provided above T-SQL query into NHibernate?

adding nhibernate and hibernate tags as IMO if this can be done in hibernate it should be easily translated into nh

Diego Mijelshon · Accepted Answer · 2011-02-22 00:18:07Z

1

I don't think that SQL does what you think it does. Iteration is not used for anything.

In any case, it seems unnecessary. You can change the WHERE to the following, and you'll have both valid SQL and HQL:

lic.RegNo IN
(
  SELECT
    MAX(g.RegNo)
  FROM
    Licence g
  GROUP BY
    g.Type,
    g.Number
)

answered Feb 22, 2011 at 0:18

Diego Mijelshon

52.7k16 gold badges119 silver badges159 bronze badges

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

2 Comments

Gutek Over a year ago

thanks, think this is it - i must be tired :), will try that tomorrow - is there a way to do this with QueryOver or Criteria API so I it will not select Type and Number when doing grouping?

Diego Mijelshon Over a year ago

I don't know... this kind of query is just easier to do with HQL.

Collectives™ on Stack Overflow

NHibernate Nested query in FROM clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related