MVC Routing template to represent infinite self-re

I have a product category table to represent a hierarchical category structure, a typical Parent-Child relationship table in the database.

Fill it with Guitar Center's data as an example:

If you render them to a page with <ul> and <li>:

Texts in blue are the URLs I would like to generate. For any given category, the link consists of its slug and its parents' slugs.

Note that the example I listed only has 2 parent-child levels. In theory, with the self-referential structure, any child could have infinite parents.

Questions:

How to set up routing template to achieve that?
If the routing template is set up to support that, how to retrieve the leaf category? For example, from the URL categories/guitars/acoustic-guitars, I would like to retrieve acoustic-guitars as the leaf category, and able to get all products under that acoustic-guitars category. Note: I don't want manual parsing on the URL. Ideally it would be the best if the lead category is binded through model binding.

How to set up routing template to achieve that?

You can't. But you can drop to a lower level and make a data-driven IRouter implementation for CMS-style route management.

An Example: `CachedRoute<TPrimaryKey>`

Here is an example that tracks and caches a 1-1 mapping of primary key to URL. It is a generic class and I have tested that it works whether the primary key is int or Guid.

There is a pluggable piece that must be injected, ICachedRouteDataProvider where the query for the database can be implemented. You also need to supply the controller and action, so this route is generic enough to map multiple database queries to multiple action methods by using more than one instance.

public class CachedRoute<TPrimaryKey> : Microsoft.AspNetCore.Routing.IRouter
{
    private readonly string _controller;
    private readonly string _action;
    private readonly ICachedRouteDataProvider<TPrimaryKey> _dataProvider;
    private readonly IMemoryCache _cache;
    private readonly IRouter _target;
    private readonly string _cacheKey;
    private object _lock = new object();

    public CachedRoute(
        string controller,
        string action,
        ICachedRouteDataProvider<TPrimaryKey> dataProvider,
        IMemoryCache cache,
        IRouter target)
    {
        if (string.IsNullOrWhiteSpace(controller))
            throw new ArgumentNullException("controller");
        if (string.IsNullOrWhiteSpace(action))
            throw new ArgumentNullException("action");
        if (dataProvider == null)
            throw new ArgumentNullException("dataProvider");
        if (cache == null)
            throw new ArgumentNullException("cache");
        if (target == null)
            throw new ArgumentNullException("target");

        _controller = controller;
        _action = action;
        _dataProvider = dataProvider;
        _cache = cache;
        _target = target;

        // Set Defaults
        CacheTimeoutInSeconds = 900;
        _cacheKey = "__" + this.GetType().Name + "_GetPageList_" + _controller + "_" + _action;
    }

    public int CacheTimeoutInSeconds { get; set; }

    public async Task RouteAsync(RouteContext context)
    {
        var requestPath = context.HttpContext.Request.Path.Value;

        if (!string.IsNullOrEmpty(requestPath) && requestPath[0] == '/')
        {
            // Trim the leading slash
            requestPath = requestPath.Substring(1);
        }

        // Get the page id that matches.
        //If this returns false, that means the URI did not match
        if (!GetPageList(context.HttpContext).TryGetValue(requestPath, out TPrimaryKey id))
        {
            return;
        }

        //Invoke MVC controller/action
        var routeData = context.RouteData;

        // TODO: You might want to use the page object (from the database) to
        // get both the controller and action, and possibly even an area.
        // Alternatively, you could create a route for each table and hard-code
        // this information.
        routeData.Values["controller"] = _controller;
        routeData.Values["action"] = _action;

        // This will be the primary key of the database row.
        // It might be an integer or a GUID.
        routeData.Values["id"] = id;

        await _target.RouteAsync(context);
    }

    public VirtualPathData GetVirtualPath(VirtualPathContext context)
    {
        VirtualPathData result = null;

        if (TryFindMatch(GetPageList(context.HttpContext), context.Values, out string virtualPath))
        {
            result = new VirtualPathData(this, virtualPath);
        }

        return result;
    }

    private bool TryFindMatch(IDictionary<string, TPrimaryKey> pages, IDictionary<string, object> values, out string virtualPath)
    {
        virtualPath = string.Empty;
        TPrimaryKey id;

        if (!values.TryGetValue("id", out object idObj))
        {
            return false;
        }

        id = SafeConvert<TPrimaryKey>(idObj);
        values.TryGetValue("controller", out object controller);
        values.TryGetValue("action", out object action);

        // The logic here should be the inverse of the logic in 
        // RouteAsync(). So, we match the same controller, action, and id.
        // If we had additional route values there, we would take them all 
        // into consideration during this step.
        if (action.Equals(_action) && controller.Equals(_controller))
        {
            // The 'OrDefault' case returns the default value of the type you're 
            // iterating over. For value types, it will be a new instance of that type. 
            // Since KeyValuePair<TKey, TValue> is a value type (i.e. a struct), 
            // the 'OrDefault' case will not result in a null-reference exception. 
            // Since TKey here is string, the .Key of that new instance will be null.
            virtualPath = pages.FirstOrDefault(x => x.Value.Equals(id)).Key;
            if (!string.IsNullOrEmpty(virtualPath))
            {
                return true;
            }
        }
        return false;
    }

