Entity Framework の遅延読み込み


Entity Framework でリレーションを張っているとき、リレーション先の読み込みは遅延されます。

例として、次のエンティティで話を進めます。

``` csharp
public class Context : DbContext
{
    public DbSet<Process> Processes { get; set; }
    public DbSet<Log> Logs { get; set; }
}

public class Process
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Log> Logs { get; set; }
}

public class Log
{
    public int Id { get; set; }
    public TraceLevel Level { get; set; }
    public string Message { get; set; }
    public int ProcessId { get; set; }
    public virtual Process Process { get; set; }
}
```

プロセス毎のログからLevelがErrorのログを最新100件まで表示するとします。


``` csharp
using (var db = new Context())
{
	foreach (var process in db.Processes)
	{
		Console.WriteLine($"--- {process.Name} ---");
        var logs = process.Logs
			.Where(l => l.Level == TraceLevel.Error)
			.OrderByDescending(l => l.Id)
			.Take(100);
		foreach (var log in logs)
		{
			Console.WriteLine(log.Message);
		}
	}
}
```

このコードを実行すると次のSQL文が発行されます。

``` sql
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Processes] AS [Extent1]
```

``` sql
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Level] AS [Level], 
    [Extent1].[Message] AS [Message], 
    [Extent1].[ProcessId] AS [ProcessId]
    FROM [dbo].[Logs] AS [Extent1]
    WHERE ([Extent1].[ProcessId] IS NOT NULL) AND ([Extent1].[ProcessId] = @EntityKeyValue1)
```

このように、単純にループを回すと最初にプロセスを取得するSELECT文と、各プロセス毎にログを取得するSELECT文が発行されます。
これは、ProcessのLogsを参照したときに、遅延読み込みが発生するからです。
よく見ると、取得しているログはプロセスが所有しているログを全件取得しています。
欲しいログはLevelがErrorの最新から100件分だけです。

Context の Logs から欲しいログを取得するように書き換えると解決します。

``` csharp
foreach (var process in db.Processes)
{
	Console.WriteLine($"--- {process.Name} ---");
    var logs = db.Logs
		.Where(_ => _.ProcessId == process.Id && _.Level == TraceLevel.Error)
		.OrderByDescending(_ => _.Id)
		.Take(100);
	foreach (var log in logs)
	{
		Console.WriteLine(log.Message);
	}
}
```

``` sql
SELECT TOP (100) 
    [Project1].[Id] AS [Id], 
    [Project1].[Level] AS [Level], 
    [Project1].[Message] AS [Message], 
    [Project1].[ProcessId] AS [ProcessId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Level] AS [Level], 
        [Extent1].[Message] AS [Message], 
        [Extent1].[ProcessId] AS [ProcessId]
        FROM [dbo].[Logs] AS [Extent1]
        WHERE ([Extent1].[ProcessId] = @p__linq__0) AND (1 = [Extent1].[Level])
    )  AS [Project1]
    ORDER BY [Project1].[Id] DESC
```

折角、Process に Logs プロパティを作成していますので、遅延読み込みを使用せずに Logs を取得するクエリをprocessのLogsから作成して実行するように書き換えてみます。

``` csharp
foreach (var process in db.Processes)
{
	Console.WriteLine($"--- {process.Name} ---");
    var logs = db.Entry(process)
		.Collection(_ => _.Logs)
		.Query()
		.Where(_ => _.Level == TraceLevel.Error)
		.OrderByDescending(_ => _.Id)
		.Take(100);
	foreach (var log in logs)
	{
		Console.WriteLine(log.Message);
	}
}
```

実行されるSQL文は先の Logs から検索した場合と同じSQL文が発行されます。

今度は遅延読み込みをやめて、プロセスを取得するときにログも併せて取得するようにしてみます。

``` csharp
foreach (var process in db.Processes.Include(_ => _.Logs))
{
	Console.WriteLine($"--- {process.Name} ---");
    var logs = process.Logs
		.Where(_ => _.Level == TraceLevel.Error)
		.OrderByDescending(_ => _.Id)
		.Take(100);
	foreach (var log in logs)
	{
		Console.WriteLine(log.Message);
	}
}
```

``` sql
SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Name] AS [Name], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[Level] AS [Level], 
    [Project1].[Message] AS [Message], 
    [Project1].[ProcessId] AS [ProcessId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[Level] AS [Level], 
        [Extent2].[Message] AS [Message], 
        [Extent2].[ProcessId] AS [ProcessId], 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Processes] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Logs] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ProcessId]
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC
```

発行されるSQL文が１回になりました。
でも、ログが全件取得されていますので、これを何とかしたいと思います。

``` csharp
var q = db.Processes
	.Select(p => new
	{
		Process = p,
		Logs = p.Logs
			.Where(l => l.Level == TraceLevel.Error)
			.OrderByDescending(l => l.Id)
			.Take(100)
	});
foreach (var item in q)
{
	Console.WriteLine($"--- {item.Process.Name} ---");
	foreach (var log in item.Logs)
	{
		Console.WriteLine(log.Message);
	}
}
```

プロセスと、そのプロセスが持つLevelがErrorな先頭100件のログをプロパティに持つ匿名オブジェクトを返すようにします。
発行されるSQL文は次のようになります。

``` sql
SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[Name] AS [Name], 
    [Project2].[C1] AS [C1], 
    [Project2].[Id1] AS [Id1], 
    [Project2].[Level] AS [Level], 
    [Project2].[Message] AS [Message], 
    [Project2].[ProcessId] AS [ProcessId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Limit1].[Id] AS [Id1], 
        [Limit1].[Level] AS [Level], 
        [Limit1].[Message] AS [Message], 
        [Limit1].[ProcessId] AS [ProcessId], 
        CASE WHEN ([Limit1].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Processes] AS [Extent1]
        OUTER APPLY  (SELECT TOP (100) [Project1].[Id] AS [Id], [Project1].[Level] AS [Level], [Project1].[Message] AS [Message], [Project1].[ProcessId] AS [ProcessId]
            FROM ( SELECT 
                [Extent2].[Id] AS [Id], 
                [Extent2].[Level] AS [Level], 
                [Extent2].[Message] AS [Message], 
                [Extent2].[ProcessId] AS [ProcessId]
                FROM [dbo].[Logs] AS [Extent2]
                WHERE ([Extent1].[Id] = [Extent2].[ProcessId]) AND (1 = [Extent2].[Level])
            )  AS [Project1]
            ORDER BY [Project1].[Id] DESC ) AS [Limit1]
    )  AS [Project2]
    ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC
```

今回の Process の Logs のような全件取得は行わないケースでは、virtual を外して遅延読み込みをさせないようにしようかなと思いました。