How to retrieve child data, via LINQ Include, in one query, to improve performance?

entity-framework entity-framework-6 linq linq-to-entities

Accepted Answer

There is possibly a limit on how many levels one can use "Include"?

It exists! As I explained here about the generated SQL statement -

  • there are 17-zzz in theSELECT is the total of all columns in all relevant tables.
  • The total number of records in included kid collections is rows.

That might be a huge (long and broad) result set that the database returned. In addition, the query optimizer of the database engine has trouble coming up with effective query plans. It is not unexpected that the command times out since the database will have a difficult time processing all the data.

The substitute

Alternatively, you might load data in sections. However, it's simpler said than done. You do, in a sense, already load the data in pieces, but the chunks are too tiny and the queries are too many (yes, N + 1). The pieces need to be bigger. There is no precise plan of action for doing it. Your table's structure and the volume of data will determine this. I'll attempt to guide you in the correct way, however.

5 levels down

Let's imagine for the sake of conciseness that the tables and relationships areA < B < C < D < E ("" denotes 1:n). The fundamental issue is something like

var query = As.Where(a => a.Property == value).ToList();

You don't want zzzz-80 zzzz, then.As , as that would be simple: you may as well load all kids as well.]

Consider that you canInclude the Bs with no issues, however that also includes theCs already becomes excessive. Thus, the question is:

var query = As.Where(a => a.Property == value)
              .Include(a => a.Bs).ToList();

both theCs , etc., ought to be loaded in data chunks.

Entity Framework has the useful function of automatically connecting all entities that are loaded into a context via a procedure called mending relationships. Then, if you load theCs the collections in each parent individuallyB The things will be filled. This makes loading the necessary data simple.Cs :

var cs = Cs.Where(c => c.B.A.Property == value).ToList();

(Assuming the back references are a part of your model as well)

No, unless you can safely exclude theDs We're almost done:

var cs = Cs.Where(c => c.B.A.Property == value)
           .Include(c => c.Ds).ToList();

The final level is then loaded by:

var es = Es.Where(e => e.D.C.B.A.Property == value).ToList();

The dots at this degree of nesting could seem frightening. It will build a four-join query. However, the significant distinction with 4Incudes Is that just now?E You query rows and columns. The search result doesn't blow out. Additionally, join execution is improved for database engines.

As a result, you now have some handles to use.Include Once you have a setup that works well, try levels and individual queries (enough).

Last but not least, remember to disable lazy loading. The collections are auto-populated by EF, yes, but they are not loaded. Accessing the collections will still result in N + 1 requests even if lazy loading is enabled.

2
5/23/2017 11:58:59 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow