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

Answer 1

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.