c# sql SQL 查询

<div style="margin: 0 auto; padding: 20px; font-family: -apple-system, BlinkMacSystemFont, “Segoe UI”, Roboto, “Helvetica Neue”, Arial, sans-serif;”>

c# sql SQL 查询

通过 Core 可以在使用关系数据库时下降到 SQL 查询。 如果所需查询无法使用 LINQ 表示，或者 LINQ 查询导致 EF 生成效率低下的 SQL，则可使用 SQL 查询。 SQL 查询可返回一般实体类型或者模型中的无键实体类型。

基本 SQL 查询

可使用 基于 SQL 查询开始 LINQ 查询：

var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToListAsync();

注意

EF Core 7.0 中引入了 。 使用更旧的版本时，请改用 。

SQL 查询可用于执行返回实体数据的存储过程：

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToListAsync();

注意

只能直接在 DbSet 上使用。 不能在任意 LINQ 查询的基础上组合使用它。

传递参数

警告

请在使用 SQL 查询时密切关注参数化

向 SQL 查询引入任何用户提供的值时c# sql，必须注意防范 SQL 注入攻击。 如果程序将用户提供的字符串值集成到 SQL 查询中，而用户提供的值被创建用来终止字符串并执行另一个恶意 SQL 操作，则表明发生了 SQL 注入。 若要详细了解 SQL 注入，请查看此页。

和 方法可以防止 SQL 注入c# sql，始终将参数数据作为单独的 SQL 参数进行集成。 但是，如果不当使用， 方法可能易受 SQL 注入攻击。 有关详细信息，请参阅下文。

下面的示例通过在 SQL 查询字符串中包含参数占位符并提供额外的自变量，将单个参数传递到存储过程：

var user = "johndoe";
var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

虽然此语法可能看起来像常规的 C# 字符串插值，但提供的值会包裹在 中，而生成的参数名称会插入到指定的 {0} 占位符位置。 这使得 可以免受 SQL 注入攻击，可以将值高效且正确地发送到数据库。

执行存储过程时，在 SQL 查询字符串中使用命名参数很有用，尤其是在存储过程具有可选参数的情况下：

var user = new SqlParameter("user", "johndoe");
var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToListAsync();

如果需要对要发送的数据库参数进行更多控制，还可以构造 并将其作为参数值提供。 这样就可以设置参数的数据库类型或其他方面（例如大小、精度或长度）：

var user = new SqlParameter("user", "johndoe");
var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

注意

传递的参数必须与存储过程定义完全匹配。 请特别注意参数的排序，注意不要缺失或错放任何参数，也可以考虑使用命名参数表示法。 此外c# sql，请确保参数类型对应，并根据需要设置其特性（大小、精确度、比例）。

动态 SQL 和参数

应尽可能使用 及其参数化。 但在某些情况下，需要将 SQL 动态拼凑在一起，并且无法使用数据库参数。 例如，假设 C# 变量包含要通过其进行筛选的属性的名称。 你可能会想要使用 SQL 查询，例如：

var propertyName = "User";
var propertyValue = "johndoe";
var blogs = await context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToListAsync();

此代码无效，因为数据库不允许将列名（或架构的任何其他部分）参数化。

首先，请务必考虑通过 SQL 或其他方式动态构造查询的影响。 接受用户提供的列名时，用户可能会选择一个未编制索引的列，使查询运行起来极慢且让数据库过载；用户也可能选择一个包含你不希望公开的数据的列。 除了真正动态的场景外，通常更好是为两个列名分别使用两个查询，而不是通过参数化将它们合并为一个查询。

如果决定要动态构造 SQL，则必须使用 ，这样就可以直接将变量数据内插到 SQL 字符串中，而不使用数据库参数：

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");
var blogs = await context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToListAsync();

在上面的代码中，我们使用 C# 字符串内插将列名直接插入到 SQL 中。 你有责任确保此字符串值是安全的，如果它的来源不安全，则需要对其进行清理。这意味着需要检测特殊字符（如分号、注释和其他 SQL 构造），并正确地转义这些字符或拒绝此类输入。

另一方面，列值通过 发送，因此在遇到 SQL 注入时是安全的。

警告

使用 时要非常小心，始终确保值来自安全的源，或者经过正确清理。 SQL 注入攻击可能会为应用程序带来灾难后果。

使用 LINQ 编写

可以使用 LINQ 运算符在初始 SQL 查询的基础上进行组合；EF Core 会将 SQL 视为子查询，在数据库中以它为基础进行组合。 下面的示例使用 SQL 查询，该查询从表值函数 (TVF) 中进行选择。 然后，使用 LINQ 进行筛选和排序，从而对其进行组合。

var searchTerm = "Lorem ipsum";
var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToListAsync();

上面的查询生成以下 SQL：

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

包含关联数据

运算符可用于加载相关数据，就像对其他 LINQ 查询那样：

var searchTerm = "Lorem ipsum";
var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToListAsync();

