如何编写查询以获得连接3个表的列表中每个订单的总和

我刚刚在学习ASP.net MVC 5，我有一个复杂的查询，我无法在控制器中正确处理。我需要显示一个即将到来的里程碑列表，并显示每个里程碑所代表的活动花费的总金额。活动在一张表中，里程碑在另一张表，服务在另一个表中，订单在另一份表中。

里程碑、活动和服务都由CampaignId绑定。订单通过ServiceId绑定到服务表。我使用的是代码优先和实体框架，但我不知道如何编写查询来组装要发送到视图的列表。

这是View

@型号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>

我需要帮助编写查询，并将其传递给视图以循环并填充字段。我可以得到除了每个里程碑的总金额之外的所有金额，因为该金额存储在Orders表中，但没有直接的路径。

[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

因此，这是我试图将所有这些放在控制器中：

public ActionResult LookUpMilestone()
    {
        if (ModelState.IsValid)
        {
var Milestones = (from m in db.MilestoneDetails                                      
orderby m.TermEndDate ascending
where m.TermEndDate >= System.DateTime.Now
 select new {
     m.CampaignId,
     totalOrders = (from serv in db.Services 
                     on m.CampaignId equals serv.CampaignId
                       join ord in db.Orders on serv.ServiceId equals       
ord.ServiceId).Sum(ord => ord.Amount))}
).Distinct();
return View("LookUpMilestones", Milestones);

select new {
 m.CampaignId,
 totalOrders = (from serv in db.Services 
                join ord in db.Orders on serv.ServiceId equals ord.ServiceId
                where m.CampaignId == serv.CampaignId
                   && ord.UserId == m.UserId  //this line might not be correct depending on what UserId is used for
                select ord
               ).Sum(ord => ord.Amount)
}