在EFCore中查询与更新导航属性相关数据

查询与更新导航属性相关数据

查询：预先加载、显式加载、延迟加载

预先加载(Eager Loading)的两种方式

预先加载数据库往返次数明显降低，有效提高查询效率。
另一方面，在某些情况下，单独查询会更加高效。在一个查询中预先加载所有相关数据时，可能会生成一个非常复杂的联接，SQL Server 无法有效处理该联接。或者，如果你正在处理一组实体且只需访问其子集的导航属性，那么采用单独查询可获得更佳性能，因为预先加载所有数据后，会检索不需要的数据。如果看重性能，那么最好测试两种方式的性能，以便做出最佳选择。
使用 Include 和 ThenInclude
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public IActionResult GetDepartmentsCourses()
{
    List<string> courseList = new List<string>();
    //预先加载所有，使用连接查询，一次数据库往返
    var departments = _context.Departments.Include(d => d.Courses);

    foreach (var d in departments)
    {
        foreach (var c in d.Courses)
        {
            courseList.Add(d.Name + "_" + c.Title);
        }
    }
    return Json(courseList);
}

Load 可在单独查询中检索一些数据，EF 会“修正”导航属性。

public IActionResult GetDepartmentsCourses2()
{
    List<string> courseList = new List<string>();
    var departments = _context.Departments;

    foreach (var d in departments)
    {
        // 单独查询，每次都会查询数据库，得到数据后将数据修正到导航属性
        _context.Courses.Where(c => c.DepartmentID == d.DepartmentID).Load();
        if (d.Courses == null)
        {
            continue;
        }
        foreach (var c in d.Courses)
        {
            courseList.Add(d.Name + "_" + c.Title);
        }
    }
    return Json(courseList);
}

显式加载(Explicit Loading)

因为要指定属性加载，所以叫显式

[HttpGet]
public IActionResult GetDepartmentsCourses3()
{
    List<string> courseList = new List<string>();
    var departments = _context.Departments;

    foreach (var d in departments)
    {
        //Collection方法，指定一个导航属性集合，结合Load加载。查数据库。因为要指定属性加载，所以叫显式
        _context.Entry(d).Collection(p => p.Courses).Load();

        //Reference方法检索非collection导航属性。查数据库。
        _context.Entry(d).Reference(p => p.Administrator).Load();
        if (d.Courses == null)
        {
            continue;
        }
        foreach (var c in d.Courses)
        {
            courseList.Add(d.Name + "_" + c.Title);
        }
    }
    return Json(courseList);
}

延迟加载（Lazy Loading）

首次读取实体时，不检索相关数据。然而，首次尝试访问导航属性时，会自动检索导航属性所需的数据。每次首次尝试从导航属性获取数据时，都向数据库发送查询。
ps：延迟加载一般都是导航属性结合 virtual 修饰来开启。目前 2.1 preview 有延迟加载功能，

更新

RowVersion 并发控制

1.
Timestamp 属性指定此列将包含在发送到数据库的 Update 和 Delete 命令的 Where 子句中。该属性称为 Timestamp。
如果更愿意使用 Fluent API，可使用 IsConcurrencyToken 方法指定跟踪属性，如下例所示：

1
2
modelBuilder.Entity<Department>()
.Property(p => p.RowVersion).IsConcurrencyToken();


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ContosoUniversity.Models
{
    public class Department
    {
        public int DepartmentID { get; set; }

        [StringLength(50, MinimumLength = 3)]
        public string Name { get; set; }

        [DataType(DataType.Currency)]
        [Column(TypeName = "money")]
        public decimal Budget { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        [Display(Name = "Start Date")]
        public DateTime StartDate { get; set; }

        public int? InstructorID { get; set; }

        [Timestamp]
        public byte[] RowVersion { get; set; }

        public Instructor Administrator { get; set; }
        public ICollection<Course> Courses { get; set; }
    }
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(int? id, byte[] rowVersion)
{
    if (id == null)
    {
        return NotFound();
    }

    //先查询
    var departmentToUpdate = await _context.Departments.Include(i => i.Administrator).SingleOrDefaultAsync(m => m.DepartmentID == id);

    if (departmentToUpdate == null)
    {
        //以下两行代码目的是，将新建deletedDepartment实例，并将post上来的form表单数据一一赋值到这个实体中，最终在视图显示
        Department deletedDepartment = new Department();
        await TryUpdateModelAsync(deletedDepartment);

        ModelState.AddModelError(string.Empty,
            "Unable to save changes. The department was deleted by another user.");
        ViewData["InstructorID"] = new SelectList(_context.Instructors, "ID", "FullName", deletedDepartment.InstructorID);

        return View(deletedDepartment);
    }

    //至关重要：当 Entity Framework 创建 SQL UPDATE 命令时，该命令将包含一个 WHERE 子句，用于查找具有原始值，并且该字段设置了[Timestamp]。
    _context.Entry(departmentToUpdate).Property("RowVersion").OriginalValue = rowVersion;

    if (await TryUpdateModelAsync<Department>(
        departmentToUpdate,
        "",
        s => s.Name, s => s.StartDate, s => s.Budget, s => s.InstructorID))
    {
        try
        {
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }
        catch (DbUpdateConcurrencyException ex)
        {
            //获取受影响的 `Department` 实体
            var exceptionEntry = ex.Entries.Single();

            //获取用户输入的新值
            var clientValues = (Department)exceptionEntry.Entity;

            //获取当前的数据库值
            var databaseEntry = exceptionEntry.GetDatabaseValues();

            if (databaseEntry == null)
            {
                ModelState.AddModelError(string.Empty,
                    "Unable to save changes. The department was deleted by another user.");
            }
            else
            {
                var databaseValues = (Department)databaseEntry.ToObject();

                if (databaseValues.Name != clientValues.Name)
                {
                    ModelState.AddModelError("Name", $"Current value: {databaseValues.Name}");
                }
                if (databaseValues.Budget != clientValues.Budget)
                {
                    ModelState.AddModelError("Budget", $"Current value: {databaseValues.Budget:c}");
                }
                if (databaseValues.StartDate != clientValues.StartDate)
                {
                    ModelState.AddModelError("StartDate", $"Current value: {databaseValues.StartDate:d}");
                }
                if (databaseValues.InstructorID != clientValues.InstructorID)
                {
                    Instructor databaseInstructor = await _context.Instructors.SingleOrDefaultAsync(i => i.ID == databaseValues.InstructorID);
                    ModelState.AddModelError("InstructorID", $"Current value: {databaseInstructor?.FullName}");
                }

                ModelState.AddModelError(string.Empty, "The record you attempted to edit "
                        + "was modified by another user after you got the original value. The "
                        + "edit operation was canceled and the current values in the database "
                        + "have been displayed. If you still want to edit this record, click "
                        + "the Save button again. Otherwise click the Back to List hyperlink.");

                //目的是忽略重新加载之前的并发冲突 
                departmentToUpdate.RowVersion = (byte[])databaseValues.RowVersion;
                ModelState.Remove("RowVersion");
            }
        }
    }
    ViewData["InstructorID"] = new SelectList(_context.Instructors, "ID", "FullName", departmentToUpdate.InstructorID);
    return View(departmentToUpdate);
}