c# - How to write a Query to get sum total of each order in the list joining 3 tables -

c# - How to write a Query to get sum total of each order in the list joining 3 tables -

i learning asp.net mvc 5 , have complicated query cannot right in controller. need show list of upcoming milestones , display total amount of money has been spend on campaign each milestone represents. campaigns in 1 table, milestones in another, services in , orders in another.

the milestones, campaigns , services tied campaignid. orders tied services table serviceid. using code first , entity framework, cannot figure out how write query assemble list send view.

this view

@model ienumerable

@{ viewbag.title = "lookupmilestones"; layout = "~/views/shared/_layoutadmindashboard.cshtml"; } <div id="admindash" class="col-md-8 col-xs-10 admindash light-grey"> <p class="lead text-uppercase">more upcoming milestones</p> <table class="table table-bordered table-condensed"> <thead> <tr class="active"> <td>id</td> <td>next milestones</td> <td>pledges $ in</td> <td>milestone $</td> <td>admin</td> </tr> </thead> <tbody> @foreach (var item in model) { <tr> <td>@html.displayfor(modelitem => item.campaignid)</td> <td>@html.displayfor(modelitem => item.termenddate)</td> <td>pledges$</td> <td>@html.displayfor(modelitem => item.targetamount)</td> <td> @html.actionlink("details", "../campaignsfsc/mydetails/", new { id = item.campaignid }) </td> </tr> } </tbody> </table> </div>

i need help writing query pass view loop through , fill in fields. can except total amount each milestone because amount stored in orders table, there not direct path.

[dbo].[milestonedetails] ( [id] int identity (1, 1) not null, [milestoneid] int not null, [campaignid] int not null, [userid] nvarchar (max) null [dbo].[order] ( [orderid] int identity (1, 1) not null, [userid] nvarchar (128) not null, [serviceid] int not null, [paymentprofileid] int not null, [orderdate] datetime not null, [amount] real not null [dbo].[service] ( [serviceid] int identity (1, 1) not null, [campaignid] int not null, [cost] decimal (19, 4) not null, [description] nvarchar (500) not null, [deliverydate] datetime not null, [deliverydesc] nvarchar (500) not null, [maxquantity] int null, [approveddate] datetime null, [displayorder] nvarchar (max) null, [status] int null

so effort set in controller:

public actionresult lookupmilestone() { if (modelstate.isvalid) { var milestones = (from m in db.milestonedetails orderby m.termenddate ascending m.termenddate >= system.datetime.now select new { m.campaignid, totalorders = (from serv in db.services on m.campaignid equals serv.campaignid bring together ord in db.orders on serv.serviceid equals ord.serviceid).sum(ord => ord.amount))} ).distinct(); homecoming view("lookupmilestones", milestones);

yes you're on right track. seem having syntactical issues

select new { m.campaignid, totalorders = (from serv in db.services bring together ord in db.orders on serv.serviceid equals ord.serviceid m.campaignid == serv.campaignid && ord.userid == m.userid //this line might not right depending on userid used select ord ).sum(ord => ord.amount) }

oh , in query, optimize, set clause before orderby clause cut down memory footprint

and i'm not seeing termenddate or targetamount in db schema anywhere or i'd help fill in properties

c# linq entity-framework