    private IDictionary<string, TPrimaryKey> GetPageList(HttpContext context)
    {
        if (!_cache.TryGetValue(_cacheKey, out IDictionary<string, TPrimaryKey> pages))
        {
            // Only allow one thread to poplate the data
            lock (_lock)
            {
                if (!_cache.TryGetValue(_cacheKey, out pages))
                {
                    pages = _dataProvider.GetPageToIdMap(context.RequestServices);

                    _cache.Set(_cacheKey, pages,
                        new MemoryCacheEntryOptions()
                        {
                            Priority = CacheItemPriority.NeverRemove,
                            AbsoluteExpirationRelativeToNow = TimeSpan.FromSeconds(this.CacheTimeoutInSeconds)
                        });
                }
            }
        }

        return pages;
    }

    private static T SafeConvert<T>(object obj)
    {
        if (typeof(T).Equals(typeof(Guid)))
        {
            if (obj.GetType() == typeof(string))
            {
                return (T)(object)new Guid(obj.ToString());
            }
            return (T)(object)Guid.Empty;
        }
        return (T)Convert.ChangeType(obj, typeof(T));
    }
}

CategoryCachedRouteDataProvider

Here we lookup the categories from the database and recursively join the slugs into a URL.

public interface ICachedRouteDataProvider<TPrimaryKey>
{
    IDictionary<string, TPrimaryKey> GetPageToIdMap(IServiceProvider serviceProvider);
}

public class CategoryCachedRouteDataProvider : ICachedRouteDataProvider<int>
{
    // NOTE: I wasn't able to figure out how to constructor inject ApplicationDbContext
    // because there doesn't seem to be a way to access the scoped services there,
    // so we are using a service locator here. If someone could let me know how
    // that is done in Startup.Configure() of .NET Core 2.0, please leave a comment.
    public IDictionary<string, int> GetPageToIdMap(IServiceProvider serviceProvider)
    {
        using (var dbContext = serviceProvider.GetRequiredService<ApplicationDbContext>())
        {
            // Query the categories so we can build all of the URLs client side
            var categories = dbContext.Categories.ToList();
            var scratch = new StringBuilder();

            return (from category in categories
                    select new KeyValuePair<string, int>(
                        GetUrl(category, categories, scratch),
                        category.CategoryId)
                    ).ToDictionary(pair => pair.Key, pair => pair.Value);
        }
    }

    private string GetUrl(Category category, IEnumerable<Category> categories, StringBuilder result)
    {
        result.Clear().Append(category.Slug);
        while ((category = categories.FirstOrDefault(c => c.CategoryId == category.ParentCategoryId)) != null)
        {
            result.Insert(0, string.Concat(category.Slug, "/"));
        }
        return result.ToString();
    }
}

CategoryController

There is nothing special going on in the controller, except for the fact that we don't need to deal with a URL or a slug at this point at all. We simply accept the id parameter that maps to the primary key of the record and then you know what to do from there...

public class CategoryController : Controller
{
    public IActionResult Index(int id)
    {
        // Lookup category based on id...

        return View();
    }
}

Usage

We configure this in Startup.cs as follows:

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

        services.AddMvc();

        services.AddSingleton<CategoryCachedRouteDataProvider>();
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
            app.UseBrowserLink();
        }
        else
        {
            app.UseExceptionHandler("/Home/Error");
        }

        app.UseStaticFiles();

        app.UseMvc(routes =>
        {
            routes.Routes.Add(
                new CachedRoute<int>(
                    controller: "Category",
                    action: "Index",
                    dataProvider: app.ApplicationServices.GetRequiredService<CategoryCachedRouteDataProvider>(),
                    cache: app.ApplicationServices.GetRequiredService<IMemoryCache>(),
                    target: routes.DefaultHandler)
                {
                    CacheTimeoutInSeconds = 900
                });

            routes.MapRoute(
                name: "default",
                template: "{controller=Home}/{action=Index}/{id?}");
        });
    }
}

Note that the CachedRoute<TPrimaryKey> can be reused to create additional routes for other tables. So, if you wanted to, you could also make your product URLs like guitars/acoustic-guitars/some-fancy-acoustic-guitar by using a join on the Category table and using a similar method to build the URL.

The URLs can be generated and added to the UI using Tag Helpers or any of the other UrlHelper based methods. For example:

<a asp-area="" asp-controller="Category" asp-action="Index" asp-route-id="12">Effects</a>

Which is generated as

<a href="/amps-and-effects/effects">Effects</a>

You can of course then use the primary key of the model to generate the URL and the text for the link - it is all automatic and straightforward using models that have the primary key and name.

The only thing extra you would need to do is to create the hierarchy for the link display. But that is outside of the scope of routing.

Note that there is no concept of hierarchy at all in routing - it is simply a list of routes that is matched from top to bottom on each request.

If the routing template is set up to support that, how to retrieve the leaf category? For example, from the URL /guitars/acoustic-guitars, I would like to retrieve acoustic-guitars as the leaf category, and able to get all products under that acoustic-guitars category. Note: I don't want manual parsing on the URL. Ideally it would be the best if the lead category is binded through model binding.

It is unclear why you would need a "leaf category" as this has nothing to do with incoming or outgoing routes, nor is it required to lookup the database data. Again, the primary key is all you need to generate the entire URL based on routing and it should be all that is required to view all of the products. But if you really need access to it you can look it up in your controller.

Customization

You may need to change the caching strategy depending on your specific requirements.

You may wish to use an LRU cache strategy with a fixed maximum number of links in RAM
You may wish to track how often the URLs are being hit and move the most frequently visited URLs to the top of the list
You may wish to share the cache between the routes and your update action methods so when URLs are successfully updated in the database, they are also updated in the cache at the same time for "real-time" URLs
You may wish to cache each URL individually and look them up one at a time instead of caching the whole list in one go