CODEWITHSUNDEEP
c#sqllinqentity-frameworklinq-to-entities
mpakbaz
mpakbaz

Reputation: 496

Entity Framework retrieve data from table with foreign key

I have 3 tables in my SQL Server database Role, Permission and RolePermission.

RolePermission consists of two columns qRole and 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.

Thanks

Upvotes: 12

Views: 54612

Answers (2)

Jonathan Karamavros
Jonathan Karamavros

Reputation: 11

@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;

Upvotes: 0

BlackICE
BlackICE

Reputation: 8916

You are looking for the .Include() statement

http://msdn.microsoft.com/en-us/data/jj574232.aspx

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;

etc.

Upvotes: 40

Related Questions