八、EFCore系列之EFCore中使用原生SQL语句

1. 某些复杂的查询可能难以用 LINQ 表达，或者表达后生成的 SQL 效率不高。
2. 需要使用某些数据库特有的功能或语法，这些功能在标准的 LINQ 查询中无法直接支持。
3. 如果已经有现成的 SQL 查询脚本或存储过程，并希望在 EF Core 中重用这些经过验证和优化的 SQL 脚本。

注意：如果使用某些数据库特有的功能或语法，会无法跨数据库。

一、EFCore执行非查询原生SQL语句

1.1 ExecuteSqlInterpolated

用途: 执行参数化的 SQL 命令（如 INSERT、UPDATE、DELETE），使用字符串插值的方式传递参数。
特点: 自动参数化，防止 SQL 注入，有异步方法。
返回值: 受影响的记录数（int）

private static void ExecuteRawSqlInsert(MyDbContext ctx)
{
    string title = "这是一个不一样的标题";
    ctx.Database
        .ExecuteSqlInterpolated(@$"
        INSERT INTO t_article(Title,Message)
        SELECT {title},Message
        from t_article
        where Id=1");
}

1.2 ExecuteSqlRaw

用途: 执行原始 SQL 命令，参数通过单独的参数数组传递。
特点: 需要手动参数化，只要正确使用参数化查询，就可以防止 SQL 注入，有异步方法。
返回值: 受影响的记录数（int）。

private static void ExecuteRawSqlInsert1(MyDbContext ctx)
{
    using (var transaction = ctx.Database.BeginTransaction())
    {
        try
        {
            string title = "这是一个不一样的标题";
            ctx.Database
                .ExecuteSqlRaw(
                @"INSERT INTO t_article(Title,Message)
                SELECT {0},Message
                from t_article where Id=1",
                title);
            // 提交事务
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // 回滚事务
            transaction.Rollback();
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }
}

二、EFCore执行原生SQL查询语句

2.1 FromSqlInterpolated

用途: 执行参数化的 SQL 查询，并将结果映射到实体中，使用字符串插值的方式传递参数。
特点: 自动参数化，防止 SQL 注入。
返回值: 返回一个 IQueryable<T>，所以没有异步方法，可以进一步使用 LINQ 进行组合查询。

private static void ExecuteRawSqlQuery(MyDbContext ctx)
{
    string titleParrent = "%不一样%";
    // 1. 使用 FromSqlInterpolated 方法执行原生SQL查询。
    IQueryable<Article> articles = ctx.Articles
        .FromSqlInterpolated(@$"
        SELECT * FROM t_article
        WHERE Title LIKE {titleParrent}
        ");
    // 2. 对 IQueryable 进行进一步处理。
    var articles1 = articles.OrderByDescending(a => a.Id);
    // 3. 遍历结果集。
    foreach (var article in articles1)
    {
        Console.WriteLine($"文章标题：{article.Title}");
    }
}

2.2 FromSqlRaw

用途: 执行原始 SQL 查询，并将结果映射到实体中，参数通过单独的参数数组传递。
特点: 需要手动参数化，只要正确使用参数化查询，就可以防止 SQL 注入。
返回值: 返回一个 IQueryable<T>，所以没有异步方法，可以进一步使用 LINQ 进行组合查询。

private static void ExecuteRawSqlQuery1(MyDbContext ctx)
{
    using (var transaction = ctx.Database.BeginTransaction())
    {
        try
        {
            string titleParrent = "%不一样%";
            IQueryable<Article> articles = ctx.Articles
                .FromSqlRaw(@"
                SELECT * FROM t_article
                WHERE Title LIKE {0}"
                , titleParrent);
            foreach (var article in articles)
            {
                Console.WriteLine($"文章标题：{article.Title}");
            }
            // 提交事务
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // 回滚事务
            transaction.Rollback();
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }
}

2.3 FromSqlInterpolated局限性

1. 映射的实体

• 实体类型: FromSqlInterpolated 的查询结果必须映射到一个实体类型（即 EF Core 中定义的 DbSet 对应的类）。
• 单表映射: 查询结果通常映射到单个实体，而不是多个关联实体。这意味着即使你在 SQL 中写了多表 JOIN 查询，EF Core 也只能将结果映射到单个实体中。

2. 查询结果的列

• 必须包含所有映射的属性: 如果查询结果映射到一个实体，SQL 查询必须返回该实体的所有映射属性（即数据库表中的所有列）。如果缺少某些列，EF Core 会抛出异常。
• 不能选择部分列: 你不能在 SQL 中只选择部分列（例如 SELECT Id, Name FROM Users），除非你定义一个与查询结果匹配的类（例如 DTO 或匿名类型）。

3. 多表查询的限制

• JOIN 查询: 虽然可以在 SQL 中写多表 JOIN 查询，但查询结果只能映射到单个实体。JOIN 的其他表的列会被忽略，除非手动处理这些数据。
• 无法自动加载关联数据: FromSqlInterpolated 不会自动加载关联数据（例如通过 Include）。如果需要加载关联数据，添加对应的DTO ，或者通过后续的 LINQ 查询加载。

2.4 多表查询

方案 1：使用 Include 加载关联数据

private static void ExecuteRawSqlQuery2(MyDbContext ctx)
{
    string titleParrent = "%不一样%";
    // 1. 使用 FromSqlInterpolated 方法执行原生SQL查询。
    IQueryable<Article> articles = ctx.Articles
        .FromSqlInterpolated(@$"
    SELECT * FROM t_article
    WHERE Title LIKE {titleParrent}
    ");
    // 2. Include 关联 Comment 表。
    var articles1 = articles.Include(a => a.Comments);
    foreach (var article in articles1)
    {
        Console.WriteLine($"文章标题：{article.Title}");
        // 3. 遍历 Comment 结果集。
        foreach (var comment in article.Comments)
        {
            Console.WriteLine($"评论内容：{comment.Message}");
        }
    }
}

方案 2：定义 DTO

创建DTO类

public class ArticleCommentDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string CMessage { get; set; }
    public string AMessage { get; set; }
}

查询数据

SqlQuery 是 EF Core 7.0 引入的新方法，用于执行原始 SQL 查询并返回映射结果。支持将查询结果映射到实体或 DTO。

private static void ExecuteRawSqlQuery2(MyDbContext ctx)
{
    string titleParrent = "%不一样%";
    // 1. 使用 SqlQuery 方法执行原生SQL查询。
    IQueryable<ArticleCommentDto> articles = ctx.Database
        .SqlQuery<ArticleCommentDto>
            (@$"
            SELECT a.Id,a.Message AS AMessage,a.Title,c.Message AS CMessage
            FROM t_article a LEFT JOIN t_comment c
            On a.Id=c.ComArticleId
            WHERE Title LIKE {titleParrent}
            ");
    // 2. 遍历结果集。
    foreach (var article in articles)
    {
        Console.WriteLine($"文章Id：{article.Id}，文章标题：{article.Title}，文章内容：{article.AMessage}，文章评论：{article.CMessage}");
    }
}

三、对比总结

对比总结

方法	用途	参数化方式	返回值	适用场景
ExecuteSqlInterpolated	执行参数化 SQL 命令	自动（字符串插值）	int	简单的增删改操作
ExecuteSqlRaw	执行原始 SQL 命令	手动（参数数组）	int	动态生成的 SQL 命令
FromSqlInterpolated	执行参数化 SQL 查询	自动（字符串插值）	IQueryable<T>	查询操作，结果映射到实体
FromSqlRaw	执行原始 SQL 查询	手动（参数数组）	IQueryable<T>	IQueryable<T> 动态生成的 SQL 查询
SqlQuery	执行参数化 SQL 命令	自动（字符串插值）	IQueryable<T>	适合直接映射到实体或 DTO

选择建议

• 安全性优先: 使用 ExecuteSqlInterpolated 或 FromSqlInterpolated，因为它们自动参数化，防止 SQL 注入。
• 灵活性优先: 使用 ExecuteSqlRaw 或 FromSqlRaw，适合动态生成的 SQL。
• 查询操作: 使用 FromSqlInterpolated 或 FromSqlRaw。
• 命令操作: 使用 ExecuteSqlInterpolated 或 ExecuteSqlRaw。