使用 LINQ 进行组合要求 SQL 查询是可组合的，因为 EF Core 会将提供的 SQL 视为子查询。 可组合 SQL 查询通常以 关键字开头，不能包含子查询中无效的 SQL 功能，例如：

SQL 不允许对存储过程调用进行组合，因此任何尝试向此类调用应用其他查询运算符的操作都将导致无效的 SQL。 请在 或 之后立即使用 或 ，确保 EF Core 不会尝试对存储过程进行组合。

更改跟踪

使用 或 的查询遵循与 EF Core 中所有其他 LINQ 查询完全相同的更改跟踪规则。 例如，如果查询指定实体类型，默认情况下结果会被跟踪。

下面的示例使用 SQL 查询，该查询从表值函数 (TVF) 中进行选择，然后禁用通过调用 进行的更改跟踪：

var searchTerm = "Lorem ipsum";
var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToListAsync();

查询标量（非实体）类型

虽然可以使用 来查询模型中定义的实体，但如果使用 ，你就可以通过 SQL 轻松查询非实体标量类型，无需下降到较低级别的数据访问 API。 例如c# sql SQL 查询，以下查询从 Blogs 表中提取所有 ID：

var ids = await context.Database
    .SqlQuery($"SELECT [BlogId] FROM [Blogs]")
    .ToListAsync();

var ids = await context.Database
    .SqlQuery($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToListAsync();

var ids = await context.Database
    .SqlQuery($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToListAsync();

还可以在 SQL 查询的基础上组合使用 LINQ 运算符。 但是，由于 SQL 成为子查询，其输出列需要由 SQL EF 添加项来引用，因此必须为输出列 Value 命名。 例如，以下查询返回的 ID 高于 ID 平均值：

var overAverageIds = await context.Database
    .SqlQuery($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

var overAverageIds = await context.Database
    .SqlQuery($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

var overAverageIds = await context.Database
    .SqlQuery($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToListAsync();

可与数据库提供程序支持的任何标量类型配合使用。 如果想要使用数据库提供程序不支持的类型，可以使用为其定义值转换。

不限于标量类型。 也可以将结果集的每一行投影到不属于 EF 模型的任意 CLR 类型中，前提是该类型具有结果集中每个列的属性。 有关更多详细信息，请参阅下面的 。

允许动态构造 SQL 查询，就像 对实体类型所做的那样。

查询未映射的类型

注意

EF Core 8.0 引入了从 返回未映射类型的功能。

除了标量类型， 还可以从 SQL 查询的结果集中填充可映射 CLR 类型的实例，而无需在 EF 模型中包括该类型。 用于查询结果的类型可以包含 EF Core 支持的常见映射构造，例如参数化构造函数和映射属性。 所使用的类型必须具有结果集中每个值的属性，但不需要匹配数据库中的任何表。 例如，给定以下类型：

public class BlogPost
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateOnly PublishedOn { get; set; }
    public int BlogId { get; set; }
}

可以使用与标量类型相同的方式查询 类型：

var start = new DateOnly(2022, 1, 1);
var end = new DateOnly(2023, 1, 1);
var posts = await context.Database
    .SqlQuery($"SELECT * FROM Posts AS p WHERE p.PublishedOn >= {start} AND p.PublishedOn < {end}")
    .ToListAsync();

所使用的类型必须具有结果集中每个值的属性，但它不需要匹配数据库中的任何表 – 例如，它可以表示列的子集或跨多个表的联接的结果。

注意

以这种方式使用的未映射类型没有定义键，并且不能与其他类型的关系。 必须将具有关系的类型映射到模型中。

未映射的复杂类型

返回的未映射类型还可以包含嵌套的 复杂类型，从而使你能够将结果集中的多个列组合成一个嵌套的值对象。 例如，假设有一个嵌套在 结果类型中的 复杂类型：

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
}
[ComplexType]
public class Address
{
    public string Line1 { get; set; }
    public string City { get; set; }
    public string PostCode { get; set; }
    public string Country { get; set; }
}

嵌套的复杂类型属性根据在模型中映射复杂类型时使用的相同列名约定从结果集中填充，其中每个嵌套属性都映射到一个列名，该列名由复杂属性名和嵌套属性名以下划线连接而成（例如，）：

var customers = await context.Database
    .SqlQuery(
        $"""
         SELECT [Id], [Name], [Address_Line1], [Address_City], [Address_PostCode], [Address_Country]
         FROM [Customers]
         """)
    .ToListAsync();

与模型中的复杂类型一样c# sql SQL 查询，嵌套类型没有键，并且无法定义与其他类型的关系。

执行非查询 SQL

在某些情况下，可能需要执行不返回任何数据的 SQL，通常用于修改数据库中的数据或调用不返回任何结果集的存储过程。 可以通过 完成此操作：

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

它会执行提供的 SQL 并返回修改的行的数目。 使用安全的参数化来防止 SQL 注入，就像 一样，而 允许动态构造 SQL 查询，就像 对查询所做的那样。

限制

从 SQL 查询返回实体类型时，需注意以下几个限制：