Entity framework linq to entities

0
votes

Previously when I wanted obtain related data in an sql query I would join tables, however now in linq to entities I want to get data from a table that is related to the table through another table. I don't know how to perform this sort of query in linq to entities. If someone could help that would be good.

The example is a table named person which has a relationship to table users which is related to table roles. I want to be able to obtain a person that has a particular role. Since person is only related to user and indirectly through user to role, I'm not sure of the query. Also using navigation properties doesn't get me all the way there either.

Any information would be good. Here is an example of the database structure:

db structure http://img194.imageshack.us/img194/4540/persons.jpg

2
entity-frameworklinq-to-entities

2 Answers

2
votes

If you using the generator in VS (ie, drap drop the data table and diagram and keys are all set in db), then the thing you are asking could be already there automatically.

e.g.

from Person in Context.Persons
where Person.Name == "PETER PAN"
select Person.User.Role.RoleName;

Exact name need to refer to the code generator, but this is the idea. Linq to entities will help to map foreign keys and those for you.

Edit

Actually I haven't tried using include. But according to msdn:include method, the include should show the object hierarchy to work. So, for your query to work, try:

from c in db.Persons.Include("aspnet_Users").Include("aspnet_Roles")
    where c.aspnet_Users.aspnet_Roles.RoleName == "Role" select c

And moreover, will you consider start from roles? 

from r in db.aspnet_Roles
where r.RoleName == "ROLE"
select r.aspnet_Users.Persons
1
votes
(from u in db.aspnet_Users.Include("Person") 
 from c in db.aspnet_Roles 
 where c.RoleName == "role" 
 select u.Persons);

Worked it out thanks for trying though.