NHibernate Projections Duplicates To List

Question

I have a problem with nHibernate projections. I don't even know if what I'm attempting can be done but if any nHibernate experts can help me I'd be grateful.

I'm using nHibernate criteria to query a database and I'm returning the result projected to a different (slimmed down) object.

I get a list of returns like

Id CompanyId Description
1 1 Desc1
1 2 Desc1
1 3 Desc1
2 1 Desc2
2 3 Desc2
3 1 Desc3
3 2 Desc3
3 3 Desc3

when I use this object

int Id
int CompanyId
string Description

What I'm looking for is to get something more like Id CompanyId Description
1 [1, 2, 3] Description
2 [1, 3] Description
3 [1, 2, 3] Description

From an object like this

int id
List<int> companyId
string description

The current code I have is similar to

 result = session.CreateCriteria<Object>()
           .Add(Restrictions.Eq("SiteId", 616))
           .SetProjection(Projections.Distinct(Projections.ProjectionList()
           .Add(Projections.Property("Id"), "Id")
           .Add(Projections.Property("CompanyId"), "CompanyId")
           .Add(Projections.Property("Description"), "Description")
           .SetResultTransformer(Transformers.AliasToBean<ObjectReduced>()).List<ObjectReduced>();

So I'd like to know if there is anything here I can do to achieve this, or even if this is completely the wrong approach and there is something better.

Thanks, Tim

Answer 1

You can't do that directly in NHibernate. The best way to achieve this is to get the result set as you are currently doing and then populate the reduced object with the CompanyIds. Something like:

var temp = session.CreateCriteria<Object>()
           .Add(Restrictions.Eq("SiteId", 616))
           .SetProjection(Projections.Distinct(Projections.ProjectionList()
           .Add(Projections.Property("Id"), "Id")
           .Add(Projections.Property("CompanyId"), "CompanyId")
           .Add(Projections.Property("Description"), "Description")
           .SetResultTransformer(Transformers.AliasToBean<ObjectReduced>())
           .List<ObjectReduced>();
var groups = temp.GroupBy(x => x.Id);
var result = new List<ObjectReduced>(groups.Count());
foreach (var member in groups)
{
    var first = member.First();
    var companyIds = member.Select(x => x.CompanyId);
    foreach (var companyId in companyIds)
    {
        first.CompanyIds.Add(companyId);
    }
    result.Add(first);
}
return result;

There's probably a LINQier way to do this but this is how I solved a similar problem.