Asp.netCore3.1数据库访问

教程：了解高级方案 - ASP.NET MVC 和 EF Core

之前的教程中，已经以每个类一张表的方式实现了继承。本教程将会介绍在掌握开发基础 ASP.NET Core web 应用程序之后使用 Entity Framework Core 开发时需要注意的几个问题。

在本教程中，你将了解：

执行原始 SQL 查询
调用查询以返回实体
调用查询以返回其他类型
调用更新查询
检查 SQL 查询
创建抽象层
了解自动更改检测
了解 EF Core 源代码与开发计划
了解如何使用动态 LINQ 简化代码

先决条件

实现继承

执行原始 SQL 查询

使用 Entity Framework 的优点之一是它可避免你编写跟数据库过于耦合的代码它会自动生成 SQL 查询和命令，使得你无需自行编写。但有一些特殊情况，你需要执行手动创建的特定 SQL 查询。对于这些情况下， Entity Framework Code First API 包括直接传递 SQL 命令将到数据库的方法。在 EF Core 1.0 中具有以下选项：

使用DbSet.FromSql返回实体类型的查询方法。返回的对象必须是DbSet对象期望的类型，并且它们会自动跟踪数据库上下文中除非你手动关闭跟踪。
对于非查询命令使用Database.ExecuteSqlCommand。

如果需要运行该返回类型不是实体的查询，你可以使用由 EF 提供的 ADO.NET 中使用数据库连接。数据库上下文不会跟踪返回的数据，即使你使用该方法来检索实体类型也是如此。

在 Web 应用程序中执行 SQL 命令时，请务必采取预防措施来保护站点免受 SQL 注入攻击。一种方法是使用参数化查询，确保不会将网页提交的字符串视为 SQL 命令。在本教程中，将用户输入集成到查询中时会使用参数化查询。

调用查询以返回实体

DbSet<TEntity> 类提供了一种方法，可用于执行返回 TEntity 类型实体的查询。若要查看实现细节，你需要更改院系控制器中 Details 方法的代码。

在 DepartmentsController.cs 的 Details 方法中，使用 FromSql 方法调用替换检索院系的代码，如以下突出显示的代码所示：

public async Task<IActionResult> Details(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    string query = "SELECT * FROM Department WHERE DepartmentID = {0}";
    var department = await _context.Departments
        .FromSql(query, id)
        .Include(d => d.Administrator)
        .AsNoTracking()
        .FirstOrDefaultAsync();

    if (department == null)
    {
        return NotFound();
    }

    return View(department);
}

为了验证新代码是否正常工作，请选择“院系”选项卡，然后选择其中某一院系的“详细信息” 。

院系详细信息

调用查询以返回其他类型

之前你在“关于”页面创建了一个学生统计信息网格，显示每个注册日期的学生数量。可以从学生实体集中获取数据 (_context.Students) ，使用 LINQ 将结果投影到EnrollmentDateGroup视图模型对象的列表。假设你想要 SQL 本身编写，而不使用 LINQ。需要运行 SQL 查询中返回实体对象之外的内容。在 EF Core 1.0 中，执行该操作的另一种方法是编写 ADO.NET 代码，并从 EF 获取数据库连接。

在HomeController.cs中，将About方法替换为以下代码：

public async Task<ActionResult> About()
{
    List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
    var conn = _context.Database.GetDbConnection();
    try
    {
        await conn.OpenAsync();
        using (var command = conn.CreateCommand())
        {
            string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
                + "FROM Person "
                + "WHERE Discriminator = 'Student' "
                + "GROUP BY EnrollmentDate";
            command.CommandText = query;
            DbDataReader reader = await command.ExecuteReaderAsync();

            if (reader.HasRows)
            {
                while (await reader.ReadAsync())
                {
                    var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
                    groups.Add(row);
                }
            }
            reader.Dispose();
        }
    }
    finally
    {
        conn.Close();
    }
    return View(groups);
}

添加 using 语句：

using System.Data.Common;

运行应用并转到“关于”页面。显示的数据和之前一样。

“关于”页面

调用更新查询

假设 Contoso University 管理员希望在数据库中执行全局更改，例如更改每门课程的学分。如果该大学提供了大量课程，那么将所有课程作为实体来检索并单独更改就非常低效。在本节中，你将实现一个网页，使用户能够指定一个系数，通过该系数即可更改所有课程的学分，而你将通过执行 SQL UPDATE 语句来完成更改。网页的外观类似于下图：

“更新课程学分”页面

在 CoursesController.cs 中，为 HttpGet 和 HttpPost 添加 UpdateCourseCredits 方法：

public IActionResult UpdateCourseCredits()
{
    return View();
}

