Can't retrieve Group By entities or by composite keys with Nhibernate's Linq

Question

I am trying to perform a group by Linq query with NH3. Knowing the underling SQL difficulties I know it's not possible but Ideally I would like to do the group by an entity and have it retrieved in it's entirety. Something like:

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 as it tries to retrieve the whole Job entity but group only by its Id.

I have tried grouping by a 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 this I get an Exception when NHibernate tryes to build an expression tree:

An item with the same key has already been added.

Anyone knows if there is any way to accomplish that with NHibernate ?

Thanks, Ilan

Answer 1

I had a similar issue trying to use the .Join extension method multiple times where the expression tree alias was taken directly from the lambda parameter name.

My knowledge of the SQL Like linq syntax is limited, but I would assume it somehow translates to the fluent equivalent where the lambda alias names are automatically assigned.

If thats the case, its possible that translating this to the fluent syntax with explicit unique labmda aliases you could avoid the issue