开发者

Sum of amount column With LINQ (two tables)

I've two Tables lets simply say :

Products : ProductID, ProductName

Orders : OrderID,ProductID,Amount,Status

I want to write a LINQ query in C# which would select ProductName,Sum(Amount) wher开发者_运维技巧e Status = 1

I am stuck with this simple query :(


class Program
{
  class Product
  {
     public int ProductID { get; set; }
     public string ProductName {get; set; }
     public Product(int ProductID, string ProductName)
     {
        this.ProductID = ProductID;
        this.ProductName = ProductName;
     }
  }

  class Order
  {
     public int OrderID { get; set; }
     public int ProductID { get; set; }
     public decimal Amount { get; set; }
     public int Status { get; set; }
     public Order(int OrderID, Product product, decimal Amount, int Status)
     {
        this.OrderID = OrderID;
        this.ProductID = product.ProductID;
        this.Amount = Amount;
        this.Status = Status;
     }
  }

  private static Product[] Products;

  private static Order[] Orders;

  static void Main(string[] args)
  {
     Products = new Product[]
     {
        new Product(1, "Bolt"),
        new Product(2, "Nut"),
        new Product(3, "Mounting Plate A"),
        new Product(4, "Mounting Plate B")
     }; 

     Orders = new Order[]
     {
        new Order(1, Products[0], 1.12M, 0),
        new Order(2, Products[1], 0.66M, 1),
        new Order(3, Products[2], 4.12M, 0),
        new Order(4, Products[0], 1.11M, 1),
        new Order(5, Products[1], 0.67M, 1)
     };

     var results = from p in Products
                   join o in Orders on p.ProductID equals o.ProductID
                   where o.Status == 1
                   group o by p
                      into orderTotals
                      select new {ProductName = orderTotals.Key.ProductName, TotalAmount = orderTotals.Sum(o => o.Amount)};

     foreach(var result in results)
     {
        Console.WriteLine("{0}: {1}", result.ProductName, result.TotalAmount);
     }
  }
}

Output:

Bolt: 1.11
Nut: 1.33


Although not the optimal, but this should work:

var interim =
         from o in orders
         where o.Status == 1
         select new { o.OrderID, o.ProductID, o.Amount, o.Status};

var final =
         from p in products 
         join x in interim on p.ProductID equals x.ProductID into g
         select new { p.ProductName, Total = g.Sum(y => y.Amount) };
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