与动态 SQL C# 相比，内部带有动态 SQL 的存储过程有什么优势？

Question

问题我发现自己需要编写一个带有分页和动态排序的存储过程（其中 case 语句不起作用）这个答案似乎是唯一的选择，但是在这一点上，有什么充分的理由让它成为一个存储过程吗？会不会有任何真正的好处，大概不会以任何有用的方式进行预编译？因此，我更倾向于简单地在 C# 代码本身中将其作为动态 SQL 来执行，并跳过使用存储过程的复杂性（我觉得它实际上在 C#构建的字符串中更具可读性）但这会明显变慢吗然后使用存储过程。更有趣的是，这是一个 EF 实际上会更快/相同的领域吗，因为它确实确实在构建动态 SQL 吗？（见背景）背景我们的大部分数据库代码都是用存储过程编写的（90%），但有一小部分代码是用实体框架编写的。我被要求用存储过程替换 EF，因为这部分代码已被确定为瓶颈，他们希望对其进行优化。作为 EF 的粉丝，我想推迟最佳点，但作为一致性的粉丝，我最初没有看到任何理由不将这 10% 与其他 90% 协调。...现在我做到了，操作有问题涉及分页和动态排序！

Answer 1

优点

SQL 在存储过程中的优点是：

• 抽象，您可以在不更改 .net 应用程序的情况下重构数据库

• 安全性，您可以通过设置正确的执行权限来进行更好的安全检查。您也可以限制数据集不输出安全意识信息。

• 透明度：对数据库执行了哪些查询？

• 查询查询的优化，使用正确的索引等。

缺点

存储过程中动态 SQL 的缺点：

动态 SQL（在 SQL 服务器中）在安全性和性能方面可能很棘手。

安全

SQL Server 防止注入的“工具”较少。变量不能随处使用，所以只有当你硬编码所有按选项排序的时候，你才是安全的。但是对于动态过滤，这可能很困难。

例如这是安全的：

posts = context.Posts  
    .FromSql("SELECT * FROM mytable")
    .OrderByDescending(p => p.CreateDate)

这是不安全的（是的，有更好的写法）：

declare varchar(200) @orderby = 'createDate DESC'
SET @sqlCommand = 'SELECT * FROM mytable ORDER by + '@orderby
EXEC (@sqlCommand)

因此，您需要列出所有选项或选中所有选项。

表现

在性能方面，存储过程中的动态 SQL可能会出现问题。返回非常动态数据的存储过程可能会选择错误的查询计划，性能不佳。

*您可以重新编译每个存储过程调用，但这也有缺点。

表值函数

另一种选择是使用表值函数 (TVF)。这些比存储过程更受限制，但它们返回一个表，您可以对其进行分页和排序。

如上所述，它们具有存储过程的优点，但没有数据库中动态 SQL 的缺点！

示例 TVF 和 EF Core

有一个很好的表值函数和 EF 示例，

.NET Core 与 TFV GetMatchingPostByTitle

posts = context.Posts  
    .FromSql("SELECT * FROM dbo.GetMatchingPostByTitle({0})", searchTerm)
    .Where(p => p.BlogId == 1)
    .OrderByDescending(p => p.CreateDate)
    .ToList();

将生成此 SQL：