# Retrieving website categories from a three-level hierarchy

I've been trying to fix this performance bottleneck in my web app for a while now.

Here is what the data looks like on my web page:

Here is how I get this data in the controller:

var allCategories = categoryRepository
.Categories
.Select(x => new
{
x.CategoryID,
x.FriendlyName,
x.RoutingName,
x.ParentID
})
.ToList();

var allListings = listingRepository
.Listings
.Where(x => x.Lister.Status != cancelledStatus)
.Select(x => new
{
x.SelectedCategoryOneID,
x.SelectedCategoryTwoID,
x.SelectedCategoryThreeID,
})
.ToList();

List<CategoryBrowseIndexViewModel> categories =
allCategories
.Where(x => x.ParentID == null)
.Select(a => new CategoryBrowseIndexViewModel
{
CategoryID = a.CategoryID,
FriendlyName = a.FriendlyName,
RoutingName = a.RoutingName,
ListingCount = allListings
.Where(x => x.SelectedCategoryOneID == a.CategoryID)
.Count(),

BrowseCategoriesLevelTwoViewModels =
allCategories
.Where(x => x.ParentID == a.CategoryID)
.Select(b => new BrowseCategoriesLevelTwoViewModel
{
CategoryID = b.CategoryID,
FriendlyName = b.FriendlyName,
RoutingName = b.RoutingName,
ParentRoutingName = a.RoutingName,
ListingCount = allListings
.Where(x => x.SelectedCategoryTwoID == b.CategoryID)
.Count(),

BrowseCategoriesLevelThreeViewModels =
allCategories
.Where(x => x.ParentID == b.CategoryID)
.Select(c => new BrowseCategoriesLevelThreeViewModel
{
CategoryID = c.CategoryID,
FriendlyName = c.FriendlyName,
RoutingName = c.RoutingName,
ParentRoutingName = b.RoutingName,
ParentParentID = a.CategoryID,
ParentParentRoutingName = a.RoutingName,
ListingCount = allListings
.Where(x => x.SelectedCategoryThreeID == c.CategoryID)
.Count()
})
.OrderBy(x => x.FriendlyName)
.ToList()
})
.OrderBy(x => x.FriendlyName)
.ToList()
})
.OrderBy(x => x.FriendlyName == jobVacanciesFriendlyName)
.ThenBy(x => x.FriendlyName == servicesLabourHireFriendlyName)
.ThenBy(x => x.FriendlyName == goodsEquipmentFriendlyName)
.ToList();


I make two database calls to get what I need into memory, and construct the data model in memory. I use nested foreach loops in the view to display the categories and the listing counts.

What I want to do is make one database call, have the database do all the work and send back the entire object model, in one go. Can this be done?

My repositories use IQueryable, like this:

public class EFListingRepository : IListingRepository
{
private EFDbContext context = new EFDbContext();

IQueryable<Listing> Listings { get { return context.Listings; } }
}


To do one database call I guess I would have to use one dbContext, like this:

EFDbContext browseControllerDbContext = new EFDbContext();

var allCategories = browseControllerDbContext
.Categories
.Select(x => new
{
x.CategoryID,
x.FriendlyName,
x.RoutingName,
x.ParentID
});

var allListings = browseControllerDbContext
.Listings
.Where(x => x.Lister.Status != cancelledStatus)
.Select(x => new
{
x.SelectedCategoryOneID,
x.SelectedCategoryTwoID,
x.SelectedCategoryThreeID,
});


But I get stuck at this point. How do I make a single call to the database to populate the List<CategoryBrowseIndexViewModel> categories variable, and have everything inside it loaded into memory in one go, ready to be looped through?

The Category entity looks like this:

public class Category
{
public long CategoryID { get; set; }

public string FriendlyKey { get; set; }

public string FriendlyName { get; set; }

public string RoutingKey { get; set; }

public string RoutingName { get; set; }

public long? ParentID { get; set; }
}


The Listing entity is similar in that it's just a (large) class with simple properties, as above. Category and Listing are not related by navigation properties or a foreign key.

