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

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

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件まで表示するとします。

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文が発行されます。

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

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 から欲しいログを取得するように書き換えると解決します。

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

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から作成して実行するように書き換えてみます。

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文が発行されます。

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

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

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文が１回になりました。
でも、ログが全件取得されていますので、これを何とかしたいと思います。

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文は次のようになります。

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 を外して遅延読み込みをさせないようにしようかなと思いました。