c# - Entity Framework - 错误地执行 2 个选择语句而不是连接

Question

我有一个相当简单的(代码优先)模型:

员工

[Table("vEmployee")] //note v - it's a view
public class Employee
{
    [Key]
    public int EmployeeNumber { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
}

员工假期权利

[Table("tblEmployeeHolidayEntitlement")]
public class EmployeeHolidayEntitlement
{
    [Key]
    public int EmployeeNumber { get; set; }

    public virtual Employee Employee { get; set; }

    public decimal StandardEntitlement { get; set; }

    //.....omitted for brevity
}

注意EmployeeHolidayEntitlement映射到表，Employee映射到 View

在构建上下文时，我会:
(不确定这是否正确!)

modelBuilder.Entity<Employee>()
    .HasOptional(x => x.HolidayEntitlement)
    .WithRequired(x => x.Employee);

现在，当我查询时，像这样:

var db = new ApiContext();
var result = db.Employees.ToList();

它很慢。

如果我查看 SQL 事件探查器，我可以看到我执行了很多语句(每个员工记录一个)而不是一个语句(加入 vEmployee 和 tblEmployeeHolidayEntitlement)- 例如:

首先，它从vEmployee中选择

SELECT 
    [Extent1].[id] AS [EmployeeNumber], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    FROM [dbo].[vEmployee] AS [Extent1]

然后每条记录返回其中一个

exec sp_executesql N'SELECT 
    [Extent1].[EmployeeNumber] AS [EmployeeNumber], 
    [Extent1].[StandardEntitlement] AS [StandardEntitlement]
    FROM [dbo].[tblEmployeeHolidayEntitlement] AS [Extent1]
    WHERE [Extent1].[EmployeeNumber] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=175219

这对我来说似乎不对-

我认为它应该做一些更像 LEFT JOIN 的事情

SELECT *
  FROM [dbo].[vEmployee] employee
  LEFT JOIN
  [dbo].[tblEmployeeHolidayEntitlement employeeEntitlement
  ON
  employee.id = employeeEntitlement.employeenumber

Answer 1

您必须使用 Include 方法，例如 db.Employees.Include(e => e.HolidayEntitlement).ToList()。如果您不这样做并且访问该属性，您将触发延迟加载。这就是发生在你身上的事情。

For more information check the documentation在加载。缺点是，如果它总是加入你的整个对象图，它会慢得令人无法接受。