时间戳

首页 > 解决方案 > 列出来自多个表的数据，但作为 1 条记录

asp.net-mvc - 列出来自多个表的数据，但作为 1 条记录

问题描述

我如何列出来自多个表的数据但作为 1 条记录？

我有 3 张桌子

表 1：服务

ID：01，名称：服务 1

表 2：服务请求

ID：01，ServiceId：01，请求者：John

表 3：ServiceApprovers

Id：01，ServiceId：01，批准者：John Id：02，ServiceId：01，批准者：Linda

我的 linq 代码如下：

public IEnumerable<ServiceRequestsViewModel> ServiceRequestGetAll()
    {
        var result = (from srv in DB.Services
                      join srq in DB.ServiceRequests on srv.Id equals srq.ServiceId 
                      join srp in DB.ServiceApprovers on srq.ServiceId equals srp.ServiceId
                      select new ServiceRequestsViewModel
                      {
                          Id = srq.Id,
                          ServiceId = srq.ServiceId,
                          RequestorId = srq.RequestorId,
                          ApproverId = srp.UserId,
                          Name = srv.Name,
                          Description = srq.Description,
                          Status = srq.Status,
                          Attachment = srq.Attachment,
                          CreatedBy = srq.CreatedBy,
                          CreatedDate = srq.CreatedDate
                      })
                      .OrderByDescending(z => z.CreatedDate);
        return result;
    }

由于 ServiceApprovers 表，此代码将返回 2 条记录。我如何返回 1 条记录并连接批准者成为“John，Linda”？

我的控制器如下：

public ActionResult Service_Request()
    {
        var dao = new ServicesDAO();
        ViewBag.CurrentLogin = CurrentLoginDetail.UserName;
        return PartialView("Service_Request", dao.ServiceRequestGetAll().ToList());
    }

我的查看页面如下：

<script>

$(document).ready(function () {

    $.ajax({
        type: "GET",
        url: '/Services/ServiceRequestCount/',
        success: function (response) {
            $(".sidenotification").text(response);
        }
    })


    $(document).on("click", '.view_button', function (e) {
        var serviceid = $(this).data('id');
        var name = $(this).data('name');
        var desc = $(this).data('description');
        var attchmnt = $(this).data('attachment');
        var requestor = $(this).data('requestorid');
        //var approver = $(this).data('approverid');
        var createdby = $(this).data('createdby');

        var createddate = $(this).data('createddate');


        //alert(requestor);

        $(".editService_ServiceId").val(serviceid);
        $(".editService_Name").text(name);
        $(".editService_Description").val(desc);
        $(".editService_Attachment").val(attchmnt);
        $(".editService_Requestor").val(requestor);
        //$(".editService_Approver").val(approver);
        $(".editService_CreatedBy").val(createdby);
        $(".editService_CreatedDate").val(createddate);

    });


    $('[data-toggle="tooltip"]').tooltip();

});


