对连接表中的列求和，以使用linq to SQL获取计算值 [英] Sum columns from joined tables to get a calculated value using linq to SQL

问题描述

在下面给出的每个客户的链接图中，我想选择所有订单，然后对于每个订单，我计算了TotalPrice =（订单中包含的所有食品项目的总和*数量）+ ExtraPrice。我正在努力使用linq to sql为它创建一个查询。我需要选择OrderId，Date和TotalPrice，它应该包含一个where子句来比较客户ID。



表格关系



我的尝试：



dc.orders.Select（o => new {o.orderId，o.date，TotalPrice =（（o.orderLines.Select（ol => ; ol.foodItem.price）.Sum（）* decimal.Parse（o.orderLines.Select（ox => ox.quantity）.ToString（）））+ decimal.Parse（o.orderLines.Select（x => ; x.extraPrice）.ToString（）））}）;

In the diagram given in below link for each customer I want to select all orders and then for each order I have calculate a TotalPrice = (Sum of all Food Items included in order * Quantity) + ExtraPrice. I am struggling to create a query for it using linq to sql.I need to select OrderId, Date and TotalPrice and it should include a where clause for comparing customer ID.

Tables Relationship

What I have tried:

dc.orders.Select(o => new { o.orderId, o.date, TotalPrice = ( (o.orderLines.Select(ol => ol.foodItem.price).Sum() * decimal.Parse(o.orderLines.Select(ox => ox.quantity).ToString())) + decimal.Parse(o.orderLines.Select(x => x.extraPrice).ToString())) } );

推荐答案

var q = from c in customer
        join o in order on o.fk_custId equals c.custId
        join ol in orderLines on ol.fk_orderId equals o.orderId
        join fi in foodItem on fi.foodId equals ol.fk_foodId
      \\  where 
        group new { c, o, ol, fi }  into g
        select new 
        { 
           orderId= o.orderId,
           date = o.date, 
           Total = g.Sum(x => x.fi.price* x.ol.quantity),
           TotalPrice= g.Sum(x => x.ol.extraPrice + x.m.Total )
        };

表连接不是特定的。请修改，因为我举例。请尝试此查询。

Table Joins are not specific. Please modify, as i was giving example. Please try this query.

这篇关于对连接表中的列求和，以使用linq to SQL获取计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！