There is possibly a limit on how many levels one can use "Include"?
It exists! As I explained here about the generated SQL statement -
SELECTis the total of all columns in all relevant tables.
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.
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 are
("" 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 that would be simple: you may as well load all kids as well.]
Consider that you can
with no issues, however that also includes the
already becomes excessive. Thus, the question is:
var query = As.Where(a => a.Property == value) .Include(a => a.Bs).ToList();
, 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 the
the collections in each parent individually
The things will be filled. This makes loading the necessary data simple.
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 the
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 4
Is that just now?
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.
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.