Here are the viewmodels, which are nested. They're looped over with nested foreach's in the view:

The CategoryBrowseIndexViewModel looks like this:

public class CategoryBrowseIndexViewModel
{
public List<BrowseCategoriesLevelTwoViewModel> BrowseCategoriesLevelTwoViewModels { get; set; }

public long CategoryID { get; set; }

public string FriendlyName { get; set; }

public string RoutingName { get; set; }

public string ParentRoutingName { get; set; }

public long? ParentID { get; set; }

public long ListingCount { get; set; }
}


And BrowseCategoriesLevelTwoViewModel

public class BrowseCategoriesLevelTwoViewModel
{
public List<BrowseCategoriesLevelThreeViewModel> BrowseCategoriesLevelThreeViewModels { get; set; }

public long CategoryID { get; set; }

public string FriendlyName { get; set; }

public string RoutingName { get; set; }

public string ParentRoutingName { get; set; }

public long? ParentID { get; set; }

public long ListingCount { get; set; }
}


BrowseCategoriesLevelThreeViewModel

public class BrowseCategoriesLevelThreeViewModel
{
public long CategoryID { get; set; }

public string FriendlyName { get; set; }

public string RoutingName { get; set; }

public string ParentRoutingName { get; set; }

public long? ParentID { get; set; }

public long? ParentParentID { get; set; }

public string ParentParentRoutingName { get; set; }

public long ListingCount { get; set; }
}


EDIT: After testing how long everything was taking using Stopwatch, from the time the controller was hit to the end of the view, the main source of slowness, unbelievably, was Html.ActionLink() in a foreach loop. Hardcoding the <a> tag sped up this page by about 3-4 seconds. I couldn't believe how slow the Html helpers are! So I guess the lesson is don't assume the database is the bottleneck! I've checked all the other loops in my codebase. In the controller loops I've been careful not to make repeated database calls, for example I was initially using IEnumerable instead of IQueryable without understanding how it worked. I understand immediate execution in linq code (Count, Average etc. ) and how it works in IQueryable. Navigation properties can make repeated calls if you're not careful, even if a list of the main entity has been loaded into memory. Thank you everyone, I've learned alot from your suggestions.

• Welcome to CR! "Snippetized" code works well for Stack Overflow, but much less so for Code Review. I'd suggest you include the actual repository class(es) and, at least, the actual method (that's a controller method, right?) that's doing this.. query. I suppose your database has all the necessary indexes and that you've profiled the EF-generated SQL? Nov 8, 2016 at 0:27
• @Mat'sMug, thanks, I've added more detail. The database is in Azure. The main thing I'm trying to find out is can I do this query in the database entirely? I don't know how to do that. (I'm assuming it will be faster.) Nov 8, 2016 at 0:55
• @nmit026 not necessarily terrifying - it provides us more context and this is a good thing ;-) without the data models the queries are often extremely hard to understand. Nov 8, 2016 at 5:07
• Did you try what happens if you use the IQueryable versions of allCategories and allListings in the categories query? That should be enough to get it all in one query (if you use one context instance!). Not that that will necessarily be faster. It's easy enough to generate queries that are too large to run efficiently. Nov 8, 2016 at 19:33
• You can simply log the SQL statements by the DbContext.Database.Log property. Nov 8, 2016 at 22:01

# Categories

I suggest changing this design before it's to late. Having a table (?) Listing with separate columns for each category (SelectedCategoryOneID, SelectedCategoryTwoID, SelectedCategoryThreeID) is a very bad idea. What if you introduce a new category? You need to change the database model.

The categories build a tree. This means you don't have to store each category id with a listing. You know the parents so they are automatically selected if any child category was chosen.

I find you can keep it really simple. For the sake of the example I removed and renamed some properties. It should just demonstrate the sample solution - not your final one so I used simplified objects.

public class Category
{
public long Id { get; set; }

public long? ParentId { get; set; }

public string Name { get; set; }
}

public class Listing
{
public long CategoryId { get; set; }
}


