# Entity Framework - Table structure, navigating many descendants, etc

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.

• I didn't do lambas on the includes to keep things shorter for the paste. Please don't shorten your code. This usually goes sideways. – t3chb0t Aug 23 '17 at 15:05
• Yea you're right, added some more and took out the shortened lazy include statements that weren't helpful. – Scott O. Aug 23 '17 at 16:26