LINQ to SQL - No Foreign Keys

Question

No foreign keys defined in the database. So I've setup associations.

problem: can't seem to be able to reference the Role table as expected:

I can get as far as u.UserNamesInRole then can't make the jump to role table.

IEnumerable<fmwebapp1.Old_App_Code.TelerikUsersDataContext.User> userList = (from u in dbTelerik.Users
where u.UsersInRoles.Role.Name = "admin"
select u.UsersInRoles);

Answer 1

where u.UsersInRoles.Role.Name = "admin"

That is incorrect syntax. You need ==.

Answer 2

In a many-to-many relationship, when I want results from one side only, filtered by values on the other side, I usually start my query in the middle of the relation:

To get a role's users:

var users = from ur in context.UsersInRole
            where ur.Role.Name == "admin"
            select ur.User;

To get a user's roles:

var roles = from ur in context.UsersInRole
            where ur.User.UserName == "Jon"
            select ur.Role

Answer 3

It would be easiest to add an FK relationship in your database and reimport the tables. EF will then do all the work for you.

If that's not possible, try adding an Association from Role to User and then set the Association Set Name to UsersInRole. Delete the UsersInRole entity from the designer.

You could also compare the EDMX file generated from importing a 'proper' FK relationship between two tables with what you have in your EDMX and then hand edit the XML to match.