Identity 2.0 Get Rolename and UserName by Linq
asp.net-identity asp.net-mvc-5 c# entity-framework-6 linq
Question
I have Uploaded two images.
- Image showing my edmx in visual studio 2013.
- Image showing my tables in ssms.
I just wanted to show RoleName(Colmun [Name] in AspNetRoles Table) with UserName(Colmun [UserName] in AspNetUsers Table) in simple table (html) View i.e user with all respective roles.How can i do this using LINQ?
Update 1: I do try like following but it didn't work
var result = from tb1 in context.Roles
join tb2 in context.Users on tb1.Id equals tb2.Id
select new { tb1.Name, tb2.UserName };
but its wrong actually i want Userid of all the user in Role table in place of tb1.Id[in Above LINQ Code] to match with Userid in User table [tb2.Id] .
When i try to access Userid through Roles table which is coming like Context.Roles.Users coz Users here is a foreign Key for Roles Table.
Accepted Answer
Try this for the above example
var result = from tb1 in context.Users
from tb2 in tb1.Roles
join tb3 in context.Roles on tb2.RoleId equals tb3.Id
orderby tb1.UserName, tb3.Name /*optional*/
select new { tb3.Name, tb1.UserName }
But to meet your requiremets:
var result = from tb1 in context.Users
from tb2 in tb1.Roles
join tb3 in context.Roles on tb2.RoleId equals tb3.Id
where tb3.Name == "Administrator"
select tb1.Id
Popular Answer
Maitain [AspNetRoles.[Id] column as primary key and [AspNetUsers].[Id] column as foriegn key.Lets write query like this
var result = (from R in context.AspNetRoles
join U in context.AspNetUsers
ON R.Id equals U.Id
select new{
RoleName=R.Name,
UserName=U.UserName});
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
