Kyle Kyle - 21 days ago 9
C# Question

How do I sum a column in a child table of a projected linq query?

I'm still pretty green on linq when it comes to projection and grouping. How can I get the results of the following SQL query using linq? Or is there a better approach to retrieving this query besides linq? I'm using entity framework and I know I can just hard code the sql but would prefer not to do that if there is a better way.

SELECT j.JobId, j.Name, j.OrderId, j.ShipDate,
st.ShipTypeId, sum(p.DeliveryCnt * p.Quantity) AS 'DeliveryCnt'
FROM Jobs j
JOIN ShipTo st ON j.JobId = st.JobId
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN DesignSetAreas dsa ON ds.DesignSetId = dsa.DesignSetId
JOIN Products p ON dsa.DesignSetAreaId = p.DesignSetAreaId
WHERE j.ShipDate >= '11/13/2016' AND j.ShipDate <= '11/26/2016'
GROUP BY j.JobId, j.Name, j.OrderId, j.ShipDate, st.ShipTypeId


The below code is what I have so far, but I'm not sure where or how I would do the grouping, or if this is even possible.

var shipList = from j in db.Jobs
join st in db.ShipTo on j.JobId equals st.JobId
join ds in db.DesignSets on j.JobId equals ds.JobId
join p in db.Products on ds.DesignSetId equals p.DesignSetId
where j.ShipDate >= startDate && j.ShipDate <= endDate
select new ShipScheduleViewModel
{
JobId = j.JobId,
Name = j.OrderId + " " + j.Name,
ShipDay = ((DateTime)j.ShipDate).Day,
ShipVia = st.ShipTypeId,
Count = p.DeliveryCnt * p.Quantity
};

Answer

Basically the group by syntax requires you to say what you want in the grouping (in this case the p Products are what you are aggregating in the select) and the values you want to group by (in this case the j Job and st.ShipTypeId) and finally you assign the grouping into a variable you can use in your select.

var shipList = from j in db.Jobs
               join st in db.ShipTo on j.JobId equals st.JobId
               join ds in db.DesignSets on j.JobId equals ds.JobId
               join p in db.Products on ds.DesignSetId equals p.DesignSetId
               where j.ShipDate >= startDate && j.ShipDate <= endDate
               group p by new { j, st.ShipTypeId } into grp
               select new ShipScheduleViewModel
               {
                   JobId = grp.Key.j.JobId,
                   Name = grp.Key.j.OrderId + " " + grp.Key.j.Name,
                   ShipDay = ((DateTime)grp.Key.j.ShipDate).Day,
                   ShipVia = grp.Key.ShipTypeId,
                   Count = grp.Sum(p => p.DeliveryCnt * p.Quantity)
               };