1

I'm performing some maintenance on some code that uses NHibernate as a person who knows almost nothing about NHibernate...

I have the following query

       var query = string.Format(@"select s.Id, s.Iccid, c.Name as Carrier, aa.StartDate as AssignmentDate, cust.Name as AssignedCustomerName 
                    from assetassignment aa 
                    left join SIM s on aa.AssetId = s.Id 
                    left join Carrier c on s.CarrierId = c.Id 
                    left join customer cust on aa.CustomerId = cust.Id 
                    where aa.enddate is null
                    and aa.CustomerId in ({0}) 
                    and s.dateremoved is null",
                    string.Join(",",idsToInclude));
    if (!string.IsNullOrWhiteSpace(carrier))
    {
        query += " and c.Name = '" + carrier + "'";
    }
   var results = _session.CreateSQLQuery(query)
       .SetResultTransformer(new AliasToBeanResultTransformer(typeof(HomepageSIMTableRow)))
       .List<HomepageSIMTableRow>();
    return results;

This works fine for me (and means I didn't have to grok NHibernate to get something running I could work against but now I need to add paging and it is just feeling smelly.

Any guidance on how to move this into NHibernate land and add paging would be awesome!

Paul D'Ambra
  • 7,629
  • 3
  • 51
  • 96
  • Erm, I may be missing something but this is SQL not HQL... are you passing SQL straight into a named query somehow? – Liath Jun 03 '14 at 08:51
  • 1
    Sorry, have expanded my example. I normally use EF and so to prove this feature and customer feedback I'm just passing sql directly through NHibernate. These datasets are large and my first queryover attempt was 'select n+1'-tastic – Paul D'Ambra Jun 03 '14 at 08:53
  • Coming from EF, take some time with NH - in comparison it's wonderful! :) – Liath Jun 03 '14 at 08:54

2 Answers2

1

I'm not sure if this works with regular SQL, but usually with NHibernate you add a 

var results = _session.CreateSQLQuery(query)
   .SetFirstResult(0)
   .SetSetMaxResults(30)
   .SetResultTransformer(new AliasToBeanResultTransformer(typeof(HomepageSIMTableRow)))
   .List<HomepageSIMTableRow>();

This works for regular Criterias and HQL queries.

You can read this as a reference: How can you do paging with NHibernate?

Community
  • 1
  • 1
jishi
  • 24,126
  • 6
  • 49
  • 75
  • Yep, this is the approach I use. Unfortunately you won't be able to do it with the SQL query. – Liath Jun 03 '14 at 08:59
  • Well, in that case you should just be able to add a limit to the query...? – jishi Jun 03 '14 at 08:59
  • yep - down to the OP which approach. You're suggesting the way I'd prefer myself – Liath Jun 03 '14 at 09:00
  • Brilliant - plugged that in and all my tests pass. Thanks! (now just to ignore that I'm passing the SQL directly :-)) – Paul D'Ambra Jun 03 '14 at 09:22
  • @jishi This is wrong way to do it if you have joins, cause there will be more SQL rows, than distinct root entities, one entity would have multiple sql rows, and the restriction is set on rows count. So you are setting 30 rows limit, which will be materialized in like 3 entities cause of joins. – Alex Burtsev Jun 29 '18 at 14:06
1

The reason this feels "smelly" is because you're writing SQL and passing it straight to the ORM.

NH offers a whole mechanism for paging at an entity level. I have found this to get a little tricky when you're eagerly loading other entities though.

My suggestion would be to either:

  • Write the pagination SQL yourself, this is probably lower risk as it will involve less changes
  • Convert the whole query to use NH ICriterion query or a HQL statement.

Unfortunately it's hard to suggest which one without knowing the risk/situation.

Community
  • 1
  • 1
Liath
  • 9,913
  • 9
  • 51
  • 81