Displaying data from three table with ASP.Net MVC

Question

I've 3 tables and 3 models. Adding control to control folder. Selecting webpages_UsersInRoles and UserContext for context. I need display UserId (Int) UserName (String) and UserRoles (String). Cant get UserName anyway?

//my user context and models
enter code here
public class UsersContext : DbContext
{
    public UsersContext()
        : base("DefaultConnection")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }
    public DbSet<webpages_Roles> RolesList { get; set; }
    public DbSet<webpages_UsersInRoles> UsersInRole { get; set; }

}



[Table("UserProfile")]
public class UserProfile
{
    [Key]
    public int UserId { get; set; }
    [Required(ErrorMessage = "Department name is required.")]
    [MaxLength(50)]
    public string UserName { get; set; }

}

[Table("webpages_Roles")]
public class webpages_Roles
{
    [Key]
    public int RoleId { get; set; }
    public string RoleName { get; set; }

    public virtual ICollection<webpages_UsersInRoles> Roles { get; set; }

}

[Table("webpages_UsersInRoles")]
public class webpages_UsersInRoles
{
    [Key]
    [Column(Order = 0)]
    public int UserId { get; set; }
   // [Key]
    [Column(Order = 1)]
    public int RoleId { get; set; }


    public virtual ICollection<webpages_Roles> Roles { get; set; }
   // public virtual ICollection<UserProfile> User { get; set; }

}
///end my controller for index

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace DizelgeMVC.Models
{
public class Default1Controller : Controller
{
    private UsersContext db = new UsersContext();

    //
    // GET: /Default1/

    public ActionResult Index()
    {
        return View(db.UsersInRole.ToList());
    }

    //
    // GET: /Default1/Details/5

    public ActionResult Details(int id = 0)
    {
        webpages_UsersInRoles webpages_usersinroles = db.UsersInRole.Find(id);
        if (webpages_usersinroles == null)
        {
            return HttpNotFound();
        }
        return View(webpages_usersinroles);
    }

    //
    // GET: /Default1/Create

    public ActionResult Create()
    {
        return View();
    }

    //
    // POST: /Default1/Create

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create(webpages_UsersInRoles webpages_usersinroles)
    {
        if (ModelState.IsValid)
        {
            db.UsersInRole.Add(webpages_usersinroles);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        return View(webpages_usersinroles);
    }

    //
    // GET: /Default1/Edit/5

    public ActionResult Edit(int id = 0)
    {
        webpages_UsersInRoles webpages_usersinroles = db.UsersInRole.Find(id);
        if (webpages_usersinroles == null)
        {
            return HttpNotFound();
        }
        return View(webpages_usersinroles);
    }

    //
    // POST: /Default1/Edit/5

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit(webpages_UsersInRoles webpages_usersinroles)
    {
        if (ModelState.IsValid)
        {
            db.Entry(webpages_usersinroles).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index");
        }
        return View(webpages_usersinroles);
    }

    //
    // GET: /Default1/Delete/5

    public ActionResult Delete(int id = 0)
    {
        webpages_UsersInRoles webpages_usersinroles = db.UsersInRole.Find(id);
        if (webpages_usersinroles == null)
        {
            return HttpNotFound();
        }
        return View(webpages_usersinroles);
    }

    //
    // POST: /Default1/Delete/5

    [HttpPost, ActionName("Delete")]
    [ValidateAntiForgeryToken]
    public ActionResult DeleteConfirmed(int id)
    {
        webpages_UsersInRoles webpages_usersinroles = db.UsersInRole.Find(id);
        db.UsersInRole.Remove(webpages_usersinroles);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    protected override void Dispose(bool disposing)
    {
        db.Dispose();
        base.Dispose(disposing);
    }
}
}

My View

@model IEnumerable<DizelgeMVC.Models.webpages_UsersInRoles>
@{

var db = new 
}

}
@{
ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
    <th>
        @Html.DisplayNameFor(model => model.RoleId)
    </th>
    <th></th>
</tr>

@foreach (var item in Model) {
<tr>
    <td>
        @Html.DisplayFor(modelItem => item.RoleId)
    </td>
    <td>
        @Html.ActionLink("Edit", "Edit", new { id=item.UserId }) |
        @Html.ActionLink("Details", "Details", new { id=item.UserId }) |
        @Html.ActionLink("Delete", "Delete", new { id=item.UserId })
    </td>
</tr>
}

</table>

Answer 1

Notice the model that you're passing to your view:

@model IEnumerable<DizelgeMVC.Models.webpages_UsersInRoles>

This is simply a list of webpages_UsersInRoles objects, each of which only has these properties:

public int UserId { get; set; }
public int RoleId { get; set; }
public virtual ICollection<webpages_Roles> Roles { get; set; }

But in your view you need more than this information. Specifically, you also want to show the UserName property on a different model. To do this, you have a few options. The two most immediate ones are:

• Create a composite model for the view which contains all of the properties you need and build that composite model from multiple models in the controller.
• Add a property to your existing webpages_UsersInRoles model which fetches the value(s) you need.

For simplicity, the second option should probably be your first attempt. In the context of any given webpages_UsersInRoles object you have a UserId, so you can use that to fetch more information about that user. A simple property like this might do the trick:

public User User
{
    get
    {
        return new UsersContext().UserProfiles.Single(u => u.UserId == this.UserId);
    }
}

Or if it's going to be accessed many times, you can locally cache the UserProfile object, something like this:

private User _user;
public User User
{
    get
    {
        if (_user == null)
            _user = new UsersContext().UserProfiles.Single(u => u.UserId == this.UserId);
        return _user;
    }
}

This will lazy-load the User the first time you need to access that property, and keep it in memory in case you need to access it again. Then on your model you can access the UserName property through this new property:

@foreach (var item in Model)
{
    item.User.UserName
}

A few things to note here:

1. Technically this isn't a great design because it couples the model with the database context. Though they're already coupled since your models are directly generated from your database. As you learn more about the patterns involved here, you'll want to try to de-couple your database from everything else. But that's another concern entirely and not immediately relevant to the question.
2. There's a saying about MVC, "Keep you models heavy and your controllers light." This approach puts the logic where it belongs, on the model instead of the controller. The models (whether generated from the database like this or custom-written) should internally contain all of the logic and references needed to do what they need to do.
3. I notice you were trying to instantiate a data context inside the view. This is an approach you don't want to continue to try. There shouldn't be any non-UI logic in the view. Any time you have to declare a variable in the view (use the var keyword, basically) that's a sign that something's wrong. The model should contain everything the view needs, and the view should just access what's on the model. If there's new data that the view needs, put it on the model.

Answer 2

Thank you for reply!

Based on your suggestion

Add a property to your existing webpages_UsersInRoles model which fetches the value(s) you need. For simplicity, the second option should probably be your first attempt. In the context of any given webpages_UsersInRoles object you have a UserId, so you can use that to fetch more information about that user.

A simple property like this might do the trick:

   //Added this codes to my webpages_UserInRoles
   public User User
    {
        get
        {
            return new UsersContext().UserProfiles.Single(u => u.UserId == this.UserId);
        }
    }

Get this:

'DizelgeMVC.Models.webpages_UsersInRoles.User' is a 'property'
 but is used like a 'type'  

When I input a class like public class User than your advice did work that add top of the page using System.Linq; but after this class doesn't know UserId