Retrieving website categories from a three-level hierarchy

Question

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.

Answer 1

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; }
}

Adjusted query:

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)