問(wèn)題描述

我正在努力尋找如何使用 LINQ 查詢或 LAMBDA 返回條件和的示例.我都是獨(dú)立編寫(xiě)的，但是將 CASE 與 SUM 結(jié)合起來(lái)很麻煩.我很想作弊"并使用 SQL 視圖，但我想我會(huì)先問(wèn).我非常感謝任何建議.這是我要轉(zhuǎn)換的 SQL.

I'm struggling to find an example of how to return a conditional sum using a LINQ query or LAMBDA. I've written both independently but combining the CASE with SUM is vexing. I'm tempted to "cheat" and use a SQL view, but thought I'd ask first. I greatly appreciate any suggestions. Here's my SQL that I'm looking to convert.

SELECT     p.product_name, 
           SUM(CASE WHEN o.order_dt <= getdate() - 1 THEN o.quantity END) AS volume_1day, 
           SUM(CASE WHEN o.order_dt <= getdate() - 7 THEN o.quantity END) AS volume_7day, 
           SUM(CASE WHEN o.order_dt <= getdate() - 30 THEN o.quantity END) AS volume_30day, 
           SUM(o.quantity) AS volume_all
FROM       products p left outer join orders o on p.product_id = o.product_id
GROUP BY   p.product_name

推薦答案

這是一個(gè)使用 Northwinds 數(shù)據(jù)庫(kù)的示例.這將為您提供您期望的結(jié)果，但 SQL 與您的示例不匹配.

Here is an example using the Northwinds database. This will get you the results that you are expecting but the SQL won't match your example.

using (var context = new NorthwindEntities())
{
    DateTime volumn1Date = DateTime.Today.AddDays(-1);
    DateTime volumn7Date = DateTime.Today.AddDays(-7);
    DateTime volumn30Date = DateTime.Today.AddDays(-30);

    var query = from o in context.Order_Details
                group o by o.Product.ProductName into g
                select new
                {
                    ProductName = g.Key,
                    Volume1Day = g.Where(d => d.Order.OrderDate.Value <= volumn1Date)
                                  // cast to Int32? because if no records are found the result will be a null                                              
                                  .Sum(d => (Int32?) d.Quantity),
                    Volume7Day = g.Where(d => d.Order.OrderDate.Value <= volumn7Date)
                                  .Sum(d => (Int32?) d.Quantity),
                    Volume30Day = g.Where(d => d.Order.OrderDate.Value <= volumn30Date)
                                   .Sum(d => (Int32?) d.Quantity)
                };


    var list = query.ToList();
}

這篇關(guān)于帶有 CASE 語(yǔ)句和 SUM 函數(shù)的 LINQ 查詢的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！