We're using the EF Core to handle the relations and accesss our datas. Actually we have a data structure with several levels as following:
Public Class Case
{
public GUID ID;
public GUID ParentID;
public virtual ICollection<Case> LinkedCases;
public virtual ICollection<Event> Events { get; set; }
}
Public Class Event
{
some properties
}
And then in my Entity framework AppDbContext, I declared following:
public DbSet<Case> Cases;
public DbSet<Event> Events;
We have to launch several times the search to load all the data. I'm currently working on the Performance test of different loading ways:
Eager loading
- This has good performance during the search of the 1/2 level.
- But we will have cartesian explosions during the search after the 3 level because of the "left join" several times with the same schema.
- The good point is that this has the least number of queries so the least time during the communication between our application server and DB server.
Explicit loading I have updated a little how it works, so basically we will load the first level, then the second level from the first level, then the third level from the second level.
- The good point is that I definitly avoid the cartesian explision, I have a DB query duration stable.
- But the bad point is that I have more queries, which means more communications, which had a significant impaction the performance.
So I'm thinking a way how I can reduce the number of queries and also avoiding too many joints between the schemas.
Dynamic ways
In this way, I will firstly search the root case by the ID provided in dbContext.Cases.
Then I will search all the case.ParentId = Id and get their IDs.
And then I will continue the search to find all the case.ParentId existed in the IDs and load them in dbContext.Cases.
In this way, I successfully reduce the number of queries and avoid too many joins issues.
Since the datas are loaded in the DbSet rather than the navigation properties, then I think about that I should create the binding between the case and their LinkedCases. But I found they are already binded.
Question I think there should be some functions which can handle this but I'm little confused how the navigation properties are binded automatically when they're loaded independantly, I tried to search in the Entity Framework forum, the official site, but didn't find too much helpful information.
Can you please help?