Get a grouped list with relational data

Question

I am new to C# and am leaning ASP.NET MVC and Entity Framework and I am trying to create an application where a user can manager Job Postings and Applicants where an Applicant can apply for many Jobs.

I am trying to pull a list of Applicants, along with a list of the Jobs they have applied for.

The following is the solution I have come up with.

HomeViewModel.cs

public class HomeViewModel
{
 public IEnumerable<ApplicantList> Applicants { get; set; }
 public IEnumerable<Jobs> Jobs { get; set; }
}
public class ApplicantList
{
 public Applicants Applicant { get; set; }
 public List<Jobs> Jobs { get; set; }
}

HomeController.cs

using(var context = new HR())
{
 HomeViewModel viewModel = new HomeViewModel();
 // Get grouped applicants list by Email Address
 var applicants = context.Applicants.GroupBy(a => a.EmailAddress)
 .Select(grp=>grp.FirstOrDefault())
 .ToList() ;
 List<ApplicantList> applicantList = new List<ApplicantList>();
 foreach (var a in applicants)
 {
 //get Jobs for each applicants
 var query = from dba in context.Applicants.Include("Jobs")
 where a.EmailAddress == dba.EmailAddress
 select dba.Jobs;
 List<Jobs> jobs = query.ToList();
 applicantList.Add(
 new ApplicantList (){ Applicant = a, Jobs = jobs }
 );
 }
 viewModel.Applicants = applicantList;
 viewModel.Jobs = context.Jobs.ToList();
 return View(viewModel);
}

HomeView.cshtml

@foreach (var applicant in Model.Applicants)
{
 Applicant name: @applicant.Applicant.FirstName <br>
 @foreach(var job in applicant.Jobs)
 {
 * @job.JobTitle <br>
 }
}

Result in View

• Applicant name: Bob Lois
  • Job Title 1
  • Job Title 2
• Applicant name: Kevin Bacon
  • Job Title 1

It works, but it's horrible code produced by a beginner like me.I want to know how I can achieve the same result in a much more eloquent way.

Answer 1

I would recommend moving the database access stuff from your "View Controller" and into more of a data access controller (think DAL).

Also, it's been a bit since I've used Entity Framework, but if I remember right, you can take the following code:

var applicants = context.Applicants.GroupBy(a => a.EmailAddress)
 .Select(grp=>grp.FirstOrDefault())
 .ToList() ;
List<ApplicantList> applicantList = new List<ApplicantList>();
foreach (var a in applicants)
{
 //get Jobs for each applicants
 var query = from dba in context.Applicants.Include("Jobs")
 where a.EmailAddress == dba.EmailAddress
 select dba.Jobs;
 List<Jobs> jobs = query.ToList();
 applicantList.Add(new ApplicantList (){ Applicant = a, Jobs = jobs });
}
viewModel.Applicants = applicantList;
viewModel.Jobs = context.Jobs.ToList();

And replace it with:

var applicants = context.Applicants.Include("Jobs").ToList();

If Entity Framework is set up right, it should pull in the Jobs that are tied to each applicant automatically. It is highly recommended to create DTOs based off of your Entity Framework entities though, so you can cut off any circular reference pitfalls (in this case, make sure that the Jobs object doesn't reference the Applicants object).

For the most part though, this doesn't look as bad as a lot of the code that I have run into that has made it into actual production environments.