LINQ to SQL - No foreign keys

Question

No foreign keys defined in the database. Therefore, I have established associations.

problem: it can't seem to reference the role table as expected:

I can get to the point that u.UserNamesInRole will not be able to navigate to the 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

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

To get the user role:

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

To get user roles:

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

Answer 2

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

This is the wrong syntax. You need to ==.

Answer 3

FK reimport . EF .

, Association Role User, Set Set Name UsersInRole. UsersInRole .

You can also compare the EDMX file created by importing the "correct" FK relationship between the two tables with what you have in your EDMX, and then manually edit the XML to match.