Using Join, Group By, and Sum in Entity Framework

c# entity-framework sql

Question

My models look like:

public class ReturnItem
{
    public int returnItemId { get ; set; }
    public int returnRequestId { get; set; }
    public int quantity { get; set; }
    public string item { get; set; }
}

public class ReturnRequest
{
    public int returnRequestId { get; set; }
    public string orderNumber  { get; set; }
    public IEnumerable<ReturnItem> returnItems { get; set; }
}

And I have the following query:

SELECT item, sum(quantity)
FROM ReturnItem
JOIN ReturnRequest
ON ReturnRequest.returnRequestId = ReturnItem.returnRequestId
WHERE ReturnRequest.orderNumber = '1XX'
GROUP BY item

How do I convert the query to Entity Framework and return a List<ReturnItem>? Can I use .Include instead of .Join?

1
1
1/27/2014 5:55:14 PM

Popular Answer

from ri in db.ReturnItems
join rr in db.ReturnRequests
   on ri.returnRequestId equals rr.returnRequestId
where rr.orderNumber == "1XX"
group ri by ri.item into g
select new {
   Item = g.Key,
   Quantity = g.Sum(i => i.quantity)
}

You can't use Include instead of Join because Include translated into Left Outer Join but you need Inner Join here.

But you can use navigation property to perform join implicitly:

db.ReturnRequests
  .Where(rr => rr.orderNumber == "1XX")
  .SelectMany(rr => rr.returnItems)
  .GroupBy(ri => ri.item)
  .Select(g => new {
      Item = g.Key,
      Quantity = g.Sum(ri => ri.quantity)
  });
7
1/27/2014 8:40:11 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow