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();