Get data from two tables(join) with linq and retur

Question

I have two tables: Projects and ProjectsData and I want to execute query with join and get the result in the View.

In the Controller I have this code:

ViewBag.projectsData = (from pd in db.ProjectsData
                                   join p in db.Projects on pd.ProjectId equals p.ID
                                   where pd.UserName == this.HttpContext.User.Identity.Name 
                                   orderby p.Name, p.ProjectNo
                                   select new { ProjectData = pd, Project = p });

What I should use in the View to extract this data. I tried that:

@foreach (var item in ViewBag.projectsData)
{
    @item.pd.UserName
}

but it doesn't work...

Answer 1

In your view you are trying to access a pd property but such property doesn't exist. The property is called ProjectData.

This being said I would strongly recommend you to use view models and strongly typed views instead of ViewBag. This way you will also get Intellisense in your view which would have helped you pick the correct names.

So you could start by defining a view model that will hold all the information your view would need:

public class MyViewModel
{
    public ProjectData ProjectData { get; set; }
    public Project Project { get; set; }
}

and then inside your controller action populate this view model and pass to the view:

public ActionResult Index()
{
    var viewModel = 
        from pd in db.ProjectsData
        join p in db.Projects on pd.ProjectId equals p.ID
        where pd.UserName == this.HttpContext.User.Identity.Name 
        orderby p.Name, p.ProjectNo
        select new MyViewModel { ProjectData = pd, Project = p };
    return View(viewModel);
}

and finally inside your strongly typed view use the view model:

@model IEnumerable<AppName.Models.MyViewModel>
@foreach (var item in Model)
{
     <div>@item.ProjectData.UserName</div>
}