[HttpPost]
public async Task<IActionResult> UpdateCourseCredits(int? multiplier)
{
    if (multiplier != null)
    {
        ViewData["RowsAffected"] = 
            await _context.Database.ExecuteSqlCommandAsync(
                "UPDATE Course SET Credits = Credits * {0}",
                parameters: multiplier);
    }
    return View();
}

当控制器处理 HttpGet 请求时，ViewData["RowsAffected"]中不会返回任何东西，并且在视图中显示一个空文本框和提交按钮，如上图所示。

当单击Update按钮时，将调用 HttpPost 方法，且从文本框中输入的值获取乘数。代码接着执行 SQL 语句更新课程，并向视图的ViewData返回受影响的行数。当视图获取RowsAffected值，它将显示更新的行数。

在“解决方案资源管理器”中，右键单击“Views/Courses”文件夹，然后依次单击“添加”和“新建项” 。

在“添加新项” 对话框中，在左侧窗格的“已安装” 下单击“ASP.NET Core” ，单击“Razor 视图” ，并将新视图命名为“UpdateCourseCredits.cshtml” 。

在Views/Courses/UpdateCourseCredits.cshtml中，将模板代码替换为以下代码：

@{
    ViewBag.Title = "UpdateCourseCredits";
}

<h2>Update Course Credits</h2>

@if (ViewData["RowsAffected"] == null)
{
    <form asp-action="UpdateCourseCredits">
        <div class="form-actions no-color">
            <p>
                Enter a number to multiply every course's credits by: @Html.TextBox("multiplier")
            </p>
            <p>
                <input type="submit" value="Update" class="btn btn-default" />
            </p>
        </div>
    </form>
}
@if (ViewData["RowsAffected"] != null)
{
    <p>
        Number of rows updated: @ViewData["RowsAffected"]
    </p>
}
<div>
    @Html.ActionLink("Back to List", "Index")
</div>

