Entity framework linq to entities

Answer 1

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

Answer 2

(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.