如何将关联PageView与Tag表筛选同标签URI的SQL查询转为LINQ

数据库中存在两张核心表：

• PageView表：存储用户页面访问记录，每条记录对应一次特定URI的访问
• Tag表：存储与PageView记录关联的标签，每个URI对应多个关联标签

返回与指定URI拥有相同标签的URI列表，按共享标签的URI计数降序排序，且仅匹配路径包含/articles/的URI。

select count(URI) as 'Count', p.URI, t.Name
from tracking.PageView as p
inner join  Tracking.Tag as t on p.ID = t.PageViewID
where t.name in 
    (select t.Name
    from tracking.PageView as p
    inner join  Tracking.Tag as t on p.ID = t.PageViewID
    where p.URI = 'URI WE WANT TAGS OF'
    )
and p.uri like '%/articles/%'
group by p.URI , t.name
order by Count desc

假设你的数据上下文DbContext中存在DbSet<PageView> PageViews和DbSet<Tag> Tags两个数据集，可按以下两种方式实现：

方式1：先提取目标标签集合（可读性更高，支持本地二次复用） #

// 替换为你要查询的目标URI
string targetUri = "URI WE WANT TAGS OF";

// 第一步：获取目标URI对应的所有标签集合
var targetTags = await context.PageViews
    .Where(p => p.URI == targetUri)
    .Join(context.Tags,
        page => page.ID,
        tag => tag.PageViewID,
        (page, tag) => tag.Name)
    .Distinct()
    .ToListAsync();

// 第二步：主查询匹配标签、分组排序
var result = await context.PageViews
    .Where(p => p.URI.Contains("/articles/"))
    .Join(context.Tags,
        page => page.ID,
        tag => tag.PageViewID,
        (page, tag) => new { page.URI, tag.Name })
    .Where(joinItem => targetTags.Contains(joinItem.Name))
    .GroupBy(joinItem => new { joinItem.URI, joinItem.Name })
    .Select(group => new 
    {
        Count = group.Count(),
        URI = group.Key.URI,
        TagName = group.Key.Name
    })
    .OrderByDescending(item => item.Count)
    .ToListAsync();

方式2：保留子查询逻辑（生成的SQL与原生SQL完全一致） #

不需要先执行标签查询，保留IQueryable类型让EF自动拼接为嵌套子查询，避免两次数据库请求：

string targetUri = "URI WE WANT TAGS OF";

// 不调用ToListAsync，保留为IQueryable，EF会自动合并为单条SQL
var targetTags = context.PageViews
    .Where(p => p.URI == targetUri)
    .Join(context.Tags,
        page => page.ID,
        tag => tag.PageViewID,
        (page, tag) => tag.Name)
    .Distinct();

// 主查询逻辑和方式1完全一致
var result = await context.PageViews
    .Where(p => p.URI.Contains("/articles/"))
    .Join(context.Tags,
        page => page.ID,
        tag => tag.PageViewID,
        (page, tag) => new { page.URI, tag.Name })
    .Where(joinItem => targetTags.Contains(joinItem.Name))
    .GroupBy(joinItem => new { joinItem.URI, joinItem.Name })
    .Select(group => new 
    {
        Count = group.Count(),
        URI = group.Key.URI,
        TagName = group.Key.Name
    })
    .OrderByDescending(item => item.Count)
    .ToListAsync();

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