DB First, MVC4, Entity Framework 5 – best practice to display values from multiple tables?

Question

First of all, I´m new to DB First, MVC4, Entity Framework 5 AND especially LINQ.

Let´s assume that we have a database with the following tables:

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)

Now we want to create a ProjectNumberResultView where we can search for a ProjectNumber. The result should show every ProjectNumber which contains the given searchString and some additional data from the other tables.

My solution is to create a new ViewModel class:

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

And I wrote this method for my 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);
}

I hate this solution, because I have to use two foreach iterations…I definitely would prefer a join solution, but I could not work it out.

I´ve tried something like this:

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(??)

My Questions are:

1. What´s the right (LINQ??) select syntax?
2. What should I have to return since it´s a joined object? Or what do I have to add to my ProjectNumberResultView.cshtml File?
3. Is there any better approach?

Answer 1

You could just project the LINQ query to an 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();

Your view of course will be strongly typed to the view model:

@model IEnumerable<ProjectNumberSearchResult>

Answer 2

How about an 'all in one'?:

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();

return MyViewModel (a custom object holding only properties that the view requires) to the view.

Answer 3

i worked this one out, because i asked for a solution, that also shows the project numbers which have no units (left join):

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);
}