如何将指定SQL查询转为EF Framework查询？解决执行报错问题

原始SQL需求 #

需要从badges表获取所有记录，新增布尔字段checked标识特定餐厅是否拥有对应徽章，对应的SQL语句如下：

SELECT id, name, (
    select count(*) > 0 from restaurants_badges as rb
        where rb.badge_id = b.id
        and rb.restaurant_id = ''
      ) as checked
FROM badges as b;

问题代码及报错 #

编写的EF Core代码执行时抛出Specified method is not supported错误，代码如下：

// 检查特定餐厅拥有哪些徽章的方法
public List<BadgeDTO> CheckBadgesInRestaurant(string restaurantId)
{
    return _context.RestaurantBadges
        .AsNoTracking()
        .Where(rb => rb.RestaurantId.Equals(restaurantId))
        .LeftJoin(_context.Badges, rb => rb.BadgeId,
        b => b.Id,
        (rb, b) => new BadgeDTO
        {
            Id = b.Id,
            Name = b.Name,
            Check = rb.RestaurantId.Count() > 0
        })
        .ToList();
}

错误原因 #

• 查询起点错误：从RestaurantBadges出发左联Badges，只能获取该餐厅已关联的徽章记录，无法拿到Badges表的全部记录，不符合原始需求。
• 方法调用逻辑错误：rb.RestaurantId.Count()是错误用法——RestaurantId是字符串类型，EF Core无法将此方法调用转换为对应的SQL逻辑，这是触发“指定方法不支持”错误的直接原因。

正确解决方案 #

从Badges表出发，对每个徽章使用Any()判断是否存在对应餐厅的关联记录，Any()会被EF Core转换为SQL中的EXISTS子查询，与原始SQL的逻辑完全一致，代码如下：

public List<BadgeDTO> CheckBadgesInRestaurant(string restaurantId)
{
    return _context.Badges
        .AsNoTracking()
        .Select(b => new BadgeDTO()
        {
            Id = b.Id,
            Name = b.Name,
            Check = _context.RestaurantBadges
                .AsNoTracking()
                .Where(br => br.BadgeId == b.Id && br.RestaurantId == restaurantId)
                .Any()
        }).OrderBy(b => b.Id)
        .ToList();
}

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