Get data from two tables(join) with linq and return result into view

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...

score 45 · Accepted Answer · answered Mar 05 '11 at 23:11

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

Thanks. This helped me for view model also. So that I can show two table data in a partial view and display that view in another database table View. — Muhammad Ashikuzzaman, Aug 25 '15 at 09:17
I did in the same way, but I received an exception: [InvalidOperationException: The model item passed into the dictionary is of type 'System.Linq.Enumerable+d__37`4[<>f__AnonymousType7`2[Auction,Winner],Auctioneer,Int32,<>f__AnonymousType8`5[String,DateTime,Decimal,String,String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[TeAwaOnlineArtworkAuction.Entities.AuctionWinner]'.] — VincentZHANG, Sep 27 '16 at 09:24
It seems that there is no easy way, we cannot avoid coding the transformation manually, take a look at this post: http://stackoverflow.com/questions/31160774/linq-join-query-how-to-display-new-table-in-view-mvc-c-sharp — VincentZHANG, Sep 27 '16 at 23:20

Get data from two tables(join) with linq and return result into view

1 Answers1

Linked