DB First、MVC4、Entity Framework 5–显示多个表中的值的最佳实践

本文关键字：最佳 显示 Entity MVC4 First Framework DB | 更新日期: 2023-09-27 18:29:25

首先，我是DB First、MVC4、Entity Framework 5，尤其是LINQ的新手。

让我们假设我们有一个包含以下表格的数据库：

T_Project
-   string ProjectNumber (PK)
T_NetworkIpAddress
-   string IP (PK)
-   …some more attributes…
T_Unit
-   Int UnitId (PK)
-   string ProjectNumber (FK)
-   string IpAddress (FK)
-   … some more attributes…
T_ProjectAndIpIntersection
-   string ProjectNumber (PK,FK)
-   string IpAddress (PK,FK)

现在我们想要创建一个ProjectNumberResultView，在这里我们可以搜索ProjectNumber。结果应该显示每个包含给定searchString和来自其他表的一些附加数据的ProjectNumber。

我的解决方案是创建一个新的ViewModel类：

public class ProjectNumberSearchResult
{
   public ProjectNumberSearchResult () {}
   public string ProjectNumber { get; set; }
   public string UnitId { get; set; }
   public string IpAddress { get; set; }
}

我为我的SearchController:写了这个方法

public ActionResult ProjectNumberResultView(string searchString)
{
  ViewBag.InputSearchString = searchString;
  List<ProjectNumberSearchResult> results = new List<ProjectNumberSearchResult<>();
  if (!String.IsNullOrEmpty(searchString))
  {
  searchString = searchString.Trim();
  //find projects
  var projects = from p in db.T_ProjectNumber
                 select p;
  projects = projects.Where(s => s.ProjectNumber.Contains(searchString));
  var units = from u in db.T_Unit
              select u;
  foreach (var p in projects.ToList())
  {
   var pUnits = units.Where(u => u.ProjectNumber.Equals(p.ProjectNumber));
   //add only project number when there are no unit entries
   if (pUnits.Count() == 0)
    results.Add(new ProjectNumberSearchResult () { ProjectNumber = p.ProjectNumber });
   //else add object with additional unit data for every unit entry
   foreach (var unit in pUnits)
    {
     var result = new ProjectNumberSearchResult();
     result.ProjectNumber = p.ProjectNumber;
     result.UnitId = unit.UnitId;
     result.IpAddress = unit.IpAddress;
     results.Add(result);
     }
   }
 }
return View(results);
}

我讨厌这个解决方案，因为我必须使用两次foreach迭代……我当然更喜欢联接解决方案，但我无法解决。

我试过这样的东西：

var projects = from p in db.T_ProjectNumber
                               select p;
projects = projects.Where(s => s.ProjectNumber.Contains(searchString));
var units = from u in db.T_Unit
         select u;
//find all projects with or without (there are project number without units) units incl. their additional data
var results = projects.Join(units, 
            pNumber => pNumber.ProjectNumber, 
            unitPNumber => unitPNumber.ProjectNumber,
             (pNumber, unitPNumber) => new { T_ProjectNumber = pNumber,
                                  T_Unit = unitPNumber })//.Select(??)
//return View(??)

我的问题是：

  1. 什么是正确的（LINQ？？）选择语法
  2. 既然它是一个联接对象，我应该返回什么？或者，我必须向ProjectNumberResultView.cshtml文件中添加什么
  3. 有什么更好的方法吗

DB First、MVC4、Entity Framework 5–显示多个表中的值的最佳实践

一个"一体机"怎么样？：

List<MyViewModel> projects =
               (from p in db.T_ProjectNumber
               join u in db.unit on p.ProjectNumber equals u.ProjectNumber
               where p.ProjectNumber.Contains(searchString)
               select new MyViewModel()
                {
                    MyViewModelProperty1 = p.ProjectNumber,
                    MyViewModelProperty2 = u.Stuff
                    // etc, etc
                }).ToList();

将MyViewModel（仅包含视图所需属性的自定义对象）返回到视图。

您可以将LINQ查询投影到IEnumerable<ProjectNumberSearchResult>:

var results = projects
    .Join(
        units,
        project => project.ProjectNumber,
        unit => unit.ProjectNumber,
        (project, unit) => new ProjectNumberSearchResult
        {
            ProjectNumber = project.ProjectNumber,
            UnitId = unit.UnitId,
            // you can populate any other properties from
            // the view model that you need
        })
    .ToList();

当然，您的视图将被强类型化为视图模型：

@model IEnumerable<ProjectNumberSearchResult>

我解决了这个问题，因为我要求一个解决方案，它还显示了没有单元的项目编号（左联接）：

public ActionResult ProjectNumberResultView(string searchString)
{
    ViewBag.InputSearchString = searchString;
    IEnumerable<ProjectNumberSearchResult> results = new List<ProjectNumberSearchResult>();
    if (!String.IsNullOrEmpty(searchString))
    {
        searchString= searchString.Trim();
        results = (from p in db.T_ProjectNumber
                   from unit in db.T_Unit
                  .Where(unit => unit.ProjectNumber == p.ProjectNumber)
                  .DefaultIfEmpty()
                   where p.ProjectNumber.Contains(searchString)
                   select new ProjectNumberSearchResult
                   {
                       ProjectNumber = p.ProjectNumber,
                       UnitId = unit.UnitId,
                       IpAddress = unit.IpAddress
                   }
        );
    }
    return View(results);
}
相关文章：