<section class="section">
<div class="row">
    <div class="col-md-12">
        <h2>Service Request Listing</h2>

        <br />
        <div class="row">


            <div class="container">

                <table class="table">
                    <thead>
                        <tr>
                            @*<th>Id</th>
            <th>Service Id</th>*@
                            <th>Name</th>
                            <th>Requestor</th>
                            <th>Description</th>

                            <th>Submitted Date</th>
                            <th>Status</th>
                            <th></th>
                        </tr>
                    </thead>


                    @foreach (var item in Model.Where(i => i.RequestorId == ViewBag.CurrentLogin || i.ApproverId == ViewBag.CurrentLogin))
                    {
                        <tbody>
                            <tr>

                                <td>@item.Name</td>

                                <td>@item.RequestorId</td>
                                <td>@item.Description</td>

                                <td>@item.CreatedDate.ToString("dd-MMM-yyyy")</td>

                                @if (item.Status == "Submitted")
                                {
                                    @*<td><div class="btn btn-static" style="cursor:default;">@item.Status</div></td>*@
                                    <td><div class="btn btn-staticsubmit badge-pill badge-primary" style="cursor:default;" data-toggle="tooltip" title="Request submitted and pending for approval">@item.Status</div></td>
                                }
                                @if (item.Status == "Approved")
                                {
                                    @*<td><div class="btn btn-static" style="cursor:default;">@item.Status</div></td>*@
                                    <td><div class="btn btn-staticapprove badge-pill badge-primary" style="cursor:default;" data-toggle="tooltip" title="Request has been approved">@item.Status</div></td>
                                }
                                @if (item.Status == "Rejected")
                                {
                                    @*<td><div class="btn btn-static" style="cursor:default;">@item.Status</div></td>*@
                                    <td><div class="btn btn-staticreject badge-pill badge-primary" style="cursor:default;" data-toggle="tooltip" title="Request has been rejected">@item.Status</div></td>
                                }


                                <td>
                                    <button type="button" class="btn view_button" href="#viewServiceModal" data-toggle="modal"
                                            data-id="@item.Id"
                                            data-name="@item.Name"
                                            data-description="@item.Description"
                                            data-attachment="@item.Attachment"
                                            data-requestorid="@item.RequestorId"
                                            data-createdby="@item.CreatedBy"
                                            data-createddate="@item.CreatedDate">
                                        View
                                    </button>
                                </td>
                            </tr>
                        </tbody>
                    }


                </table>
            </div>



        </div>
    </div>
</div>


<div class="modal fade" id="viewServiceModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true">
@using (Html.BeginForm("ServicesApprove", "Services", FormMethod.Post, new { @Id = "mainForm", @class = "edit_form" }))
{

<div class="modal-dialog modal-dialog-centered" style="min-width:60%" role="document">
    <input type="hidden" class="form-control editService_ServiceId" id="Id" name="Id" />
    <div class="modal-content">
        <div class="modal-header">
            <h3><label class="control-label editService_Name" for="Name"></label></h3>
        </div>
        <div class="modal-body">
            <div class="form-group">



                <div class="form-group">
                    <fieldset>
                        <div class="row">
                            <div class="col-6">
                                <label class="control-label" for="Desc"><strong>Description:</strong></label>
                                <textarea readonly class="form-control-plaintext editService_Description" name="Description" id="Description" type="text" style="resize:none; cursor:default;"></textarea>
                            </div>
                            <div class="col-6">
                                <label class="control-label" for="Attachment"><strong>Attachment:</strong></label>
                                <input readonly class="form-control-plaintext editService_Attachment" name="Attachment" id="Attachment" type="text" style="cursor:default;">
                            </div>
                        </div>
                    </fieldset>
                </div>

                <div class="form-group">
                    <fieldset>
                        <div class="row">
                            <div class="col-6">
                                <label class="control-label" for="RequestorId"><strong>Requested By:</strong></label>
                                <input readonly class="form-control-plaintext editService_Requestor" name="RequestorId" id="RequestorId" type="text" style="cursor:default;">
                            </div>
                            <div class="col-6">
                                <label class="control-label" for="CreatedDate"><strong>Submitted Date:</strong></label>
                                <input readonly class="form-control-plaintext editService_CreatedDate" name="CreatedDate" id="CreatedDate" type="text" style="cursor:default;">
                            </div>
                        </div>
                    </fieldset>
                </div>


            </div>
            <div class="modal-footer">

                <input type="submit" class="btn btn-success" value="Approve" formaction="ServicesApprove" />
                <input type="submit" class="btn btn-danger" value="Reject" formaction="ServicesReject" />

                <button type="button" class="btn btn-outline-danger" data-dismiss="modal">Cancel</button>
            </div>
        </div>
    </div>
</div>

}

视图页面上的列表不需要显示“批准者”，但我需要包含“批准者”，以便我可以将它们传递给我的模式。

标签： asp.net-mvclinq

解决方案

只需按 ServiceId 对结果进行分组

result.GroupBy(_ => _.ServiceId)

并更改您的模型UserId，以便它可以容纳多个用户：

IEnumerable<string> UsersId { get; set; }

推荐阅读