You now want to list all categories together with their listing-counts. If you keep it simple you can achieve this with a join.

I assume you have these two tables:

var categories = new[]
{
new Category { Id = 1, ParentId = null, Name = "Foo" },
new Category { Id = 2, ParentId = 1, Name = "Bar" },
new Category { Id = 3, ParentId = 1, Name = "Baz" },
new Category { Id = 4, ParentId = 3, Name = "Qux" },
new Category { Id = 5, ParentId = 4, Name = "Waldo" },
new Category { Id = 6, ParentId = 4, Name = "Corge" },
};

var listings = new[]
{
new Listing { CategoryId = 1 },
new Listing { CategoryId = 1 },
new Listing { CategoryId = 2 },
new Listing { CategoryId = 2 },
new Listing { CategoryId = 2 },
new Listing { CategoryId = 3 },
new Listing { CategoryId = 4 },
new Listing { CategoryId = 4 },
new Listing { CategoryId = 4 },
new Listing { CategoryId = 4 },
new Listing { CategoryId = 5 },
};


You can join them on the category-id:

var cats =
from c in categories
join l in listings on c.Id equals l.CategoryId into catListings
select new
{
c,
ListingCount = catListings.Count()
};


This will give you a list of categories with the respecitve listing-counts:

# ViewModels

What about the views? You don't need three of them. I guess you have three categories. You add one category and you need to create a new view for it and adjust all your queries - a lot of work for a small change.

I find you should have only one view-model with a single view that can recursively (or with a stack) display all categories. You start with the ones that have no parents and and get then the ones that have this one as their parent and so one until you have displayed them all.

A new type for the results:

class CategoryListing
{
public Category Category { get; set; }
public int ListingCount { get; set; }
}


var cats =
from c in categories
join l in listings on c.Id equals l.CategoryId into catListings
select new CategoryListing
{
Category = c,
ListingCount = catListings.Count()
};


Print results:

static void PrintCategories(IEnumerable<CategoryListing> categories)
{
var rootCategories = categories.Where(c => !c.Category.ParentId.HasValue);

var cats = new Stack<CategoryListing>(rootCategories);
var depths = new Stack<int>();
depths.Push(0);

while (cats.Any())
{
var cat = cats.Pop();
var catDepth = depths.Any() ? depths.Pop() : 0;

Console.WriteLine(\$"{new string('-', catDepth)}{cat.Category.Name} ({cat.ListingCount})");

var childCats = categories.Where(c => c.Category.ParentId == cat.Category.Id);
foreach (var childCat in childCats)
{
if (childCat.Category.ParentId.HasValue)
{
cats.Push(childCat);
depths.Push(catDepth + 1);
}
}
}
}


And these are the results:

Foo (2)
-Baz (1)
--Qux (4)
---Corge (0)
---Waldo (1)
-Bar (3)

• Very good code, thanks! Love the stack. Naming things is hard: I've changed SelectedCategoryOneID to LevelOneSelectedCategoryID, because there are three levels in the category hierarchy. This is unlikely to change. If I add a fourth level, then yes a database migration will be required. Each listing is required to select categories to the lowest level in the hierarchy. If I add another top-level category (three so far), no changes are necessary. I can do things like: IQueryable<Listing> listings = listingRepository.Listings.Where(x => x.LevelTwoSelectedCategoryID == categoryTwo.CategoryID); Dec 2, 2016 at 0:17
• I didn't want to store a category ID for each of the three levels required in the listing entity. But I thought it would be faster and just generally easier to work with than storing the category ID of the lowest level and then looking up the parent ID of that for the second level and then the parent of the parent, just to pull back all the listings that are in a level 1 or 2 category (as I do elsewhere). I thought the code snippet in the comment above was short and easy, fast, and the design unlikely to change. Storing two extra fields seemed trivial. What would be faster? Thank you! Dec 2, 2016 at 0:32
• Actually, should be SelectedLevelOneCategoryID, SelectedLevelTwoCategoryID etc. or maybe just LevelOneCategoryID Dec 2, 2016 at 1:09