Entity Framework - Table structure, navigating many descendants, etc

Question

Someone suggested to try asking this here instead of stackoverflow, so I'm taking their advice.

So I'm rewriting a dated application and trying to structure things the right way. I have a Category > Product > Part > Options basic structure, but there are multiple layers in each and I don't know how to simplify my data structure, and navigate children effectively.

I feel like it's a little complicated for e-commerce, but we have a multitude of product, part, and part options.

So just for kicks I tried to see if I could round up all the data from a top level category all the way down to the swatches for the different part options to see if an entire page could display the entire category/product line at once (I know not to do this in production). The immediate problem I ran into was including all the descendants in my LINQ queries, as there are several that require intermediary objects due to the extra columns in the relational tables. That's necessary, I understand, but it gets messy quickly as this is setup to have a potentially unlimited number of category/subcategory levels. For example:

IQueryable<Category> Categories = Context.CategoryHierarchies
    .Where(w => w.ParentCategoryId == null)
    .Where(w => w.Active == true)
    .OrderBy(o => o.Sort)
    .Select(s => s.Category)
    .Include(x => x.ParentCategories)
    .Include(x => x.SubCategories).ThenInclude(x => x.Category)
    .Include(x => x.CategoryProducts).ThenInclude(x => x.Products)
    .ThenInclude(x => x.ProductParts).ThenInclude(x => x.Part)
    .ThenInclude(x => x.PartStyles).ThenInclude(x => x.Swatch)

    // I can follow the chain down but will need a second one to 
    // get to other parts of the tree, such as product themes and the 
    // two other part options, and currently this is only for the
    // current category. Doing sub categories would be ideal, but that,
    // assuming there's never more than 2 sub-category levels, still 
    // triples the size of this stupid-large query.

    .Include(x => x.SubCategories).ThenInclude(x => x.Category)
    .Include(x => x.CategoryProducts).ThenInclude(x => x.Products)
    .ThenInclude(x => x.ProductParts).ThenInclude(x => x.Part)
    .ThenInclude(x => x.PartStyles).ThenInclude(x => x.Swatch)

    // etc..

I didn't do lambas on the includes to keep things shorter for the paste. Now obviously this could go on even longer as I didn't get into the part options, but from there, I would potentially need to have 3 more lines for each level of part option, right? Like:

.Include(x => x.SubCategories).ThenInclude(x => x.Category)
    .ThenInclude(x => x.CategoryProducts).ThenInclude(x => x.Product)
    .ThenInclude(x => x.ProductParts).ThenInclude(x => x.Part)
    .ThenInclude(x => x.PartMaterials).ThenInclude(x => x.Swatch))

.Include(x => x.SubCategories).ThenInclude(x => x.Category)
    .ThenInclude(x => x.CategoryProducts).ThenInclude(x => x.Product)
    .ThenInclude(x => x.ProductParts).ThenInclude(x => x.Part)
    .ThenInclude(x => x.PartFinishes).ThenInclude(x => x.Swatch))

All the way down. Yikes. So for my question, when I'm loading my ViewModels into a view, and I want to access categories, products, and potentially parts, is there a better way to do this? I have a view that does a foreach on each level that I can, but it starts getting tedious real fast. Do I just load them all as separate objects in the view model and access them directly, and populated through separate queries? I'm pretty new to this and would really appreciate anyone's suggestions.

I did see the .NET Core .ThenInclude() stuff, which does look helpful, but I wasn't completely sure it would clean things up that much. It's a lot of descending.

edit: put lambdas back in. Still not a full query, but readers can use their imaginations as to how large it could potentially be for my hypothetical.

Answer 1

I also work on an e-commerce platform that has tons of data in it. What I've found is that if you have more than a very basic query, write the query yourself. EF speeds up saving records and very basic selects, but as soon as you start doing something a bit complicated, EF will spit out thousands of lines of SQL for something you could have done yourself in 100. You are better off just trying to create the SQL for it and use Dapper or just create an object that you can map directly back to from the query. Use context.Database.SqlQuery(sql, parameters).ToList() and you'll be able to get exactly what you need. Though, naturally, it is going to be way more work on your part since you have to do all the joins manually and type that out, but if you need it to perform, then I would really recommend just typing it out. I've had calls go from 60 seconds down to < 4 by just doing it myself instead of relying on EF.