LINQ x Code First - Eager Loading

2015-01

LINQ x Entity Framework で単純なリレーションを持つモデルを走査する。

Employee (子, N) と Customer (親, 1) を Customer の値を条件に結合する場合の LINQ と、対応して発行された SQL を記録する。

オッカムの剃刀的には、メンバ Customer.Address は不要だが残してる。

エンティティ モデル

public class Customer // 親
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
    public string Address { get; set; }

    public ICollection<Employee> Employees { get; set; }
}

public class Employee // 子
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }

    public int? CustomerId { get; set; }
    public Customer Customer { get; set; }
}

エンティティの初期化

protected override void Seed(MyDbContext context)
{
    base.Seed(context);
    //
    // カスタマ Customers
    //
    var customers = new List<Customer>
    {
        new Customer { CustomerId = 11, CustomerName = "D GmbH", Address = "Deutschland" },
        new Customer { CustomerId = 12, CustomerName = "丸々株式会社", Address = "Japan" },
        new Customer { CustomerId = 13, CustomerName = "A Corporation", Address = "U.S.A." }
    };
    customers.ForEach(c => context.Customers.Add(c));
    //
    // 従業員 Employees
    //
    var emps = new List<Employee>
    {
        new Employee { EmployeeId = 1, EmployeeName = "田中太郎", CustomerId = 12 },
        new Employee { EmployeeId = 3, EmployeeName = "Wolfgang Abel", CustomerId = 11 },
        new Employee { EmployeeId = 4, EmployeeName = "Aileen Smith", CustomerId = 13 },
        new Employee { EmployeeId = 5, EmployeeName = "佐藤花子", CustomerId = 12 },
        new Employee { EmployeeId = 6, EmployeeName = "Tom Wilde" }
    };
    emps.ForEach(c => context.Employees.Add(c));
    context.SaveChanges();
}

子から親を参照

1. 親を Include

query = db.Employees
            .Include(e => e.Customer);

SELECT
  [Extent1].[EmployeeId] AS [EmployeeId],
  [Extent1].[EmployeeName] AS [EmployeeName],
  [Extent1].[CustomerId] AS [CustomerId],
  [Extent2].[CustomerId] AS [CustomerId1],
  [Extent2].[CustomerName] AS [CustomerName],
  [Extent2].[Address] AS [Address]
  FROM  [dbo].[Employees] AS [Extent1]
  LEFT OUTER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]

2. 親の条件で子を参照する

なぜか、INNER JOIN した後に LEFT JOIN している

query = db.Employees
    .Include(e => e.Customer)
    .Where(e => e.Customer.CustomerName == "丸々株式会社");

SELECT
  [Extent1].[EmployeeId] AS [EmployeeId],
  [Extent1].[EmployeeName] AS [EmployeeName],
  [Extent1].[CustomerId] AS [CustomerId],
  [Extent3].[CustomerId] AS [CustomerId1],
  [Extent3].[CustomerName] AS [CustomerName],
  [Extent3].[Address] AS [Address]
  FROM   [dbo].[Employees] AS [Extent1]
  INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
  LEFT OUTER JOIN [dbo].[Customers] AS [Extent3] ON [Extent1].[CustomerId] = [Extent3].[CustomerId]
  WHERE N'丸々株式会社' = [Extent2].[CustomerName]

2.1. INNER と LEFT JOIN されるのを嫌って Include やめてみる

指定した Customer の条件どおりに抽出されるものの、Employees.Customer は null になっている。

query = db.Employees
    .Where(e => e.Customer.CustomerName == "丸々株式会社");

SELECT
  [Extent1].[EmployeeId] AS [EmployeeId],
  [Extent1].[EmployeeName] AS [EmployeeName],
  [Extent1].[CustomerId] AS [CustomerId]
  FROM  [dbo].[Employees] AS [Extent1]
  INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
  WHERE N'丸々株式会社' = [Extent2].[CustomerName]

2.2 カスタマ名がほしいので、Where と Include の順番を入れ替えてみる

NG, カスタマ名は抽出できたが、やはり INNER と LEFT JOIN になってしまう。

query = db.Employees
    .Where(e => e.Customer.CustomerName == "丸々株式会社")
    .Include(e => e.Customer);

SELECT
  [Extent1].[EmployeeId] AS [EmployeeId],
  [Extent1].[EmployeeName] AS [EmployeeName],
  [Extent1].[CustomerId] AS [CustomerId],
  [Extent3].[CustomerId] AS [CustomerId1],
  [Extent3].[CustomerName] AS [CustomerName],
  [Extent3].[Address] AS [Address]
  FROM   [dbo].[Employees] AS [Extent1]
  INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
  LEFT OUTER JOIN [dbo].[Customers] AS [Extent3] ON [Extent1].[CustomerId] = [Extent3].[CustomerId]
  WHERE N'丸々株式会社' = [Extent2].[CustomerName]

2.3 クエリ構文で書いてみる。

期待に近い SQL に変換された。そうだ結合には join を使うんだったと思い出す。

ただ、匿名型の e (employee) と c (customer) に値は充填されているが employee.customer は null のままなので、クエリの後に設定してやる必要がある。

var q = from e in db.Employees
        join c in db.Customers on e.CustomerId equals c.CustomerId
        where c.CustomerName == "丸々株式会社"
        select new
        {
          e.EmployeeId, e.EmployeeName, e.CustomerId, c.CustomerName, c.Address
        };

SELECT
    [Extent1].[EmployeeId] AS [EmployeeId],
    [Extent1].[EmployeeName] AS [EmployeeName],
    [Extent1].[CustomerId] AS [CustomerId]
    FROM  [dbo].[Employees] AS [Extent1]
    INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
    WHERE N'丸々株式会社' = [Extent2].[CustomerName]

2.4 クエリが Employee を返すようにする

The entity cannot be constructed in a LINQ to Entities query

これで、クエリが Employee オブジェクトを構成して返してくれる。
最後の AsQueryable は文脈に応じて使用する。

var q = (from e in db.Employees
         join c in db.Customers on e.CustomerId equals c.CustomerId
         where c.CustomerName == "丸々株式会社"
         select new { e, c })
        .ToList()
        .Select(t =>
            new Employee
            {
                EmployeeId = t.e.EmployeeId,
                EmployeeName = t.e.EmployeeName,
                CustomerId = t.e.CustomerId,
                Customer = t.c
            }
        ).AsQueryable();

SELECT
    [Extent1].[EmployeeId] AS [EmployeeId],
    [Extent1].[EmployeeName] AS [EmployeeName],
    [Extent1].[CustomerId] AS [CustomerId],
    [Extent2].[CustomerId] AS [CustomerId1],
    [Extent2].[CustomerName] AS [CustomerName],
    [Extent2].[Address] AS [Address]
    FROM  [dbo].[Employees] AS [Extent1]
    INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]
    WHERE N'丸々株式会社' = [Extent2].[CustomerName]

2.5 2.4 と等価なメソッド構文

var q = db.Employees.Join(  // TOuter
        db.Customers,       // TInner
        e => e.CustomerId,  // TOuterKeySelector
        c => c.CustomerId,  // TInnerKeySelector
        (e, c) => new { e, c }
    )
    .Where(t => t.c.CustomerName == "丸々株式会社")
    .ToList()
    .Select(t =>
        new Employee
        {
            EmployeeId = t.e.EmployeeId,
            EmployeeName = t.e.EmployeeName,
            CustomerId = t.e.CustomerId,
            Customer = t.c
        }
    ).AsQueryable();