这是我的SQL查询，用于检索产品具有相同id的数量总和，我想在LINQ中使用它

Question

这是我的SQL查询，用于检索其中产品有相同的id，我想在LINQ中使用它。

select p.ProductId,s.TotalQuantity,p.Title from Products p
    join (SELECT ProductId, SUM(Quantity) AS TotalQuantity
            FROM SalePerProduct s join Invoices i on s.InvoiceId = i.InvoiceId
            where i.IssueDate like '%2016%'
            GROUP BY s.ProductId) s
    on s.ProductId = p.ProductId;

我尝试过这个LINQ查询，但它工作不好，它总结了具有相同产品id的数量，但我无法使用它显示产品标题，需要一些帮助。提前谢谢。

 var sales = from sale in db.SalePerProducts
                        join product in db.Products
                        on sale.ProductId equals product.ProductId
                        where sale.ProductId == product.ProductId
                        group sale by sale.ProductId into g
                        select new
                        {
                            ProductId = g.Key,
                            Sum = g.Sum(sale => sale.Quantity),
                        };

Answer 1

答案是这样的，我终于可以检索到多个名称和PRODUCT_ID相同的产品作为一个记录，并汇总数量，谢谢大家。如果你发现这个有用的，请投票

var sales = from sale in db.SalePerProducts
                        join product in db.Products
                        on sale.ProductId equals product.ProductId
                        join invoice in db.Invoices
                        on sale.InvoiceId equals invoice.InvoiceId
                        where sale.ProductId == product.ProductId &&
                                invoice.IssueDate.Date == date
                        group sale by new { sale.ProductId,product.Title } into g
                        select new
                        {
                            ProductId = g.Key.ProductId,
                            Product_Title = g.Key.Title,
                            Quantity_Sold = g.Sum(sale => sale.Quantity)
                        };

Answer 2

var sales = from sale in db.SalePerProducts
                    join product in db.Products
                    on sale.ProductId equals product.ProductId                       
                    group new {sale, product} by sale.ProductId into g
                    select new
                    {
                        ProductId = g.Key,
                        Title = g.FirstOrDefault().product.Title,
                        Sum = g.Sum(s => s.sale.Quantity),
                    };

我删除了不必要的where子句，你的版本中有。