I'm trying to fetch a bunch of rows that ultimately get aggregated into a single object. Think something like this:
Root
/ \
/ \
/ \
L R
/ | \ / | \
A ... Z A ... Z
Originally the way I was writing this just did a bunch of .Includes and .ThenIncludes. Unfortunately the query was pretty complex and executed horribly which has led me to a situation where I do something essentially like:
async Task<Stuff> GetStuff(MyDbContext context)
{
var root = await context.Root.FirstOrDefaultAsync(r =>
r.Id == 1 &&
r.CreatedAt == someDateTime);
var leftSide = await context.Sides.FirstOrDefault(s =>
s.RootId == root.Id &&
s.CreatedAt == root.CreatedAt &&
s.Side == "left");
var rightSide = // similar to left but searching by right side.
var leftSideChildren = await context.SideChildren.Where(c =>
c.SideId == leftSide.SideId &&
c.CreatedAt == leftSide.CreatedAt &&
c.Side == "left");
var rightSideChildren = await context.SideChildren... // similar to leftSideChildren query
}
This method of getting data works better than attempting include it all, but it's still horrendously slow, taking around 10 seconds to fetch the structure. The data when it's completed isn't that bad, its a root object, a left and right side object, and then usually a couple children per side. There's some other included objects that I omitted from this description for brevity's sake.
As it stands right now it has to do a round trip query for every single call. I've timed the queries and they average around 600ms each, except the very last level which I've had to do some more complex querying on.
I'm curious if entity framework (or other libraries like EF Core Plus) has any smarts that I could employ here. I've been tinkering with EF Core Plus a bit on this but don't really understand what they're offering (I'm fairly new to working with ORMs). There was something about Futures that seemed like it could accomplish what I wanted, and I was hoping I could build up a future query that goes and gets the root level object, then make another future query that uses the value of root level future query. Sort of like if I had built up a SQL query that does this manually and assigns variables to use in the next query. Is there any such thing that exists in Entity Framework to achieve this so I can attempt to batch all my queries at once and only have to do one round trip call?