CODEWITHSUNDEEP
c#sqlentity-framework
janinaj
janinaj

Reputation: 154

Using Join, Group By, and Sum in Entity Framework

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?

Upvotes: 1

Views: 20970

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236228

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)
  });

Upvotes: 7

Related Questions