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

Accepted Answer

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.

Entity Framework retrieve data from table with foreign key

Solution

Problem