I have 3 tables in my SQL Server database
RolePermission consists of two columns
qPermission which are the foreign keys for the other two tables.
Therefore when Entity Framework creates the model for it, it just create two classes and add virtual property for
RolePermission into each role and permission classes.
Now I need to select columns from
RolePermission so what I did I wrote this piece of code:
var rolePermission = PREntitiy.Roles.Where(r => r.qRole == TxtRole.Text) .Select(p => p.Permissions);
Doing this I can access
rolePermission table however I need to retrieve some column from role table and some column from
rolePermission in a single query like what we do in a join statement in SQL.
In other words, I need a linq query to access some column from role table and some from
rolePermission but I need to do it in just one linq query like a join statement in SQL.
You are looking for the
var role = PREntitiy.Roles.Include(r=>r.Permission).Where(r => r.qRole == TxtRole.Text)
I don't have all your classes, so that Permission property may be incorrect.
You would access the properties from each class using dot notation like normal:
var x = role.name; var y = role.Permission.Name;
@BlackICE helped me a lot with his suggestion. This isn't exactly the same but I had a similar problem. I have three tables, Users (Username, Password), Roles (RoleId, RoleName), and UserRoles (Username, RoleId). The last was made up of keys from the other two. To get the role, given the username I had to do the following. I don't know if this is proper/improper but it worked for me :).
IQueryable<User> IQUsers = _dbContext.Users.Include(u => u.Roles).Where(u => u.Username == username); User _user = IQUsers.FirstOrDefault<User>(); Role _role = _user.Roles.FirstOrDefault<Role>(); roleName = _role.RoleName;