使用Entity Framework处理SQL Server多对多关系的地址查询问题

首先默认你使用关系型数据库实现需求，以下方案基于常规表字段设计约定，字段名可根据实际业务表结构调整：

• 先对齐各表核心字段的命名约定（和你的实际设计匹配即可，不需要完全一致）：
  • User 用户表：核心主键为user_id，标记唯一用户
  • Owner 所有者表：核心主键为owner_id，标记唯一所有者主体；额外字段bind_user_id记录该所有者绑定的对应用户，当所有者就是用户本身时，该字段存储对应user_id
  • UserOwnerR 用户-所有者权限中间表：存储用户和所有者的授权关系，字段为user_id（被授权的用户ID）、owner_id（开放权限的所有者ID），存在记录即代表用户可访问该所有者下的资源
  • Address 地址表：核心字段address_id、owner_id，标记地址归属的所有者

通用查询实现 #

传入需要查询的目标用户ID（示例中用${targetUserId}表示），SQL写法兼容MySQL、PostgreSQL等主流数据库：

SELECT DISTINCT a.*
FROM Address a
-- 先关联出当前用户所有被授权访问的所有者下的地址
INNER JOIN UserOwnerR r
  ON a.owner_id = r.owner_id
  AND r.user_id = ${targetUserId}
-- 匹配当前用户自身作为所有者的记录，用于后续排除
LEFT JOIN Owner o
  ON a.owner_id = o.owner_id
  AND o.bind_user_id = ${targetUserId}
-- 过滤掉用户自己作为所有者直接关联的地址
WHERE o.owner_id IS NULL;

逻辑说明 #

1. 第一次内连接通过权限中间表，直接筛选出所有「对用户开放权限的所有者」名下的地址，这一步拿到用户所有可访问的地址全集
2. 左连接匹配所有者为用户自身的记录，通过WHERE o.owner_id IS NULL把用户自己直接持有的地址排除，最终结果完全符合要求：仅包含其他所有者授权开放的地址，不含用户作为所有者直接关联的地址

简化场景写法 #

如果你的设计中Owner表和User表是严格一对一关系，且owner_id和对应用户的user_id取值完全相同，可以跳过Owner表关联，直接写简化版查询：

SELECT DISTINCT a.*
FROM Address a
INNER JOIN UserOwnerR r
  ON a.owner_id = r.owner_id
  AND r.user_id = ${targetUserId}
-- 直接排除所有者ID等于当前用户ID的自有地址
WHERE a.owner_id != ${targetUserId};

内容的提问来源于stack exchange，提问作者Stronghold