CODEWITHSUNDEEP
c#nhibernatecriteria-api
Tobias
Tobias

Reputation: 2985

NHibernate Query with joins and group by

I have 4 assoiciated classes and need to do a query.

The classes are:

 public class Project{

     IList<Process> Processes{get;set;}

 }


 public class Process{

     IList<Association> Associations{get;set;}

 }


 public class Association{

     public IList<MonthCapacity> MonthCapacities{get;set;}
     public FieldOfActivity FieldOfActivity{get;set;}

 }

 public class MonthCapacity{

     public int Hours{get;set;}
     public DateTime MonthDate{get;set;}

 }

 public class FieldOfActivity{

 }

The query should result in a DTO:

 public class ChartDto{

      public Project Project{get;set;}
      public FieldOfActivity FieldOfActivity{get;set;}
      public int Hours{get;set;}

 }

I tried it with QueryOver but didn't get it to work. Could someone help me out?

Thanks in advance Tobi

Upvotes: 2

Views: 999

Answers (1)

Firo
Firo

Reputation: 30813

can you test this:

Process processAlias = null;
Association assocAlias = null;
FieldOfActivity actAlias = null;

var subquery = QueryOver.Of<MonthCapacity>()
    .Where(m => m.Association == assocAlias)
    .Select(Projections.Sum<MonthCapacity>(m => m.Hours));

var results = session.QueryOver<Project>()
    .JoinQueryOver(p => p.Processes, () => processAlias)
    .JoinQueryOver(p => p.Associations, () => assocAlias)
    .JoinAlias(p => p.FieldOfActivity, () => actAlias)
    .Select(p => new ChartDto { Project = p, FieldOfActivity = actAlias, Hours = Projections.Subquery(subquery) })
    .List();

Edit:

var results = session.QueryOver<Project>()
    .JoinQueryOver(p => p.Processes, () => processAlias)
    .JoinQueryOver(p => p.Associations, () => assocAlias)
    .JoinAlias(p => p.FieldOfActivity, () => actAlias)
    .SelectList(list => list
        .Select(p => new ChartDto { Project = p, FieldOfActivity = actAlias, Hours = 0 })
        .Select(Projections.Subquery(subquery))
    )
    .List<object[]>()
    .Select(objects =>
    {
        var chart = (ChartDto)objects[0];
        chart.Hours = (int)objects[1];
        return chart;
    });

Upvotes: 2

Related Questions