如何在C# EF Core中实现JOIN子句带额外条件的SQL查询

假设你已经定义了如下实体及DbContext配置：

public class Notification
{
    public int Id { get; set; }
    public int NotificationType { get; set; } // 0=用户通知，1=会话通知
    public int ObjectId { get; set; }
    // 其他通知字段...
}

public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    // 其他用户字段...
}

public class Conversation
{
    public int ConversationId { get; set; }
    public string Name { get; set; }
    // 其他会话字段...
}

// DbContext里的DbSet声明
public DbSet<Notification> Notifications { get; set; }
public DbSet<User> Users { get; set; }
public DbSet<Conversation> Conversations { get; set; }

写法1：查询语法（更贴近原生SQL，易读性高） #

EF Core中JOIN的多条件匹配可以通过构造属性完全一致的匿名类实现，生成的SQL会自动转为AND条件：

var query = from n in _context.Notifications
            // 左连接User表，添加notificationType=0的额外条件
            join u in _context.Users 
            on new { n.ObjectId, Type = 0 } equals new { ObjectId = u.UserId, Type = n.NotificationType } into userJoin
            from u in userJoin.DefaultIfEmpty()
            // 左连接Conversation表，添加notificationType=1的额外条件
            join c in _context.Conversations
            on new { n.ObjectId, Type = 1 } equals new { ObjectId = c.ConversationId, Type = n.NotificationType } into convJoin
            from c in convJoin.DefaultIfEmpty()
            select new 
            {
                UserFirstName = u.FirstName,
                ConversationName = c.Name
                // 按需添加其他字段
            };

// 执行查询
var result = query.ToList();

写法2：方法链语法 #

var result = _context.Notifications
    .GroupJoin(
        _context.Users,
        n => new { n.ObjectId, Type = 0 },
        u => new { ObjectId = u.UserId, Type = n.NotificationType },
        (n, userJoin) => new { n, userJoin }
    )
    .SelectMany(x => x.userJoin.DefaultIfEmpty(), (x, u) => new { x.n, u })
    .GroupJoin(
        _context.Conversations,
        x => new { x.n.ObjectId, Type = 1 },
        c => new { ObjectId = c.ConversationId, Type = x.n.NotificationType },
        (x, convJoin) => new { x.n, x.u, convJoin }
    )
    .SelectMany(x => x.convJoin.DefaultIfEmpty(), (x, c) => new 
        {
            UserFirstName = x.u.FirstName,
            ConversationName = c.Name
        })
    .ToList();

注意：两个关联条件的匿名类的属性名、属性类型、属性顺序必须完全一致，EF Core才能正确解析为JOIN后的AND条件，生成的SQL和你给出的原生SQL完全匹配。

你当前的设计属于多态外键关联，优点是表字段少，但是存在几个明显缺陷：

• 无法添加数据库层面的外键约束，无法保证ObjectID的数据合法性，容易出现脏数据
• 无法针对关联场景创建有效索引，数据量上来后JOIN查询性能会明显下降
• 后续新增通知类型需要修改所有关联查询的代码，维护成本高

给你两种可落地的优化方案：

方案1：分字段存储不同外键（推荐，改动最小性能最高） #

给Notification表新增独立的关联字段，允许为NULL，仅对应类型的字段存储值：

优势：

• 可以给每个关联字段加外键约束，保证数据一致性
• 可以分别给user_id、conversation_id建索引，查询性能大幅提升
• 新增通知类型只需要加对应字段即可，不需要修改原有查询逻辑
• EF Core可以直接配置导航属性，不需要手动写复杂的JOIN语句

方案2：EF Core TPH继承模式（适合业务逻辑复杂的场景） #

用EF Core原生支持的按层次表（TPH）模式设计：

• 定义Notification基类存储公共字段
• 定义UserNotification、ConversationNotification子类，分别关联User、Conversation导航属性
• EF Core自动用notification_type作为鉴别器，查询时可以直接用OfType<T>筛选对应类型的通知，不需要手动处理关联条件。

// 通知基类
public abstract class Notification
{
    public int Id { get; set; }
    public string Title { get; set; }
    // 其他公共字段
}

// 用户通知子类
public class UserNotification : Notification
{
    public int UserId { get; set; }
    public User User { get; set; }
}

// 会话通知子类
public class ConversationNotification : Notification
{
    public int ConversationId { get; set; }
    public Conversation Conversation { get; set; }
}

查询时可以直接写：

// 直接查询所有用户通知
var userNotifications = _context.Notifications.OfType<UserNotification>().Include(n => n.User).ToList();

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