asked on

How to use Linq group by and sum values from 3 tables

I am know how I'd do this in SQL but am new to Linq and can't figure this one out.

In the database, one Request will have multiple RequestSteps, each RequestStep will have 1 procedure which has the TechHrs attribute
I want to group by Request and Sum up the TechHrs for all the steps

If I do this:

var query1 =
	from r in Requests
	from proc in Procedures
	from rs in RequestSteps
	group r by r.RequestNum into rsg
	select new {rsg.First().RequestNum, SUM = rsg.Sum(x => x.TechHrs)};

Open in new window

The x.TechHrs isn't defined

I would think this would work

	select new {rsg.First().RequestNum, SUM = rsg.Sum(x => x.rs.proc.TechHrs) };

Open in new window

But rs isn't defined

Fernando Soto

Hi Lab Guy;

The issue with this part of the code,

SUM = rsg.Sum(x => x.rs.proc.TechHrs)

is that x represents a data record of Request and TechHrs is not a member of Request. In your model there should be a field which is a collection of RequestStep which needs to be inserted between x and TechHrs. The code snippet below should do what you need. You will need to replace the refToRequestStep with the field name in Request table that is the collection of RequestStep not shown in your model screen shot.

var query1 =
          from r in Requests
          group r by r.RequestNum into rsg
          from req in rsg
          select new
          {
            RequestNum = rsg.First().RequestNum,
            SUM = rsg.refToRequestStep.Sum(x => x.TechHrs)
          };

Open in new window

Lab Guy

ASKER

If I change my select to this

select rsg;

Open in new window

I get this (using LinqPad)

So, looks to me like this should work

	select new {
		rsg.First().RequestNum, 
		SUM = rsg.RequestSteps.Procedures.Sum(x => x.TechHrs) 
	};

Open in new window

But get this error:

'System.Linq.IGrouping<string,LINQPad.User.Request>' does not contain a definition for 'RequestSteps' and no extension method 'RequestSteps' accepting a first argument of type 'System.Linq.IGrouping<string,LINQPad.User.Request>' could be found (press F4 to add a using directive or assembly reference)

Fernando Soto

Sorry I did not realize that the TechHrs field was in the Procedures table. Please try it like this to see if it gives the results you need.

var query1 =
          from r in Requests
          group r by r.RequestNum into rsg
          select new
          {
            RequestNum = rsg.First().RequestNum,
            SUM = (from rr in RequestSteps
                   select rr.RequestSteps.Select( rs => rs.Procedures.TechHrs ).Sum( h => h ))
             )
          };

Open in new window

Lab Guy

ASKER

Ok, getting closer, still not recognizing RequestSteps

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial