从 MVC Razor 中的数据库获取数据时的 LINQ 查询性能问题

Question

问题陈述:我正在尝试使用 Linq 查询绑定(bind)数据库中的多表数据来查看，这需要更多时间。我在数据库中有大约 10000 条记录。有人建议使用 IQueryable 而不是 IEnumerable，但这会影响我当前的代码吗(在 View 和 Controller 中)？或者不使用它我可以完成这个吗？

为了提高加载结果的性能，我应该做什么？我做错了什么？请建议我一些更好的方法来做到这一点......

Controller :

public ActionResult Index()
    {
        var result = (from pr in db.Prod.AsEnumerable()
                      join s in db.Shift.AsEnumerable() on pr.Shift equals s.ShiftID
                      join m in db.Module.AsEnumerable() on pr.Module equals m.ModuleID
                      select new GlobalModel()
                      {
                          prodModelIndex = pr,
                          prodModel = prodModel,
                          shiftModel = s,
                          moduleModel = m,
                          ddlShift = objTransactionGeneralController.GetAllShift(),
                          ddlModule = objTransactionGeneralController.GetAllModule()

                      }).ToList();
    return PartialView(result);

    }





   public TransGeneralModel GetAllModule()
    {

        objTransGeneralModel.ddlModule = (from m in db.Module.AsEnumerable()
                                           select new SelectListItem
                                           {
                                               Value = m.ModuleID.ToString(),
                                               Text = m.ModuleName,
                                           }).ToList();


        return objTransGeneralModel;
    }

    public TransGeneralModel GetAllShift()
    {

        objTransGeneralModel.ddlShift = (from s in db.Shift.AsEnumerable()
                                          select new SelectListItem
                                          {
                                              Value = s.ShiftID.ToString(),
                                              Text = s.ShiftName,
                                          }).ToList();


        return objTranGeneralModel;
    }

查看:

@model IEnumerable<SIA.Models.Trans.GlobalModel>

@using GridMvc.Html

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
    ViewBag.Title = "Index";

    }

    <link rel="stylesheet" href="@Url.Content("~/Content/jquery.dataTables.min.css")">
    <script src="@Url.Content("~/Scripts/jquery-2.1.1.min.js")"></script>


    <h2>Details</h2>
    <hr />

    <div style="width: 1000px; padding-left: 70px">
        @Html.Partial("Create")
        <br />
    </div>

    <h5 class="pull-right">
        <b class="fa fa-keyboard-o" style="color: blue"></b>
        @Ajax.ActionLink("Edit", "ProdEdit", "Prod", new { }, new AjaxOptions
    {
        InsertionMode = InsertionMode.Replace,
        UpdateTargetId = "prod-details",
        HttpMethod = "GET",
    }, new { style = "color:blue" })
    </h5>

    <br />
    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
        @Html.ValidationSummary(true)
        if (Model.FirstOrDefault().prodModelIndex != null)
        { 

        <div id="prod-details">
            <table class="table table-striped" id="tblProdDetails">
                <thead>
                    <tr>

                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.ProdID)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Date)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Module)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().productionModelIndex.Shift)
                        </th>

                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Hour)
                        </th>
                        <th>
                            @Html.DisplayNameFor(model => model.FirstOrDefault().prodModelIndex.Output)
                        </th>

                    </tr>
                </thead>
                <tbody>
                    @foreach (var item in Model)
                    {
                        <tr id="customer-row-@item.prodModelIndex.ProdID">


                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.ProdID)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.Date)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.moduleModel.ModuleName)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.shiftModel.ShiftName)
                                @Html.HiddenFor(modelItem => item.prodModelIndex.Shift)
                            </td>

                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.Hour)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.prodModelIndex.Output)
                            </td>
                        </tr>
                    }
                </tbody>
            </table>

        </div>
        }
    }

    <script>

        $(document).ready(function () {

            $('#tblProdDetails').dataTable({
                "order": [[1, "desc"], [3, "asc"]]

            });

        });
    </script>

    @section Scripts {
        @Scripts.Render("~/bundles/jqueryval")
        @Scripts.Render("~/Scripts/jquery.dataTables.min.js")
        <script type='text/javascript'>
            $(function () {
                $('.datepicker').datepicker({
                    format: "dd M yyyy",
                }).on('changeDate', function (e) {
                    $(this).datepicker('hide');
                });
            })

        </script>
    }

Answer 1

首先，当您调用 ToList()、AsEnumerable() 或 FirstOrDefault() 等方法时，它将执行查询数据库。在您的情况下，最好删除它们以使用 joins 进行单个查询。

var result = (from pr in db.Prod
                      join s in db.Shift on pr.Shift equals s.ShiftID
                      join m in db.Module on pr.Module equals m.ModuleID
                      select new GlobalModel()
                      {
                          prodModelIndex = pr,
                          prodModel = prodModel,
                          shiftModel = s,
                          moduleModel = m

                      }).ToList();