How to query a complex result set from EntityFramework and display the result set in MVC.

Question

I am working in MVC3, C# and using Razor. I don't work with MVC on a consistent basis so I am constantly having to relearn things... I want a view that displays a list of schools (from a specified state) with a nested list of the students (from that school) that have the middle name 'Bob'.

Seems simple enough but so far I am not able to find a solution.

Ex: VIEW

Results for Maryland

My First School: - Billy Bob Johnson - Sally Bob Simpson - Adam Bob Jones

Another School: - Arnold Bob Smith - Cathy Bob Powers - Jenny Bob Smith

The Other School: - Barney Bob Edson - Sue Bob Purdee - Alan Bob Warfield

public class School
{ 
   public int SchoolId {get; set;} 
   public string Schoolname {get; set;} 
   public int StateId {get; set;} 
} 


public class Student
{ 
   public int StudentId {get; set;}
   public int SchoolId {get; set;} 
   public string Firstname {get; set;} 
   public string Middlename {get; set;} 
   public string Lastname {get; set;} 
} 


public class SchoolViewModel
{ 
   public int SchoolId {get; set;}
   public string Schoolname {get; set;}  
   public IEnumerable<Student> Bobs { get; set; }
} 

Getting a list by doing a SELECT with JOINS will give me a data set that needs additional work in order to display in the VIEW as illustrated above. I am thinking that there is a way to grab the nested list of students in a way that allows the data set to act like a mulitidimensional array. But I am not finding examples of that approach - leading me to think that THAT is not the way... ?? How do I grab the data in a way that makes the best use LINQ / MVC / ViewModel etc... ? :)

I was trying something like this - but don't know if this is the right direction or not - and it gave me an error when I tried to set the Bobs. :)

I get an error like: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[stuff] GetBobs(stuff)' method, and this method cannot be translated into a store expression.

  private IQueryable<SchoolViewModel> GetSchools(int StateId)
  {
        var query = from s in db.Schools
                    where s.State == StateId
                    orderby s.Schoolname
                    select new SchoolViewModel
                    {
            SchoolId = s.SchoolId,
                        Name = s.Name,
                        Bobs = GetBobs(s.SchoolId)
                    };
    var results = query;
        return results;
    }

    private IQueryable<Student> GetBobs(int id)
    {

        var query = from s in db.Students
                    where s.Middlename == 'Bob'
                    where s.SchoolId == id
                    select s;

        var results = query;
        return results;
    }

Answer 1

Well you can always do this for the query portion, I'm guessing that's where you are getting tripped up:

var schools = db.Schools
     .Where(x => x.State == "<Whatever>")
     .Select(x => new {
          School = x,
          Bobs = x.Where(y => y.MiddleName == "Bob")
     }).ToList();

List<Schools> schoolsView = new List<Schools>();
foreach(var x in schools)
{
     schoolsView.Add(new SchoolsViewModel(){
          //Set Properties here
          SchooldID = x.School.ID,
          SchoolName = x.School.Name,
          Students = x.Bobs.ToList() //You can project here if needed.
     };
}

return schoolsView;

Then for your view you can do this any number of ways but if you have the concrete ViewModel with the fixed output it's just nested loops:

<ul>
@foreach(var m in Model)  //Assuming your model is a list of SchoolViewModels
{
     <li>@m.SchoolName
          <ul>
               @foreach(var s in m.Students)
               {
                    <li>@s.Name</li>
               }
          </ul>
     </li>
}
</ul>

You also have cute options for doing things like String.Join(",", Model.Students) to output the list but this is up to you.

Edit: Additional Clarification

You will also want to change your models a tad, I'm not sure what EF version you are using but in EF4.1+ you can specify your navigation properties to remove the need for an explicit join condition (which is the right approach in the end).

http://msdn.microsoft.com/en-us/library/bb738520.aspx

So your Schools model would become:

public class School
{ 
   public int SchoolId {get; set;} 
   public string Schoolname {get; set;} 
   public int StateId {get; set;} 
   public virtual IList<Student> Students {get; set; }
} 

And then in your DB configuration (If you are using fluent the configuration would be:

modelBuilder.Entity<School>()
     .HasMany(x => x.Students).WithRequired();

Then you gain the power of doing

db.Schools
    .Where(x => x.Name.Contains("Some Value"))
    .Include(x => x.Schools.Where(x => x.MiddleName.Contains("SomeValue")))
    .ToList();

Which even makes my prior query even more simple without needing to use ambiguous type definitions.

Hopefully this clarification helps a bit further.

Answer 2

You could try something like this to display the data.

Top level view:

@model SchoolViewModel
@using (Html.BeginForm()) 
{
    <fieldset>
        @Html.EditorFor(x => x.Bobs)
        <input type="submit" value="Save" />
    </fieldset>
}

You could then use editor templates for display purposes. Create them at the following URL: ~/Views/Shared/EditorTemplates/Student.cshtml

@model Student
@Html.DisplayFor(x => x.StudentID)
@Html.DisplayFor(x => x.SchoolID)
@Html.DisplayFor(x => x.FirstName)
@Html.DisplayFor(x => x.MiddleName)
@Html.DisplayFor(x => x.LastName)

You can then put any sort of formatting you'd like on the sublist generated. Each Student in the Bobs IEnumerable will render according to the rules you define in the editor template.

As far as actually querying the database goes, I would create some sort of repository along this pattern:

public interface IRepository<TEntity> where TEntity : class
{
    List<TEntity> FetchAll();
    IQueryable<TEntity> Query { get; }
    void Add(TEntity entity);
    void Delete(TEntity entity);
    void Save();
}  

A concrete implementation of which would look like:

public class SQLRepository<T> : IRepository<T> where T : class
{
    private DataContext db;
    public SQLRepository()
    {
        this.db = new TestDataContext();
    }
    public void Add(T entity)
    {
        db.GetTable(Of T)().InsertOnSubmit(entity)
    }
    public void Delete(T entity)
    {
        db.GetTable(Of T)().DeleteOnSubmit(entity)
    }
    public System.Collections.Generic.List<T> FetchAll()
    {
        return Query.ToList();
    }
    public System.Linq.IQueryable<T> Query {
        get { return db.GetTable<T>(); }
    }
    public void Save()
    {
        db.SubmitChanges()
    }
}