Split multiple linq include()

Question

How to split multiple linq include() methods for optimization query:

allRecords = context.Records.Where(r => r.versionId == version.id)
.Include(x => x.colors)
.Include(x => x.tags)
.Include(x => x.ranks)
.OrderBy(r => r.sortkey).ToList();

How can I optimize this query by breaking it down into simpler ones? This request is too slow. It is necessary to increase its perfomance.

I don't understand the question. _If you don't need the `Include`, remove it or comment it out._ — mjwills, Sep 19 '18 at 10:04
How can I optimize this query by breaking it down into simpler ones? — Mikhail Kostiuchenko, Sep 19 '18 at 10:07
The edit doesn't improve anything. If you want to retrieve colors, you want to retrieve colors. If you *don't* want them, don't use `Include()` — Panagiotis Kanavos, Sep 19 '18 at 10:07
@MichaelKostiuchenko `How can you optimize this query by breaking it down into simpler ones?` doesn't explain anything. Executing *more* queries is *more* complex and can be N times slower than one query that retrieves what you want. Explain what the *actual* problem is — Panagiotis Kanavos, Sep 19 '18 at 10:08
@MichaelKostiuchenko all functions in LINQ are just functions that take an IQueryable as a parameter and return a *new* one. That means you don't have to use all of them in a single chain. Each function returns a new IQueryable, which means you can store intermediate objects in variables, use conditional logic to call them etc. — Panagiotis Kanavos, Sep 19 '18 at 10:10
`This request is too slow. It is necessary to increase its perfomance.` that's *completely* different. Do you have proper indexes? What do the tables look like? What does the *execution plan* look like? What does slow mean? Loading the same stuff in 2 or 3 queries will take 2 or 3 times longer — Panagiotis Kanavos, Sep 19 '18 at 10:12
Please perform a SQL Trace and let us know what is the exact query being submitted to the database. — mjwills, Sep 19 '18 at 10:13
Are `VersionId` and `sortkey` covered by indexes? What about the fields involved in foreign key constraints? Do you really want to load all data for all those records at once or do you want specific fields only? — Panagiotis Kanavos, Sep 19 '18 at 10:14

score 1 · Answer 1 · answered Sep 19 '18 at 10:08

You can make the includes conditional, using e.g. some boolean flags to do so:

var query = context.Records.Where(r => r.versionId == version.id);

if (includeColors) query = query.Include(x => x.colors);
if (includeTags  ) query = query.Include(x => x.tags);
if (includeRanks ) query = query.Include(x => x.ranks);

allRecords = query.OrderBy(r => r.sortkey).ToList();

Split multiple linq include()

1 Answers1