具有CASE语句和SUM函数的LINQ查询 [英] LINQ Query with both CASE statement and SUM function

问题描述

我正在努力寻找一个如何使用LINQ查询或LAMBDA返回条件总和的示例.我两个人都是独立写的，但是将CASE和SUM结合起来很麻烦.我很想作弊"并使用一个SQL视图，但是我想先问一下.我非常感谢任何建议.这是我要转换的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

推荐答案

以下是使用Northwinds数据库的示例.这将为您提供预期的结果，但是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();
}

这篇关于具有CASE语句和SUM函数的LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！