CODEWITHSUNDEEP
c#mysqlasp.net.netlinq
kkagill
kkagill

Reputation: 35

Asp.Net LINQ to combine three tables

I want to show in my view three columns:

======================================
UserName      | Email    | Role Name
======================================    
codelover12     [email protected]    Student    
kimmy45634      [email protected]    Teacher    
======================================

so initially what I was thinking is that

I can join Id in AspNetUsers table with UserId in AspNetUserRoles table, and also join Id in AspNetRoles so I can figure out who has which role.

I was trying like this: 

var model = from users in db.Users
            join userRoles in db.AspNetUserRoles on users.Id equals roles.UserId
            join role in db.Roles on role.Id equals userRoles.RoleId 
            select new { users.UserName, users.Email, role.Name };

but figured out that I cannot access AspNetUserRoles. I'd appreciate your help.

Upvotes: 0

Views: 219

Answers (2)

kkagill
kkagill

Reputation: 35

Well, I figured it out.

Easiest way to do it was adding this line of code to IdentityModels.cs

public DbSet<IdentityUserRole> UserRoles { get; set; }

then you can now have access to AspNetUserRoles, and write codes like

var findRole = (from userRole in db.UserRoles
                join role in db.Roles on
                userRole.RoleId equals role.Id
                where userRole.UserId == Id
                select role.Name).ToList();

Hope this can help others who have similar issues.

Upvotes: 1

jarres
jarres

Reputation: 71

EF create cross table AspNetUserRoles automatically. So, you can use navigation properties or do smth like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{    
    modelBuilder.Entity<AspNetUser>()
        .HasMany(x => x.Roles)
        .WithMany(x => x.AspNetUsers)
    .Map(x =>
    {
        x.ToTable("AspNetUserRoles"); // third table is named Cookbooks
        x.MapLeftKey("AspNetUserId");
        x.MapRightKey("RoleId");
    });
}

Upvotes: 0

Related Questions