MVC4 C# Populating data in a viewmodel from database

Question

I have a viewmodel which needs data from two models person and address:

Models:

public class Person
{
   public int Id { get; set; }
   public string Name { get; set; }
   public int Age { get; set; }
   public int Gender { get; set; }
}

public class Address
{
   public int Id { get; set; }
   public string Street { get; set; }
   public int Zip { get; set; }
   public int PersonId {get; set; }
}

The Viewmodel is as such

public class PersonAddViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Street { get; set; }
}

I have tried several ways to get data into the viewmodel and pass it to the view. There will be multiple records returned to display.

My latest method is populating the view model as such:

private AppContexts db = new AppContexts();
public ActionResult ListPeople()
{
    var model = new PersonAddViewModel();
    var people = db.Persons;
    foreach(Person p in people)
    {
        Address address = db.Addresses.SingleOrDefault(a => a.PersonId == p.Id)
        model.Id = p.Id;
        model.Name = p.Name;
        model.Street = address.Street;
    }
    return View(model.ToList());
}

I get an error on the Address address = db... line of "EntityCommandExecutionException was unhandled by user code.

How can you populate a view model with multiple records and pass to a view?

Final Solution:

private AppContexts db = new AppContexts();
private AppContexts dbt = new AppContexts();
public ActionResult ListPeople()
{
    List<PersonAddViewModel> list = new List<PersonAddViewModel>();
    var people = db.Persons;
    foreach(Person p in people)
    {
        PersonAddViewModel model = new PersonAddViewModel();
        Address address = dbt.Addresses.SingleOrDefault(a => a.PersonId == p.Id)
        model.Id = p.Id;
        model.Name = p.Name;
        model.Street = address.Street;
    }
    return View(list);
}

Answer 1

First, EntityCommandExecutionException errors indicates an error in the definition of your entity context, or the entities themselves. This is throwing an exception because it's found the database to be different from the way you told it that it should be. You need to figure out that problem.

Second, regarding the proper way to do this, the code you've shown should work if your context were correctly configured. But, a better way would be to use Navigational properties, so long as you want to get all related records and not specify other Where clause parameters. A navigational property might look like this:

public class Person
{
   public int Id { get; set; }
   public string Name { get; set; }
   public int Age { get; set; }
   public int Gender { get; set; }

   public virtual Address Address { get; set; }
   // or possibly, if you want more than one address per person
   public virtual ICollection<Address> Addresses { get; set; }
}

public class Address
{
   public int Id { get; set; }
   public string Street { get; set; }
   public int Zip { get; set; }
   public int PersonId { get; set; }

   public virtual Person Person { get; set; }
}

Then you would simply say:

public ActionResult ListPeople()
{
    var model = (from p in db.Persons // .Includes("Addresses") here?
                select new PersonAddViewModel() {
                    Id = p.Id,
                    Name = p.Name,
                    Street = p.Address.Street,
                    // or if collection
                    Street2 = p.Addresses.Select(a => a.Street).FirstOrDefault()
                });

    return View(model.ToList());
}

Answer 2

For displaying lists of objects, you could use a generic view model that has a generic list:

public class GenericViewModel<T>
{
    public List<T> Results { get; set; }

    public GenericViewModel()
    {
        this.Results = new List<T>();
    }
}

Have a controller action that returns, say all people from your database:

[HttpGet]
public ActionResult GetAllPeople(GenericViewModel<People> viewModel)
{
    var query = (from x in db.People select x); // Select all people
    viewModel.Results = query.ToList();

    return View("_MyView", viewModel);
}

Then make your view strongly typed, taking in your generic view model:

@model NameSpace.ViewModels.GenericViewModel<NameSpace.Models.People>