通过选择Courses选项卡运行UpdateCourseCredits方法，然后在浏览器地址栏中 URL 的末尾添加"/ UpdateCourseCredits"到 (例如： http://localhost:5813/Courses/UpdateCourseCredits)。在文本框中输入数字：

“更新课程学分”页面

单击Update。你会看到受影响的行数：

“更新课程学分”页面中受影响的行

单击“返回列表”可以查看课程列表，其中学分已替换为修改后的数字。

请注意，生产代码将确保更新最终生成有效的数据。此处所示的简化代码可将学分的数字进行相乘，以便足以生成大于 5 的数字。 (Credits 属性具有 [Range(0, 5)] 特性。)更新查询会起作用，但无效数据可能在系统的其他部分中产生意外结果，这些部分假定学分为 5 或更少。

有关原生 SQL 查询的详细信息，请参阅原生 SQL 查询。

检查 SQL 查询

有时能够以查看发送到数据库的实际 SQL 查询对于开发者来说是很有用的。 EF Core 自动使用 ASP.NET Core 的内置日志记录功能来编写包含 SQL 查询和更新的日志。在本部分中，你将看到记录 SQL 日志的一些示例。

打开StudentsController.cs并在Details方法的if (student == null)语句上设置断点。

在调试模式下运行应用，并转到某位学生的“详细信息”页面。

转到输出窗口显示调试输出，就可以看到查询语句：

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (56ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [s].[ID], [s].[Discriminator], [s].[FirstName], [s].[LastName], [s].[EnrollmentDate]
FROM [Person] AS [s]
WHERE ([s].[Discriminator] = N'Student') AND ([s].[ID] = @__id_0)
ORDER BY [s].[ID]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (122ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT [s.Enrollments].[EnrollmentID], [s.Enrollments].[CourseID], [s.Enrollments].[Grade], [s.Enrollments].[StudentID], [e.Course].[CourseID], [e.Course].[Credits], [e.Course].[DepartmentID], [e.Course].[Title]
FROM [Enrollment] AS [s.Enrollments]
INNER JOIN [Course] AS [e.Course] ON [s.Enrollments].[CourseID] = [e.Course].[CourseID]
INNER JOIN (
    SELECT TOP(1) [s0].[ID]
    FROM [Person] AS [s0]
    WHERE ([s0].[Discriminator] = N'Student') AND ([s0].[ID] = @__id_0)
    ORDER BY [s0].[ID]
) AS [t] ON [s.Enrollments].[StudentID] = [t].[ID]
ORDER BY [t].[ID]

你会注意到一些可能会让你觉得惊讶的操作： SQL 从 Person 表最多选择 2 行 (TOP(2)) 。 SingleOrDefaultAsync方法服务器上不会解析为 1 行。原因是：

如果查询返回多个行，该方法会返回 null。
如果想知道查询是否返回多个行，EF 必须检查是否至少返回 2。

请注意，你不必使用调试模式，并在断点处停止，然后在输出窗口获取日志记录。这种方法非常便捷，只需在想查看输出时停止日志记录即可。如果不进行此操作，程序将继续进行日志记录，要查看感兴趣的部分则必须向后滚动。

创建抽象层

许多开发人员编写代码实现存储库和工作模式单元以作为使用 Entity Framework 代码的包装器。这些模式用于在应用程序的数据访问层和业务逻辑层之间创建抽象层。实现这些模式可让你的应用程序对数据存储介质的更改不敏感，而且很容易进行自动化单元测试和进行测试驱动开发 (TDD)。但是，编写附加代码以实现这些模式对于使用 EF 的应用程序并不总是最好的选择，原因有以下几个：

EF 上下文类可以为使用 EF 的数据库更新充当工作单位类。
对于使用 EF 进行的数据库更新，EF 上下文类可充当工作单元类。
EF 包括用于无需编写存储库代码就实现 TDD 的功能。

有关如何实现存储库和工作单元模式的详细信息，请参阅本系列教程的 Entity Framework 5 版本。

Entity Framework Core 的实现可用于测试的内存数据库驱动程序。有关详细信息，请参阅测试以及 InMemory。

自动脏值检测

Entity Framework 通过比较的实体的当前值与原始值来判断更改实体的方式（因此需要发送更新到数据库）。查询或附加该实体时会存储的原始值。如下方法会导致自动脏值：

DbContext.SaveChanges
DbContext.Entry
ChangeTracker.Entries

如果正在跟踪大量实体，并且这些方法之一在循环中多次调用，通过使用ChangeTracker.AutoDetectChangesEnabled属性暂时关闭自动脏值检测，能够显著改进性能。例如：

_context.ChangeTracker.AutoDetectChangesEnabled = false;

EF Core 源代码与开发计划

Entity Framework Core 源位于 https://github.com/dotnet/efcore。仓库中除了有源代码，还可包括每夜生成、问题跟踪、功能规范、设计会议备忘录和将来的开发路线图。你可以归档或查找 bug 并进行更改。

尽管源代码处于开源状态， Entity Framework Core 是由 Microsoft 完全支持的产品。 Microsoft Entity Framework 团队将控制接受哪些贡献和测试所有的代码更改，以确保每个版本的质量。

现有数据库逆向工程

若想要通过对现有数据库中的实体类反向工程得出数据模型，可以使用scaffold-dbcontext。可以参阅入门教程。

使用动态 LINQ 简化代码

本系列的第三个教程演示如何通过在switch语句中对列名称进行硬编码来编写 LINQ 。如果只有两列可供选择，这种方法可行，但是如果拥有许多列，代码可能会变得冗长。要解决该问题，可使用 EF.Property 方法将属性名称指定为一个字符串。要尝试此方法，请将 StudentsController 中的 Index 方法替换为以下代码。

 public async Task<IActionResult> Index(
     string sortOrder,
     string currentFilter,
     string searchString,
     int? pageNumber)
 {
     ViewData["CurrentSort"] = sortOrder;
     ViewData["NameSortParm"] = 
         String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : "";
     ViewData["DateSortParm"] = 
         sortOrder == "EnrollmentDate" ? "EnrollmentDate_desc" : "EnrollmentDate";

     if (searchString != null)
     {
         pageNumber = 1;
     }
     else
     {
         searchString = currentFilter;
     }

     ViewData["CurrentFilter"] = searchString;

     var students = from s in _context.Students
                    select s;
     
     if (!String.IsNullOrEmpty(searchString))
     {
         students = students.Where(s => s.LastName.Contains(searchString)
                                || s.FirstMidName.Contains(searchString));
     }

     if (string.IsNullOrEmpty(sortOrder))
     {
         sortOrder = "LastName";
     }

     bool descending = false;
     if (sortOrder.EndsWith("_desc"))
     {
         sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
         descending = true;
     }

     if (descending)
     {
         students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
     }
     else
     {
         students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
     }

     int pageSize = 3;
     return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), 
         pageNumber ?? 1, pageSize));
 }

鸣谢

Tom Dykstra 和 Rick Anderson (twitter @RickAndMSFT) 共同编写了本教程。 Rowan Miller、 Diego Vega 和 Entity Framework 团队的其他成员协助代码评审和帮助解决编写教程代码时出现的调试问题。 John Parente 和 Paul Goldman 致力于更新 ASP.NET Core 2.2 的教程。