带有 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屋！