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

What is db in this case? And what is the message of the exception? Are you using Entity Framework or LinqToSql? Whatever db is seems to be having trouble executing the command to retrieve the data but without more info, it could be anything. — Brian S
– Brian S, Commented Apr 17, 2013 at 23:03
@sylon I am not exactly sure what you mean by navigation properties? I looked it up and found it would be something like this in my case public virtual Person Person in my Address model? — Xaxum
– Xaxum, Commented Apr 18, 2013 at 0:39

vapcguy · Accepted Answer · 2015-02-27 07:06:10Z

6

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

edited Feb 27, 2015 at 7:06

vapcguy

1

answered Apr 18, 2013 at 5:50

Erik Funkenbusch

93.7k29 gold badges201 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vapcguy Over a year ago

Note: If setting this up to use a collection, using .FirstOrDefault() will give you only the first address in the collection - even though all of them will be in the collection. But since the OP's code had .SingleOrDefault(), I suppose this was already accounted for. .FirstOrDefault() is also better than .SingleOrDefault(), since "single" is more for when you know you will only get one response.

vapcguy Over a year ago

Sorry one more note: To get this to work for me, I had to add .Include("Addresses") at the end of db.Persons before the collection would fill in. I edited the post to include a comment on this and correction of many syntax errors.

MattSull · Accepted Answer · 2013-04-17 23:05:17Z

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>

answered Apr 17, 2013 at 23:05

MattSull

5,5645 gold badges48 silver badges68 bronze badges

5 Comments

Xaxum Over a year ago

Thanks for the response. I am not sure how this would get the data from the address model data as well? I can get the people or the address without an issue it is just when I try to use a viewmodel to combine them into one model the issue aries.

MattSull Over a year ago

You should create a relationship between Person and Address, it'll depend on the scope of your app, but for simplicity let's say it's a 1:1 i.e. one Person has one Address. Then you'll be able to access a person's address like person.Address.City etc. in your view. Here's a link on to Code First associations weblogs.asp.net/manavi/archive/2011/01/23/…

Xaxum Over a year ago

Is this what @sylon was mentioning above as navigation properties? I have a one to many relationship, one person can have many addresses. The foreign keys were set up by entity framework but maybe without the virtual I can't reference like person.Adddress.City.

MattSull Over a year ago

Yes you need to specify the navigation properties as described in @sylons answer.

Xaxum Over a year ago

Thanks for the option. This helped get to where I had to be.

Collectives™ on Stack Overflow

MVC4 C# Populating data in a viewmodel from database

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related