Entity Framework retrieve data from table with foreign key

Question

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

BlackICE · Accepted Answer · 2014-02-04 16:47:11Z

41

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.

answered Feb 4, 2014 at 16:47

BlackICE

8,9563 gold badges56 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mpakbaz Over a year ago

Thanks, it really helped, just to mention you need to note that Include is an extension method in the System.Data.Entity namespace so make sure you are using that namespace.

Jonathan Karamavros · Accepted Answer · 2018-11-18 03:46:57Z

0

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

answered Nov 18, 2018 at 3:46

Jonathan Karamavros

111 silver badge5 bronze badges

Collectives™ on Stack Overflow

Entity Framework retrieve data from table with foreign key

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related