14,241,004 members
Rate this:
See more:
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())) } );
Posted
Updated 18-Jul-17 20:46pm
BillWoodruff 16-Jul-17 17:23pm

You have posted the same question in the C# language forum. Please remove one of the questions. Double-posting is not allowed.
Member 12462411 17-Jul-17 4:02am

Sorry, this is the first time I have posted over here.

Rate this:

## Solution 1

```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.