LINQ - Left Join And Group By

Question

I have a problem. This is my products class

public int ProductID { get; set; }
public string ProductName { get; set; }
public int CategoryID { get; set; }
public decimal Price { get; set; }
public int CompanyID { get; set; }
public string QuantityPerUnit { get; set;

And this is OrderDetail Class

public int DetailID { get; set; }
public int OrderID { get; set; }
public int ProductID { get; set; }
public decimal UnitPrice { get; set; }
public int Quantity { get; set; }
public bool Discount { get; set; }

so ı wrote this query in mssql. So, how can I write this query on linq ?

select Products.ProductID, Sum(OrderDetails.Quantity) as 'NumberOfOrdered' from Products
left join OrderDetails on Products.ProductID = OrderDetails.ProductID
group by Products.ProductID
order by Products.ProductID

I Wrote this query but it doesn't work.

from p in _context.Products
join d in _context.OrderDetails on p.ProductsID equals d.ProductsID into t1
from d1 in t1.DefaultIfEmpty()
group new { p, d } by new { p.ProductsID, d.ProductsID } into g
orderby g.Key.ProductsID
select new ProductsOrderDetails
{
ProductsID = g.Key.ProductsID,
QuantityToplam = g.(x=>x.d.Quantity)
}).ToList();

Welcome to SO. What do you mean by "it doesn't work". What are your actual results compared to expected results. You are missing the `Sum` from the SQL. — Peter Smith, Jun 24 '20 at 20:12
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jun 24 '20 at 20:51
try to update the query by : ``group new { p, d } by p.ProductsID into g`` and `{ProductsID = g.Key, QuantityToplam = g.Sum(x=>x?.d?.Quantity ?? 0)}` — Mohammed Sajid, Jun 24 '20 at 20:55
Which ORM are you using (Entity Framework?) + which version? And why don't you have navigation properties like Product.OrderDetails? — Gert Arnold, Jun 25 '20 at 08:21
@Sajid ı tried but ı got exception. group new { p,d } in this code, 'd' turns me exception. — Eren Yılmaz, Jun 25 '20 at 14:15
@GertArnold ı have navigation properties, ı just didn't paste it . I'm using Entity Framework version 6.4.4 , and ı created database with code first. — Eren Yılmaz, Jun 25 '20 at 14:16
Is anything wrong with this props? This is navigation props in my Products class public Category Category { get; set; } public Company Company { get; set; } public List OrderDetails { get; set; } And these are in the Detail Class public Order Order { get; set; } public Product Product { get; set; } — Eren Yılmaz, Jun 25 '20 at 14:25

score 0 · Answer 1 · answered Jun 25 '20 at 08:16

So you have Products, and OrderDetails. Every Product has zero or more OrderDetails, every OrderDetail is the detail of exactly one Product, namely the Product that OrderDetail.ProductId belongs to.

Requirement: From every Product, give me the Id and the sum of the Quantities of all its OrderDetails

Whenever you need the sequence of items, every item with its sub-items, like Schools with their Students, Authors with their Books, Orders with their OrderDetails, consider using one of the overloads of Enumerable.GroupJoin

In this case, I don't want a simple Product with its OrderDetails, I want to compose a special result. For every Product I want the Id and the sum of Quantities of all its OrderDetauls. Therefore I use the Overload with a parameter resultSelector.

var result = dbContext.Products.GroupJoin(    // GroupJoin Products
     dbContext.OrderDetails,                  // with Orderdetails
    product => product.Id,                    // From every Product take the Id
    orderDetail => orderDetail.ProductId,     // From every OrderDetail that the foreign key

    // resultSelector: for every Product and all its zero or more OrderDetails
    // create one new object
    (product, orderDetailsOfThisProduct) => new
    {
        Id = product.Id,
        QuantitiesToPlam = orderDetailsOfThisProduct
            .Select(orderDetail => orderDetail.Quantity)
            .Sum(),
    });

For property QuantitiesToPlam: for every OrderDetail that belongs to this Product: take the value of property Quantity, and Sum them.

I tried this code, but ım taking a exception like this: "The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type"' — Eren Yılmaz, Jun 25 '20 at 14:00

LINQ - Left Join And Group By

1 Answers1