Entity Framework retrieve data from table with foreign key

c# entity-framework linq linq-to-entities sql


In my SQL Server database, there are 3 tables.Role , Permission and RolePermission .

RolePermission features two columns.qRole and qPermission which serve as the other two tables' foreign keys.

Due to this, Entity Framework just needs to create two classes and add virtual properties when creating the model for it.RolePermission into each position and type of permissions.

I must now choose columns fromRolePermission Consequently, I wrote the following code:

var rolePermission = PREntitiy.Roles.Where(r => r.qRole == TxtRole.Text)
                                    .Select(p => p.Permissions);

By doing this, I have access torolePermission table, but I must obtain a few columns from the role table and a few columns from therolePermission similar to how we would write a SQL join statement in a single query.

Alternatively, I require a Linq query to obtain some columns from the Role table and some fromrolePermission However, I must accomplish it in a single Linq query, much like a SQL join statement.


2/4/2014 5:09:24 PM

Accepted Answer

2/4/2014 4:47:11 PM

Popular Answer

@BlackICE His advice was quite helpful to me. Although not the same, I experienced a similar issue. I have three tables: UserRoles, Roles (RoleId, RoleName), and Users (Username, Password) (Username, RoleId). Keys from the first two were combined to create the last. Given the username, I had to complete the following in order to receive the role. I'm not sure if this is right or wrong, but it seemed to work 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;

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow