score:2

Accepted answer

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.

score:1

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

Related Query

More Query from same tag