Click here to Skip to main content
14,241,004 members
Rate this:
Please Sign up or sign in to vote.
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
Comments
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.

1 solution

Rate this:
Please Sign up or sign in to vote.

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.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100