如何在Entity Framework Core中通过SQL层面验证记录能否插入

原代码的核心问题是两次独立的数据库调用之间存在竞态条件——检查座位可用后，到执行插入前的这段时间里，其他请求可能已经抢走了这个座位，导致重复预订。

针对你的复杂业务场景，以下是几种具备原子性的SQL方案，确保判断和插入操作在数据库层面一次性完成，彻底避免并发冲突：

方案1：IF NOT EXISTS + INSERT 原子操作 #

修正你提供的示例SQL（原示例存在语法错误），实现判断与插入的原子执行：

DECLARE @IsSuccess BIT = 0;

-- 原子性检查：只有座位未被占用时才执行插入
IF NOT EXISTS (SELECT 1 FROM Tickets WHERE SeatNumber = @SeatNumber)
BEGIN
    INSERT INTO Tickets (UserId, SeatNumber)
    VALUES (@UserId, @SeatNumber);
    SET @IsSuccess = 1;
END;

-- 返回操作结果：1表示预订成功，0表示座位已被占用
SELECT @IsSuccess AS Result;

在EF Core中调用该SQL的示例代码：

public bool BookSeat(int userId, string seatNumber)
{
    var paramUserId = new SqlParameter("@UserId", userId);
    var paramSeatNumber = new SqlParameter("@SeatNumber", seatNumber);
    var paramResult = new SqlParameter("@IsSuccess", SqlDbType.Bit) 
    { 
        Direction = ParameterDirection.Output 
    };

    _dbContext.Database.ExecuteSqlRaw(@"
        DECLARE @IsSuccess BIT = 0;

        IF NOT EXISTS (SELECT 1 FROM Tickets WHERE SeatNumber = @SeatNumber)
        BEGIN
            INSERT INTO Tickets (UserId, SeatNumber)
            VALUES (@UserId, @SeatNumber);
            SET @IsSuccess = 1;
        END;

        SELECT @IsSuccess AS Result;", paramUserId, paramSeatNumber, paramResult);

    return (bool)paramResult.Value;
}

方案2：INSERT ... SELECT 简化写法 #

这种方式将判断逻辑直接嵌入INSERT语句，语法更简洁，原子性同样有保障：

-- 仅当座位未被占用时，才插入新记录
INSERT INTO Tickets (UserId, SeatNumber)
SELECT @UserId, @SeatNumber
WHERE NOT EXISTS (SELECT 1 FROM Tickets WHERE SeatNumber = @SeatNumber);

-- 通过返回插入行数判断结果：1=成功，0=失败
SELECT @@ROWCOUNT AS Result;

EF Core中的调用示例：

public bool BookSeat(int userId, string seatNumber)
{
    var paramUserId = new SqlParameter("@UserId", userId);
    var paramSeatNumber = new SqlParameter("@SeatNumber", seatNumber);

    var rowsAffected = _dbContext.Database.ExecuteSqlRaw(@"
        INSERT INTO Tickets (UserId, SeatNumber)
        SELECT @UserId, @SeatNumber
        WHERE NOT EXISTS (SELECT 1 FROM Tickets WHERE SeatNumber = @SeatNumber);

        SELECT @@ROWCOUNT;", paramUserId, paramSeatNumber);

    return rowsAffected > 0;
}

复杂业务场景扩展 #

如果你的校验逻辑不止座位占用（比如用户权限、场次有效性等），只需将所有校验条件加到WHERE子句中即可，整个操作依然保持原子性：

INSERT INTO Tickets (UserId, SeatNumber, SessionId)
SELECT @UserId, @SeatNumber, @SessionId
WHERE NOT EXISTS (SELECT 1 FROM Tickets WHERE SeatNumber = @SeatNumber AND SessionId = @SessionId)
AND EXISTS (SELECT 1 FROM Sessions WHERE Id = @SessionId AND StartTime > GETDATE()) -- 校验场次未开始
AND EXISTS (SELECT 1 FROM Users WHERE Id = @UserId AND IsActive = 1); -- 校验用户状态正常

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