How to create many to many relationship in Linq to SQL?

Question

I have a User table, Roles table and a link table User2Roles. I want to create LinqToSQL entities so that I can access roles from user as role.Users and user.Roles manually. Using designer it actually creates 3 entities, User to User2Roles and User2Roles to Roles.

Answer 1

Unfortunately I believe that you're stuck with that interim entity. As far as I know Linq to SQL does not support many to many joins in a nice clean way like you're thinking. There are workarounds in code to make the object model at least appear to support many to manys in the way you want.

Check out this post on SO
Or this link

Answer 2

You can create your own property in the role and user classes. The purpose of this property would be to get the users for the current role and return the results as a collection. Then you can do the same for the other class.

Here is some code without testing (will be replaced once I can get back to my dev box)

partial class Roles
{
  List<users> Users
  {
    get { return User2Roles.users.toList(); }
  }
}

partial class Users
{
  List<roles> Roles
  {
    get { return User2Roles.roles.toList(); }
  }
}

Answer 3

You cannot actually create a many-to-many relationship in Linq to SQL. However, you can easily create your own "sugar" for it.

public partial class User
{
    public IQueryable<Role> Roles
    {
        get { return UserRoles.Select(ur => ur.Role); }
    }
}

public partial class Role
{
    public IQueryable<User> Users
    {
        get { return UserRoles.Select(ur => ur.User); }
    }
}

That's pretty much all there is to it. This will save you the trouble of always having to deal with the intermediate entity. You can write code against it as if it were a real M:M relationship:

foreach (Role role in user.Roles) { ... }

You should even be able to use this in query projections (although you will likely have some problems using it in filters).