Cannot get Group By objects or compound keys using Nhibernate Linq

Question

I am trying to execute a group on Linq request with NH3. Knowing the difficulties with not achieving SQL, I know that this is impossible, but ideally I would like to make the group an entity and get it completely. Sort of:

var list = from proposals in Session.Query<Proposal>() group proposals by proposals.Job into jobGrouping select new { Job = jobGrouping.Key, TotalProposals = jobGrouping.Count() }; 

This generates an illegal SQL query when it tries to get the entire Job object, but is grouped only by its identifier.

I tried to group by composite field:

  var list = from proposals in Session.Query<Proposal>() group proposals by new { proposals.Job.Name, proposals.Job.Status} into jobGrouping select new { Job = jobGrouping.Key.Name, Status = jobGrouping.Key.Status, TotalProposals = jobGrouping.Count() }; 

But whenever I try to do this, I get an exception when NHibernate tries to build an expression tree:

An item with the same key has already been added.

Does anyone know if there is a way to accomplish this using NHibernate?

Thanks Ilan

Answer 1

I had a similar problem trying to use the .Join extension method several times when the expression tree alias was taken directly from the lambda parameter name.

My knowledge of SQL syntax like linq is limited, but I would suggest that it somehow translates into a free equivalent, where lambda aliases are automatically assigned.

If so, it is possible that it translates this to free syntax with explicit unique aliases labmda, you could avoid the problem