How can I provide values for non-grouped columns in NHibernate?

Question

I have a criteria query:

Session.CreateCriteria<Sell043Report>()
  .SetProjection(.ProjectionList()
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.location))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.agent))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.cusip))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.SettlementDate))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.salePrice))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.foreignFx))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.batchNumber))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.origSaleDate))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.planName))
    .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.dateTimeAdded))
    .Add(LambdaProjection.Sum<Sell043Report>(r => r.shares))
    .Add(LambdaProjection.Sum<Sell043Report>(r => r.netMoney))
    .Add(LambdaProjection.Sum<Sell043Report>(r => r.grossMoney))
    .Add(LambdaProjection.Sum<Sell043Report>(r => r.taxWithheld))
    .Add(LambdaProjection.Sum<Sell043Report>(r => r.fees)))
  .List<Sell043Report>();

that generates the following SQL:

SELECT 
  this_.location as y0_, 
  this_.agent as y1_, 
  this_.cusip as y2_, 
  this_.SettlementDate as y3_, 
  this_.salePrice as y4_, 
  this_.foreignFx as y5_, 
  this_.batchNumber as y6_, 
  this_.origSaleDate as y7_, 
  this_.planName as y8_, 
  this_.dateTimeAdded as y9_, 
  sum(this_.shares) as y10_, 
  sum(this_.netMoney) as y11_, 
  sum(this_.grossMoney) as y12_, 
  sum(this_.taxWithheld) as y13_,
  sum(this_.fees) as y14_ 
FROM 
  MIS_IPS_Sell043Report this_ 
GROUP BY
  this_.location,
  this_.agent,
  this_.cusip,
  this_.SettlementDate,
  this_.salePrice,
  this_.foreignFx,
  this_.batchNumber,
  this_.origSaleDate,
  this_.planName,
  this_.dateTimeAdded

however the Sell043Report table has additional columns than those listed in the SELECT statement so I'm receiving this error when attempting to get a list of Sell043Reports:

System.ArgumentException: The value "System.Object[]" is not of type "xyz.Sell043Report" and cannot be used in this generic collection.

I suspect the problem is that I'm not selecting all of the columns for a Sell043Report and so it doesn't know how to map the dataset to the object. I'm trying to achieve something like this:

SELECT 
  this_.location as y0_, 
  this_.agent as y1_, 
  this_.cusip as y2_, 
  this_.SettlementDate as y3_, 
  this_.salePrice as y4_, 
  this_.foreignFx as y5_, 
  this_.batchNumber as y6_, 
  this_.origSaleDate as y7_, 
  this_.planName as y8_, 
  this_.dateTimeAdded as y9_, 
  sum(this_.shares) as y10_, 
  sum(this_.netMoney) as y11_, 
  sum(this_.grossMoney) as y12_, 
  sum(this_.taxWithheld) as y13_,
  sum(this_.fees) as y14_,
  '' as Address1,
  '' as Address2 // etc
FROM 
  MIS_IPS_Sell043Report this_ 
GROUP BY
  this_.location,
  this_.agent,
  this_.cusip,
  this_.SettlementDate,
  this_.salePrice,
  this_.foreignFx,
  this_.batchNumber,
  this_.origSaleDate,
  this_.planName,
  this_.dateTimeAdded

How can I do this using NHibernate?

Answer 1

You likely want something like this before the call to .List<>():

SetResultTransformer(Transformers.AliasToBean(typeof(Result)))

Where Result is a new class you use to store the subset of columns you want as a result.

See also this question:

http://stackoverflow.com/questions/2207329/projections-in-nhibernate

The error you are getting is because without the call to SetResultTransformer, it wants to return an object array. You are then (unintentionally) trying to cast that object array to a list of Sell043Report objects, which isn't possible.

Your other option is to remove the call to List<>() and instead deal with the object array.