How to perform skip and take in Linq with joining multiple tables in optimized way

2 hours ago 2

ARTICLE AD BOX

How to write a linq query that does the same as below SQL

select * from [dbo].[LitEdit] as le inner join [dbo].[LitEditJob] as lej on lej.BulkEditId = le.Id inner join [dbo].[LitEditJobTask] as job on job.litEditJobId = lej.Id where lej.LitProcessId = 'ABC002512' and job.State = 'init' order by job.LitNumber OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

I have a performance issue with the actual query because of large data in LitEditJobTasks table.

var retval = await context.LitEditJob .Include(j => j.litEdit) .ThenInclude(b => b.LitEditJobs) .ThenInclude(jb => jb.LitEditJobTasks) .Where(j => j.LitProcessId == "ABC002512") .FirstOrDefaultAsync(cancellationToken);

I have tried a few things such as

var jobs = await context.LitEditJob .Include(j => j.LitEdit) .ThenInclude(b => b.LitEditJobs) .Where(j => j.LitProcessId == "ABC002512") .SelectMany(j => j.LitEditJobTasks) .OrderBy(t => t.LitNumber) .Skip(10) .Take(10) .ToListAsync(cancellationToken);

But this only brings the data of LitEditJobTasks but not the other table