Wsrtjtyk

How do i list data from multiple tables but as 1 record?

i Have 3 tables

Table1: Services

Id: 01, Name: Service 1

Table2: ServiceRequests

Id: 01, ServiceId: 01, Requestor: John

Table3: ServiceApprovers

Id: 01, ServiceId: 01, Approvers: John
Id: 02, ServiceId: 01, Approvers: Linda

My linq code is as per below:

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;

    }

This code will return 2 record due to the ServiceApprovers table.
How do i return 1 record and concatenate the Approvers to become "John,Linda"?

My Controller is as below:

public ActionResult Service_Request()

    {

        var dao = new ServicesDAO();

        ViewBag.CurrentLogin = CurrentLoginDetail.UserName;

        return PartialView("Service_Request", dao.ServiceRequestGetAll().ToList());

    }

My view page is as below:

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



}

There is no need for my list on the view page to display the "Approver" but i need to include the "Approvers" so i can pass them to my modal.

Just group your result by ServiceId

result.GroupBy(_ => _.ServiceId)

and change your model's UserId so that it can take multiple users:

IEnumerable<string> UsersId { get; set; }