ASP.net MVC displaying results from two tables on one view

Question

Hello

I'm crating a simple MVC app that has People and Notes tables.

Using the repository method and LINQ to SQL to access the data.

Each person has a single note for a day and the note has a CreatedAt. Foreign Key in Note table as PersonID.

I have managed to get CRUD working for both Person and Note independently but I'd like to be able to display the note of a single person for a single day in my view.

In my repository I've created a helper which I think should work, but I'm not sure how I'd use it in my controller.

public IQueryable<Note> FindSinglePersonsNote(int id)
{
    return from Note in db.Notes
           where Note.CreatedAt > DateTime.Now.AddDays(-1)
           && Note.PersonId == id
           select Note;
}

What I'd like to happen is when I click on the person details link, to show the Persons name and the note connected to that person for today.

Any help would be appreciated.

Cheers

Tom

Update:

I think I'm getting close...

My Person controller now looks like this

public ViewResult PersonNote(int personId)
{
    var personNote = new PersonNoteViewModel
    {
        person = scrumRepository.GetPerson(personId),
        note = scrumRepository.GetNote(personId)

    };

    return View(personNote);
}

And I created a new view called PersonNote

And tried to access the values like so:

<%= Model.person.PersonName %>
<%= Model.note.Issues %>

And my repo looks like this:

    public Note GetNote(int id)
    {
        return db.Notes.SingleOrDefault(d => d.PersonId == id);
    }


    public Person GetPerson(int id)
    {
        return db.Persons.SingleOrDefault(d => d.PersonId == id);
    }

But I'm getting a very strange error when I try to view the page.

The parameters dictionary contains a null entry for parameter 'personId' of non-nullable type 'System.Int32' for method 'System.Web.Mvc.ViewResult PersonNote(Int32)' in 'ScrumApp.Controllers.PersonController'. An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter. Parameter name: parameters

Answer 1

Create a ViewModel object that looks something like this:

public class PersonNoteViewModel
{
    public Person person { get; set; }
    public Note note { get; set; }
}

Populate an instance of this object in your controller method using your repository, and then pass this object to your view for display:

public ViewResult PersonNote(int id)
{
    var personNote = new PersonNoteViewModel
    {
        Person = repository.GetPerson(id);
        Note = repository.GetNote(<blah>);
    }

    return View(personNote);
}