LINQ to SQL Many to Many Comparison Query

Question

I have just finished creating a custom role provider using LINQ to SQL for data access. One of the functions you need to write for the provider is to remove users from roles;

public void RemoveUsersFromRoles(string[] usernames, string[] rolenames);

Now one approach would be;

1. return a list of roles
2. iterate for each role and remove that role from all users that have that role

This approach is far from efficient I would like to know if there are better ways to handle this type of problem in LINQ to SQL.

Is there a way to create a select statement in LINQ to SQL that will take a string array for comparison, instead of looping and making N number of selects? Any approach better than what I described above would be much appreciated.

Tables Involved:

User (RecordID, Username)
Role (RecordID, Rolename)
UsersInRole (RoleID,UserID)

Thank You!

Answer 1

This should work:

var uirQuery = from uir in db.UsersInRole
               join u in db.User on uir.UserID equals u.RecordID
               join r in db.Role on uir.RoleID equals r.RecordID
               where usernames.Contains(u.Username) 
                  && rolenames.Contains(r.Rolename)
               select uir;

db.UsersInRole.DeleteAllOnSubmit(uirQuery);

LINQ to SQL translates the "Contains" expression to a T-SQL IN clause.