LINQ to SQL 多对多比较查询

发布于 2024-07-30 09:44:48 字数 548 浏览 2 评论 0原文

我刚刚使用 LINQ to SQL 进行数据访问创建了一个自定义角色提供程序。您需要为提供者编写的功能之一是从角色中删除用户；

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

现在一种方法是；

返回角色列表
对每个角色进行迭代，并从拥有该角色的所有用户中删除该角色

这种方法远非高效我想知道是否有更好的方法来处理 LINQ to SQL 中的此类问题。

有没有办法在 LINQ to SQL 中创建一个 select 语句，该语句将采用字符串数组进行比较，而不是循环并进行 N 次选择？任何比我上面描述的方法更好的方法将不胜感激。

相关表：

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

谢谢！

原文

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;

return a list of roles
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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄居者 2024-08-06 09:44:48

这应该可行：

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 将“Contains”表达式转换为 T-SQL IN 子句。

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.

回复收藏 0 原文

~没有更多了~