MVC 5 EF 6 - 如何使用相关表对象标准搜索表 [英] MVC 5 EF 6 - How to Search a Table Using Related Table Object Criteria

问题描述

我需要显示PO请求的结果表，其中包括PO Items（这部分我想出来的），并为所有显示的项目提供搜索功能（这部分我被大部分弄清楚）。

使用下面的代码，这是一个稍微删除的版本，我可以显示我需要的数据，我能够搜索几乎所有我需要的，只要它是来自PORequests模型（这是父模型）。当我尝试使用子模型POItem进行搜索时，实际的对象（ID，描述等）不可访问。这是使用DatabaseFirst模型和EF6.x DbContextGenerator构建的。

  // POItemData ViewModel 
 using FinanceSearch.Models; 
命名空间FinanceSearch.ViewModels 
 {
 public class POItemData 
 {
 public PagedList.IPagedList< PORequest> PORequests {get;组; } 
 public PagedList.IPagedList< POItem> POItems {get;组; } 
} 
} 
 
 // POItems模型
命名空间FinanceSearch.Models 
 {
 using System; 
使用System.Collections.Generic; 
 
 public partial class POItem 
 {
 public int ID {get;组; } 
 public int Amount {get;组; } 
 public string描述{get;组; } 
 
 public virtual PORequest PORequest {get;组; 
} 
} 
 
 // PORequest模型
命名空间FinanceSearch.Models 
 {
 using System; 
使用System.Collections.Generic; 
使用System.ComponentModel.DataAnnotations; 
 
 public partial class PORequest 
 {
 [System.Diagnostics.CodeAnalysis.SuppressMessage（Microsoft.Usage，CA2214：DoNotCallOverridableMethodsInConstructors）] 
 public PORequest ）
 {
 this.POAttachments = new HashSet< POAttachment>（）; 
 this.POItems = new HashSet< POItem>（）; 
} 
 
 //其他相关内容
 
 public virtual ICollection< POItem> POItems {get;组; } 
 
} 
} 
 
 
 //控制器
使用系统; 
使用System.Collections.Generic; 
使用System.Data; 
使用System.Data.Entity; 
使用System.Linq; 
使用System.Net; 
使用System.Web; 
使用System.Web.Mvc; 
使用FinanceSearch.Models; 
使用PagedList; 
使用System.Web.UI.WebControls; 
使用System.Data.Entity.SqlServer; 
使用FinanceSearch.ViewModels; 
 
命名空间FinanceSearch.Controllers 
 {
 public class PORequestsController：Controller 
 {
 private Finance db = new Finance（）; 
 
 
 
 public ActionResult Index（int？id，int？page，string sortOrder，string currentFilter，string poNumber，
 string AppropNumber，string ContractNumber，string ItemDescription） 
 {
 
 ViewBag.CurrentSort = sortOrder; 
 ViewBag.POSortParm = String.IsNullOrEmpty（sortOrder）？ PONumber_desc：; 
 ViewBag.AppropNumberSortParm = sortOrder ==AppropNumber？ AppropNumber_desc：AppropNumber; 
 ViewBag.ContractNumberSortParm = sortOrder ==ContractNumber？ ContractNumber_desc：ContractNumber; 
 
 
 if（poNumber！= null）
 {
 page = 1; 
} 
 else 
 {
 ponumber = currentFilter; 
} 
 
 // int pageSize = 3; 
 int pageNumber =（第1页）; 
 
 var viewModel = new POItemData（）; 
 
 viewModel.PORequests = db.PORequests 
 .Include（i => i.POItems）
 .OrderBy（i => i.ID）.ToPagedList（pageNumber， 5）; 
 
 if（！String.IsNullOrEmpty（poNumber））
 {
 viewModel.PORequests = viewModel.PORequests.Where（s => s.PONumber.Contains（ponumber）） .ToPagedList（pageNumber，5）; 
} 
 if（！string.IsNullOrEmpty（AppropNumber））
 {
 viewModel.PORequests = viewModel.PORequests.Where（x => x.AppropNumber.Contains（AppropNumber） ）.ToPagedList（pageNumber，5）; 
} 
 if（！string.IsNullOrEmpty（ContractNumber））
 {
 viewModel.PORequests = viewModel.PORequests.Where（x => x.ContractNumber.Contains（ContractNumber） ）.ToPagedList（pageNumber，5）; 
} 
 if（！string.IsNullOrEmpty（ItemDescription））
 {
 
} 
  

在ItemDescription if语句中，类似以下内容将会很好：

  if（！string.IsNullOrEmpty（ItemDescription））
 {
 viewModel.PORequests = viewModel.PORequests.Where（x => x.POItems.Description.Contains（ItemDescription））ToPagedList（pageNumber，5 ）; 
} 
  

x.POItems.Description部分在这个意义上不存在。显然与它相关列表。继续剩余的代码...

  switch（sortOrder）
 {
 casePONumber_desc：
 viewModel.PORequests = viewModel.PORequests.OrderByDescending（s => s.PONumber）.ToPagedList（pageNumber，5）; 
 break; 
 caseAppropNumber：
 viewModel.PORequests = viewModel.PORequests.OrderBy（s => s.AppropNumber）.ToPagedList（pageNumber，5）; 
 break; 
 caseAppropNumber_desc：
 viewModel.PORequests = viewModel.PORequests.OrderByDescending（s => s.AppropNumber）.ToPagedList（pageNumber，5）; 
 break; 
 caseContractNumber：
 viewModel.PORequests = viewModel.PORequests.OrderBy（s => s.ContractNumber）.ToPagedList（pageNumber，5）; 
 break; 
 caseContractNumber_desc：
 viewModel.PORequests = viewModel.PORequests.OrderByDescending（s => s.ContractNumber）.ToPagedList（pageNumber，5）; 
 break; 
 default：
 viewModel.PORequests = viewModel.PORequests.OrderBy（s => s.PONumber）.ToPagedList（pageNumber，5）; 
 break; 
} 
 
 
 return View（viewModel）; 
} 
  

在上面的代码中，我还包括我的分页和排序的东西，因为在我遇到的教程和答案中，似乎有所改变，这个项目是必需的。

下面是我使用的索引/视图显示结果：

  //查看（索引）
 @model FinanceSearch.ViewModels.POItemData 
 @using PagedList.Mvc; 
< link href =〜/ Content / PagedList.css =stylesheettype =text / css/> 
 
 @ {
 ViewBag.Title =PORequests; 
} 
 
< h2>财务< / h2> 
 
 @ *< p> 
 @ Html.ActionLink（创建新，创建）
< / p> * @ 
 
< p> 
< button class =btn btn-defaultdata-toggle =collapsehref =＃MainFilter@ * data-target =＃MainFilter* @ aria-multiselectable =true =true> Filter By ...< / button> 
< / p> 
 
 @ *< div id =accordionrole =tablistaria-multiselectable =true> * @ 
 @using（Html.BeginForm（Index PORequests，FormMethod.Get））
 {
< div id =MainFilterclass =collapse> 
 
< table class =table-responsive> 
< tr> 
< td> 
项目：
< / td> 
< td> 
 @ Html.TextBox（ItemDescription，ViewBag.CurrentFilter as string）//这是用户使用字符串
< / td> 
< / tr> 
< tr> 
< td> 
 PO＃：
< / td> 
< td> 
 @ Html.TextBox（PONumber，ViewBag.CurrentFilter as string）
< / td> 
< / tr> 
< / table> 
< br /> 
< p> 
< button class =btn btn-primary btn-smdata-parent =＃MainFilterdata-toggle =collapsehref =＃SubFilterPOInfo@ * data-target =＃SubFilterPOInfo* @>按邮件信息...筛选< / button> 
< / p> 
 
< div id =SubFilterPOInfoclass =collapse> 
< table class =table-responsive> 
< tr> 
< td> 
 AppropNumber：
< / td> 
< td> 
 @ Html.TextBox（AppropNumber，ViewBag.CurrentFilter as string）
< / td> 
< / tr> 
< tr> 
< td> 
合同编号：
< / td> 
< td> 
 @ Html.TextBox（ContractNumber，ViewBag.CurrentFilter as string）
< / td> 
< / tr> 
< / table> 
< / div> 
 
} 
 
< div style =overflow-x：scroll> 
 
< table class =tablestyle =white-space：nowrap> 
 
< tr> 
< th>< / th> 
< th>< / th> 
< th> 
 @ Html.ActionLink（PONumber，Index，new {sortOrder = ViewBag.POSortParm，currentFilter = ViewBag.CurrentFilter}）
< / th& 
< th> 
 @ Html.ActionLink（AppropNumber，Index，new {sortOrder = ViewBag.AppropNumberSortParm，currentFilter = ViewBag.CurrentFilter}）
< / th& 
< th> 
 @ Html.ActionLink（ContractNumber，Index，new {sortOrder = ViewBag.ContractNumberSortParm，currentFilter = ViewBag.CurrentFilter}）
< / th> 
< / tr> 
 
 @foreach（Model.PORequests中的var项）
 {
< tr> 
< td> 
 @ * @ Html.ActionLink（Edit，Edit，new {id = item.ID}）| * @ 
 @ Html.ActionLink（Details，Details，new { id = item.ID}）| 
 @ * @ Html.ActionLink（删除，删除，新的{id = item.ID}）* @ 
< / td> 
< td> 
 //我可以在PORequests表中列出相关的项目
 @foreach（item.POItems中的var poitem）
 {
 @ poitem.Description< br /> 
} 
 
< / td> 
< td> 
 @ Html.DisplayFor（modelItem => item.PONumber）
< / td> 
< td> 
 @ Html.DisplayFor（modelItem => item.AppropNumber）
< / td> 
< td> 
 @ Html.DisplayFor（modelItem => item.ContractNumber）
< / td> 
 
< / tr> 
 
} 
< / table> 
 
< / div> 
< br /> 
 //分页和排序东西
 @ Model.PORequests.PageCount 
 @（Model.PORequests.PageCount< Model.PORequests.PageNumber？0：Model.PORequests.PageNumber） Html.PagedListPager（Model.PORequests，page => Url.Action（Index，new {page，sortOrder = ViewBag.CurrentSort，currentFilter = ViewBag.CurrentFilter}）
  / pre> 
 
 
 总结： 
 
 
 
我似乎不了解使用变量ItemDescription在POItems列表中进行搜索，并获取包含POItem表作为相关对象的PORequest表，以显示结果。我的其他搜索都按预期方式运行。
 
 
 
我已经在本网站上搜索过，但是任何类似的问题都涉及到它的视图，只显示每个项目列表，我可以做，或创建/编辑功能，或者也许，他们超出了我对MVC的理解，足够他们似乎不是一个解决方案。
 
 
 
以下是一些这样的例子：
 
 
 
  MVC代码第一：自己的模型与SimpleMembership用户之间的一对多关系 
 
 
 
  EntityFramework 6.如何获取相关对象 
 
 
 
  https://stackoverflow.com/questions/263 72533 / add-the-list-of-object-to-parent-object-using-mvc-5-and-ef-6  
 
 
 
任何帮助
解决方案
向下搜索搜索表达式中的项目：
  if（！string.IsNullOrEmpty（ItemDescription））
 {
 viewModel.PORequests = viewModel.PORequests.Where x => x.POItems 
 .Any（i =&i; Description.Contains（ItemDescription）））
 .ToPagedList（pageNumber，5）; 
} 
  
 
I need to display a resulting table of PO Requests which includes the PO Items (this part I figured out) and provide search functionality for all items displayed (this part I got mostly figured out).

Using the code below, which is a slightly stripped down version, I am able to display the data I need and I am able to search for nearly all I need, as long as it is data from the PORequests model (this is the "parent model"). When I try to search using the "child model," POItems, the actual objects (ID, Description, etc.) are not accessible. This was built using DatabaseFirst model and EF6.x DbContextGenerator. 
//POItemData ViewModel
using FinanceSearch.Models;
namespace FinanceSearch.ViewModels
{
    public class POItemData
    {
        public PagedList.IPagedList<PORequest> PORequests { get; set; }
        public PagedList.IPagedList<POItem> POItems { get; set; }
    }
}

//POItems Model
namespace FinanceSearch.Models
{
    using System;
    using System.Collections.Generic;

    public partial class POItem
    {
        public int ID { get; set; }
        public int Amount { get; set; }
        public string Description { get; set; }

        public virtual PORequest PORequest { get; set; }
    }
}

//PORequest Model
namespace FinanceSearch.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;

    public partial class PORequest
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public PORequest()
        {
            this.POAttachments = new HashSet<POAttachment>();
            this.POItems = new HashSet<POItem>();
        }

        //other relevant stuff

        public virtual ICollection<POItem> POItems { get; set; }

    }
}


//Controller
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using FinanceSearch.Models;
using PagedList;
using System.Web.UI.WebControls;
using System.Data.Entity.SqlServer;
using FinanceSearch.ViewModels;

namespace FinanceSearch.Controllers
{
    public class PORequestsController : Controller
    {
        private Finance db = new Finance();



        public ActionResult Index(int? id, int? page, string sortOrder, string currentFilter, string poNumber,
           string AppropNumber, string ContractNumber, string ItemDescription)
        {

            ViewBag.CurrentSort = sortOrder;
            ViewBag.POSortParm = String.IsNullOrEmpty(sortOrder) ? "PONumber_desc" : "";
            ViewBag.AppropNumberSortParm = sortOrder == "AppropNumber" ? "AppropNumber_desc" : "AppropNumber";
            ViewBag.ContractNumberSortParm = sortOrder == "ContractNumber" ? "ContractNumber_desc" : "ContractNumber";


            if (poNumber != null)
            {
                page = 1;
            }
            else
            {
                ponumber = currentFilter;
            }

            //int pageSize = 3;
            int pageNumber = (page ?? 1);

            var viewModel = new POItemData();

            viewModel.PORequests = db.PORequests
               .Include(i => i.POItems)
               .OrderBy(i => i.ID).ToPagedList(pageNumber, 5);

            if (!String.IsNullOrEmpty(poNumber))
            {
                viewModel.PORequests = viewModel.PORequests.Where(s => s.PONumber.Contains(ponumber)).ToPagedList(pageNumber, 5);
            }
            if (!string.IsNullOrEmpty(AppropNumber))
            {
                viewModel.PORequests = viewModel.PORequests.Where(x => x.AppropNumber.Contains(AppropNumber)).ToPagedList(pageNumber, 5);
            }
            if (!string.IsNullOrEmpty(ContractNumber))
            {
                viewModel.PORequests = viewModel.PORequests.Where(x => x.ContractNumber.Contains(ContractNumber)).ToPagedList(pageNumber, 5);
            }
            if (!string.IsNullOrEmpty(ItemDescription))
            {

            }
In the ItemDescription if statement, something like the following would be nice:
if (!string.IsNullOrEmpty(ItemDescription))
{
viewModel.PORequests = viewModel.PORequests.Where(x => x.POItems.Description.Contains(ItemDescription)).ToPagedList(pageNumber, 5);
}
The x.POItems.Description part doesn't exist in this sense. Apparently related to it being a list. Continuing remainder of code...
            switch (sortOrder)
            {
                case "PONumber_desc":
                    viewModel.PORequests = viewModel.PORequests.OrderByDescending(s => s.PONumber).ToPagedList(pageNumber, 5);
                    break;
                case "AppropNumber":
                    viewModel.PORequests = viewModel.PORequests.OrderBy(s => s.AppropNumber).ToPagedList(pageNumber, 5);
                    break;
                case "AppropNumber_desc":
                    viewModel.PORequests = viewModel.PORequests.OrderByDescending(s => s.AppropNumber).ToPagedList(pageNumber, 5);
                    break;
                case "ContractNumber":
                    viewModel.PORequests = viewModel.PORequests.OrderBy(s => s.ContractNumber).ToPagedList(pageNumber, 5);
                    break;
                case "ContractNumber_desc":
                    viewModel.PORequests = viewModel.PORequests.OrderByDescending(s => s.ContractNumber).ToPagedList(pageNumber, 5);
                    break;
                default: 
                    viewModel.PORequests = viewModel.PORequests.OrderBy(s => s.PONumber).ToPagedList(pageNumber, 5);
                    break;
            }


            return View(viewModel);
        }
In the code above, I included my paging and sorting stuff as well, because in the tutorials and answers I have come across, it appears to make a difference in how it all turns out, and is required for this project.

Below is the Index/View I am using to display the results:
//View    (Index)    
@model FinanceSearch.ViewModels.POItemData
@using PagedList.Mvc;
<link href="~/Content/PagedList.css" rel="stylesheet" type="text/css" />

@{
    ViewBag.Title = "PORequests";
}

<h2>Finance</h2>

@*<p>
    @Html.ActionLink("Create New", "Create")
</p>*@

<p>
    <button class="btn btn-default" data-toggle="collapse" href="#MainFilter" @*data-target="#MainFilter"*@ aria-multiselectable="true" aria-expanded="true">Filter By...</button>
</p>

@*<div id="accordion" role="tablist" aria-multiselectable="true">*@
@using (Html.BeginForm("Index", "PORequests", FormMethod.Get))
{
    <div id="MainFilter" class="collapse">

        <table class="table-responsive">
            <tr>
                <td>
                    Item: 
                </td>
                <td>
                    @Html.TextBox("ItemDescription", ViewBag.CurrentFilter as string) //this is where the user would search items using a string
                </td>
            </tr>
            <tr>
                <td>
                    PO #:
                </td>
                <td>
                    @Html.TextBox("PONumber", ViewBag.CurrentFilter as string)
                </td>
            </tr>
        </table>
        <br />
        <p>
            <button class="btn btn-primary btn-sm" data-parent="#MainFilter" data-toggle="collapse" href="#SubFilterPOInfo" @*data-target="#SubFilterPOInfo"*@>Filter by PO Info...</button>
        </p>

        <div id="SubFilterPOInfo" class="collapse">
            <table class="table-responsive">
                <tr>
                    <td>
                        AppropNumber:
                    </td>
                    <td>
                        @Html.TextBox("AppropNumber", ViewBag.CurrentFilter as string)
                    </td>
                </tr>
                <tr>
                    <td>
                        Contract Number:
                    </td>
                    <td>
                        @Html.TextBox("ContractNumber", ViewBag.CurrentFilter as string)
                    </td>
                </tr>
            </table>
        </div>

}

<div style="overflow-x: scroll">

    <table class="table" style="white-space:nowrap">

        <tr>
            <th></th>
            <th></th>
            <th>
                @Html.ActionLink("PONumber", "Index", new { sortOrder = ViewBag.POSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("AppropNumber", "Index", new { sortOrder = ViewBag.AppropNumberSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("ContractNumber", "Index", new { sortOrder = ViewBag.ContractNumberSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
        </tr>

        @foreach (var item in Model.PORequests)
            {
            <tr>
                <td>
                    @*@Html.ActionLink("Edit", "Edit", new { id = item.ID }) |*@
                    @Html.ActionLink("Details", "Details", new { id = item.ID }) |
                    @*@Html.ActionLink("Delete", "Delete", new { id = item.ID })*@
                </td>
                <td>
                    //I am able to list the related items in the PORequests table
                    @foreach (var poitem in item.POItems)
                    {
                        @poitem.Description<br />
                    }

                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.PONumber)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.AppropNumber)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ContractNumber)
                </td>

            </tr>

        }
    </table>

</div>
<br />
//paging and sorting stuff
    Page @(Model.PORequests.PageCount < Model.PORequests.PageNumber ? 0 : Model.PORequests.PageNumber) of @Model.PORequests.PageCount
    @Html.PagedListPager(Model.PORequests, page => Url.Action("Index", new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))
To summarize:

I don't seem to comprehend how to use the variable ItemDescription to search within the POItems list, and get the PORequest table, which includes the POItem table as a related object, to display the results. My other searches all function as expected.

I have searched, on this site in particular, but any of the similar questions involve the view end of it just displaying each item in the list, which I am able to do, or creating/editing functionality, or, perhaps, they are beyond my understanding of MVC enough where they didn't appear to be a solution.

Here are a few such examples:

MVC Code First: One-to-many relationship between own model and SimpleMembership user

EntityFramework 6. How to get related objects?

https://stackoverflow.com/questions/26372533/add-the-list-of-object-to-parent-object-using-mvc-5-and-ef-6

Any help would be greatly appreciated.
解决方案
Drill down to the items in the search expression:
if (!string.IsNullOrEmpty(ItemDescription))
{
    viewModel.PORequests = viewModel.PORequests.Where(x => x.POItems
        .Any(i => i.Description.Contains(ItemDescription)))
        .ToPagedList(pageNumber, 5);
}


                        
这篇关于MVC 5 EF 6  - 如何使用相关表对象标准搜索表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！