How to access Table built by MVC EF automatically -m2m

Question

I'd like to know how to access the Table from the Controller and View I suppose.

My models look like so

public class UserProfiles
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }

    public string UserName { get; set; }

    public virtual ICollection<Roles> Roles { get; set; } //many to many
    public virtual ICollection<dbClient> Clients { get; set; } // many to many

}

My other model looks like

public class Roles
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    [Required(ErrorMessage = "Required")]
    public int RoleID { get; set; }
    [Required(ErrorMessage = "Required")]
    public string RoleName { get; set; }

    public virtual ICollection<UserProfiles> UserProfiles { get; set; } //many to many

}

When i run migrations and update from the PMC ( Package Manager Console ), It brings the creates the 2 tables with Keys to a 3rd table that hold jsut the ID values of both Tables.

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OilNGasWeb.Models.Roles>()
            .HasMany<OilNGasWeb.Models.UserProfiles>(r => r.UserProfiles)
            .WithMany(u => u.Roles)
            .Map(m =>
            {
                m.ToTable("Webpages_UsersInRoles");
                m.MapLeftKey("RoleId");
                m.MapRightKey("UserId");
            });
    }

enter image description here

Recently I have tried:

var result=(from u in db.UserProfiles 
                from r in u.Roles  **1.**    
                where r.RoleID.Contains(u.UserId) **2.** 
                select u);

But it is somehow not picking up on Webpages_UsersInRoles... ( after it creates the table for me must I create a class for it???? )

Why can't I just link into it.

2Pics for @Andrew Counts

enter image description here

Steps i followed to get m2m tables

Generating the Many to Many EF Code First

Afterwards I tried to use with examples from :

Getting m2m work with view

m2m

m2m controller updating

All those and more I read through, but non say how to use a m2m from the controller level? to pass values into the View.

Thanks for any help

score 1 · Answer 1 · answered Aug 01 '13 at 18:35

They weren't "magically" made for you; users and roles are ASP.NET Membership tables. They're designed to be manage through ASP.NET Membership, not your application. However, that doesn't mean your application can't manage them. At a base level, they're just tables in a database, so you can simply follow EF's Database First design to interact with them. All that really entails is manually specifying the table to use on your models and turning off EF's database initializer on the context that has those models.

First, you create classes to approximate the membership tables. You can have Visual Studio generate these for you, but you'll end up with .edmx files which don't really mix well if the rest of your application is Code First. It's not difficult to create them yourself. Just look at the database table and create properties on your class of the same types with the same names.

Once you have your class, you'll need to decorate it with the [Table] attribute. For example on your Membership class:

[Table("webpages_Membership")]
public class Membership
{
    ...
}

Then, you'll need to create a new context. You can call it whatever you want, but it will need to look approximate like this:

public class MembershipContext : DbContext
{
    public MembershipContext()
        : base("name=YourConnectionStringName")
    {
        Database.SetInitializer<MembershipContext>(null);
    }

    public DbSet<Membership> Memberships { get; set; }
    ...
}

The custom constructor does two things: 1) it passes the connection string name to use to connect to the right database and 2) it turns off EF's database intialization so you won't get prompted to do migrations and it won't try to do something crazy like drop and try to recreate your database. This is why it needs to be a different context than the one your code first models are using.

That's really all there is to it. Now, you just create an instance of this context in your controller and use it like you would use any other context.

So when i deleted the model I created because the website stopped working ( made a boo-boo ). And re-ran the migration and update, the tables should have been re-created in my database. But they werent. Therefore I created my own tables and found out how to make them Keyed the way they were before i delted the db. Now the question is just how the EF uses the middle table to access between the 2 tables im really accessing in codefirst? - in example when i look at users i know that that user is admin, i don't have to look at usersID match it in the AutoCreated Table and match it to the RoleID... — Don Thomas Boyle, Aug 01 '13 at 18:43
Also have a look at EF Power Tools Beta 3 - They have a code first reverse engineer function that handles mappings for you. http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d. It's actually very handy. — Jack, Aug 01 '13 at 19:01
@Chris Pratt all that asside i took a look into what you asked me to look into and its not getting me anywhere, studied it for several days now and understand it well. The issue still persists that i cannot access the "Created" Tables from within my Controller ? Any more ideas? — Don Thomas Boyle, Aug 08 '13 at 15:34

score 1 · Accepted Answer · answered Aug 01 '13 at 19:14

1

the .Include() extension method is what you are missing. Julie Lerman wrote a great post on MSDN which discusses loading related data, the .Include() extension method, the virtual keyword, and more.

If I were to re-write your query, I would probably start with

var model = from u in db.UserProfiles
    .Include("Roles")
    .Where(u.Roles.Where(r => r.RoleID != 1 && r.RoleID != null))
    .Select u;

something along those lines should return the results you expect.

answered Aug 01 '13 at 19:14

Claies

22,124
4
53
77

You were right on the money, I had some issues with my tables though and had to adjust your query a bit to match something like http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/updating-related-data-with-the-entity-framework-in-an-asp-net-mvc-application All i have to do now is read and grasp this and implement, givin this works ill up the Answer to Answered. – Don Thomas Boyle Aug 08 '13 at 16:05

How to access Table built by MVC EF automatically -m2m

2 Answers2