We've released v3.0 of our own forum / customer support system HnD! HnD is the system powering our own support forums and is doubling as a prime example of how to use LLBLGen Pro in a big application. It's fully open source (released under the GPL2 license) and freely available.

In this post we'll shed a light on the long journey HnD took to go from a netfx powered webforms system to a .net core 3.1, Asp.net Core MVC powered system and how it works internally. As the system is pretty big we can't go into all details but at least it should give you some ideas how we did some things and why. Please click on the images below for a full size view. All images are taken from inside the LLBLGen Pro designer of the HnD project.

A brief history

HnD started back in the mid 2000's as a replacement for a commercial Q&A system we were using that didn't work very well. We first called it 'TinyForum' as it was... a tiny little forum system, (you know we have a knack for naming things! ;) ), and after adding much needed specific support features we renamed it 'HnD' which stands for 'Help and Discuss'. It was a webforms based system, running LLBLGen Pro v2.6, using SelfServicing and we basically kept running that version in the period 2008-2020 as it worked fine.

After a while the design felt pretty dated: it wasn't responsive, the layout was pretty compact, so browsing on a mobile device was terrible, and on the backend, we didn't like it using SelfServicing as our recommended LLBLGen Pro template paradigm is Adapter. As a prime example how to use our system it fell a bit short. In 2015 we tried to rebuild it with Asp.net MVC. This turned out to be a bigger project than we anticipated. Converting to Adapter was pretty easy but converting the webforms to pure MVC wasn't. On top of that, we wanted to do more on the client so it required javascript code, something we weren't familiar with. After working on it from time to time, the project stranded with a 60% done MVC front end. We tried to revive it in 2017 but didn't succeed.

After the release of LLBLGen Pro v5.7, we decided we had to do it now or move to another forum as our old system was system too dated. As .net core is now mainstream it was an ideal opportunity to port the half-done MVC app to .net core 3.1, use Asp.net Core MVC and optimize things a bit across the board. The end result of this work is now live: HnD 3.0!

As the system has seen many winters, there are parts which haven't been touched in years while others are brand new. It's also the reason why for instance it uses Bootstrap 3 and not a newer version because the design we're using was based on Bootstrap 3 and migrating to a newer version would increase the workload. So, let's see how this system works!

High-level overview

The HnD system consists of the following projects. Generated projects are generated using LLBLGen Pro from a single project, which contains both an entity model and a derived model. The LLBLGen Pro project contains several Model Views to illustrate the relationship between the various elements in the entity model.

GuiCore

This is the UI project, built with Asp.net Core MVC.

MarkdownDeep

The Markdown parser used to convert posts to HTML. This version is an extended version of the MarkdownDeep parser of DocNet.

SD.HnD.BL

The layer GuiCore talks to and which performs all logic in the system. This is also the layer which performs data access.

SD.HnD.DALAdapter

The generated project which contains all entity and typed list classes, defined in the entity model in the LLBLGen Pro project.

SD.HnD.DALAdapterDBSpecific

The generated project which contains the DataAccessAdapter class and all metadata information regarding mappings as well as access methods for calling the stored procedure to initialize a new database.

SD.HnD.DTOs

The generated project which contains all DTO element classes, defined in the derived model 'DTOs' in the LLBLGen Pro project.

SD.HnD.DTOs.Persistence

The generated project which contains projections to project entity queries to DTO instances defined in SD.HnD.DTOs as well as updating an entity instance with the data in a DTO instance.

SD.HnD.DTOs.Utility

A small project that contains classes and functionality which are used across projects.

Entity model, general overview

Let's start by the abstract entity model of the system. We'll look at various parts of the entity model by looking at specific Model Views defined in the LLBLGen Pro Designer.

Content

First up is the model view which contains all content related entities:

The Sections contain forums, as illustrated in our own support system, per forum you have threads, and per thread you have posts. This is basic forum 101 design. Each post is posted by a user, each thread is started by a user, each user has zero or more bookmarks of threads, and each post can have zero or more attachments. The attachments are stored in a separate database file in SQL Server to keep file management easier.

All content is pre-generated to HTML when it's posted. This means that if you view a thread, the post content that's shown is the HTML equivalent of the markdown stored in the Message entities.

Security

Now lets look a bit deeper at security. A forum system with random users from the internet requires security at many levels.

HnD of course uses passwords for users which are salted, Pbkdf2 hashed. The older versions of HnD uses MD5 hashing and to avoid having the previous users reset their passwords when we moved to HnD 3.0, we migrated the MD5 hashes as if they were the plaintext passwords so HnD stores salted, Pbkdf2 hashed MD5 hashes of the original password. But security goes much further than that: who can see which forum, who can do what on any given thread? To solve that, HnD uses role-based security.

There are two sets of action rights: system action rights and role-forum combination action rights. System action rights are things like 'can perform security management'. These are defined when editing a role. Role-forum combination action rights are action rights defined on a role-forum relationship, so all users in the role 'Users' have a set of given rights for a given forum.

A user then inherits all action rights for all roles the user is in. These rights are cached for a logged in user and applied for every query done on the visiting user. This goes pretty far, as e.g. a search result might match a thread in a forum the user has no access rights to: the thread shouldn't be visible in the search results at all.

Support Queues.

HnD uses a concept called a 'support queue'. This is a queue defined by an admin to which a thread can be added, manually or automatically, and which can then be listed in a single place for the support team. This is an easy way to see which threads need attention. The following model view shows the entities involved:

Per forum the default support queue is defined so each new thread is automatically placed in that support queue. When a thread is marked as done by either the topic starter or someone who has the action right to do so, the thread is automatically removed from the support queue and when a new post is added to the thread, the thread is re-added to the support queue automatically. Manually adding/removing a thread to/from a support queue is of course also possible for the users who have that action right.

Thread statistics

When you browse a forum you'll see that at places the system will show you thread statistics: how many posts are in the thread, how many views it has, who posted the last post in the thread and when etc. This information isn't simple to obtain: it requires aggregating all posts in the system to determine the last posts for each thread in a forum. You also can't 'keep track of that' in the thread entity itself as it would create a cycle: the Message entity depends on the Thread entity and in that situation the Thread entity would also depend on the Message entity. To solve this, we added a separate entity, ThreadStatistics. This entity keeps track of the last message in a thread, the number of messages in a thread and the number of views. This is, in a way, denormalized data, but for a system like a forum, with many reads and occasional writes it's a good solution:

The system contains more entities than these of course, you can see for yourself by loading the LLBLGen Pro project into the LLBLGen Pro designer.

Typed Lists

HnD is a system that has many reads and occasional writes. This means that most data is consumed in a read-only fashion, often in flat-list format. LLBLGen Pro offers a feature called 'Typed List' which does exactly that: it defines a flat typed list over related data. Of course we can define these lists in code with a poco and some handwritten code, but the designer allows us to define these lists in a couple of clicks and generate the Linq or QuerySpec query for us to fetch the data at runtime. Let's look at one of these.

SearchResult

The Typed List 'SearchResult' is a flat list projection of a joined set which looks like the following:

First we join Message with Thread, then ThreadStatistics with Thread and Forum with Thread, Then Message again but over a different relationship, so it's aliased as 'LastMessage', and after that we join the Section to Forum. The end result is a set of data which is used to display a list of threads matching a search query. The following fields are present in the projection:

This Typed List is generated as a Poco class for the data and a QuerySpec query to formulate the joins. To use it, all we have to do is write a QuerySpec query:

// From https://github.com/SolutionsDesign/HnD/blob/master/BL/Searcher.cs#L93
var q = qf.GetSearchResultTypedList()
          .Where(searchTermFilter
                 .And(ForumFields.ForumID.In(forumIds))
                 .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userId)))
          .Limit(500)
          .OrderBy(CreateSearchSortClause(orderFirstElement))
          .Distinct();
if(orderSecondElement != orderFirstElement)
{
    // simply call OrderBy again, it will append the sortclause to the existing one.
    q.OrderBy(CreateSearchSortClause(orderSecondElement));
}

List<SearchResultRow> toReturn;
try
{
    // get the data from the db. 
    using(var adapter = new DataAccessAdapter())
    {
        toReturn = await adapter.FetchQueryAsync(q).ConfigureAwait(false);
    }
}
catch
{
    // probably an error with the search words / user error. Swallow for now, which will 
    // result in an empty resultset.
    toReturn = new List<SearchResultRow>();
}

It utilizes some helper methods to create the proper predicates which you can lookup in the sourcecode.

Derived Model: DTOs

HnD's development spans over 15 years. During that time a lot has changed in LLBLGen Pro and to utilize more powerful features we converted some of the data structures we had in older versions to derived elements in a Derived Model. Derived elements are (potentially) hierarchical projections of entity definitions, and can contain denormalized data. The LLBLGen Pro designer generates these derived elements as DTO classes plus queries/projections or as document definitions for a document database. HnD uses SQL Server so we're using the derived elements as DTOs.

One of the interesting derived elements, or DTOs, in the project is MessageInThread. MessageInThread is a hierarchical element which contains a User derived element and an Attachment derived element. Fetching a set of these elements will result in multiple objects which contain each these related elements. This is done this way and not as a flat list because a message can have multiple attachments, which would cause, in a joined, flat list potential duplicate data.

The DTOs are now fetched using 3 queries per page: one main query for the root element, which is the Message derived element, one query for the related User derived elements and one query for the Attachment related elements. These queries are fetched in an eager loading scenario, and this is transparent for the developer:

// From: https://github.com/SolutionsDesign/HnD/blob/master/BL/ThreadGuiHelper.cs#L183
public static async Task<List<MessageInThreadDto>> GetAllMessagesInThreadAsDTOsAsync(int threadId, int pageNo, int pageSize)
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = metaData.Message
                        .Where(m => m.ThreadID == threadId)
                        .OrderBy(m => m.PostingDate)
                        .TakePage(pageNo, pageSize);
        var messages = await q.ProjectToMessageInThreadDto().ToListAsync().ConfigureAwait(false);

        // update thread entity directly inside the DB with an update statement so the # of views is increased by one.
        var updater = new ThreadStatisticsEntity();
        // set the NumberOfViews field to an expression which increases it by 1
        updater.Fields[(int)ThreadStatisticsFieldIndex.NumberOfViews].ExpressionToApply = (ThreadStatisticsFields.NumberOfViews + 1);
        await adapter.UpdateEntitiesDirectlyAsync(updater, new RelationPredicateBucket(ThreadStatisticsFields.ThreadID == threadId)).ConfigureAwait(false);
        return messages;
    }
}

That's all the code needed to fetch all the information displayed when you view a thread of messages in a forum: message data, user data, attachments. It also updates the number of views in the statistics entity of the thread. The code above updates the value in the table row directly without fetching the entity first.

Derived models are a powerful feature of LLBLGen Pro, which allows you to define new elements based on the existing entity model for how you want to consume your data. You can define as much derived models as you want, and as they're derived from an existing abstract entity model, namely the one in the same project, the derived elements aren't based on loose sand: they have a theoretical basis and it's easy to keep them in sync with your entity model.

You also notice this query is using Linq, while we previously saw a query using QuerySpec, and the update query in the method above uses a low-level API expression. This is totally fine, you can mix these in your app, whatever feels better in that particular situation: sometimes you can easier express something using Linq, sometimes easier in another of the supported query APIs: they're all the same to the framework. Use what works for you, leave the heavy lifting to us.

System Architecture overview

Let's look a bit deeper in how the system architecture looks like. We can't go into every detail, but we'll illustrate some design decisions here and why some things look the way they are.

General aspects

• The whole system is async/await oriented, so most methods are asynchronous.
• The Asp.net Core MVC project uses explicitly mapped routes, not route attributes.
• Caching is used for some data that can be stale for a short period of time, like the list of forums per section on the landing page
• Data is mostly fetches as readonly projections. Entity fetches are only used if data has to be manipulated in a more complex fashion or for when editing them is rare or only done occasionally, like in admin pages.
• Entity data manipulation is done through direct deletes and updates when possible, so without fetching the entities first.
• Hierarchical deletes are done in code, nothing relies no cascading deletes/updates in the database.
• Search uses the SQL Server full text search functionality.
• If code is on a hot path, a query shouldn't pull more data than the consumer needs.
• All main code paths have been optimized using ORM Profiler to see which queries are executed and if they could be optimized

SD.HnD.BL project

Now, being an app originally designed in the mid-2000's, it bares the design philosphies (some would say 'scars') of that era and while tempting, we didn't rewrite the business logic layer just to meet some standard set by some people out there on the Internet. What we had in the SD.HnD.BL project worked very well so we kept it as-is. The basic gist is that all Create, Update and Delete related functionality is done in classes ending with Manager and all Read related functionality is done in classes ending with GuiHelper.

These are the historical aspects of old(er) software that has been migrated through the years. Nowadays we'd likely name things differently, but as the main goal is to have some direction of what is where, these names will do. So for instance, all functionality that fetches thread related data is in the ThreadGuiHelper class, and all functionality that manipulates Thread entity instances is in the ThreadManager class.

To be able to filter a set of threads based on role security, a central set of methods is used to formulate the project and join list for a query. These are ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStatsWithForumName and ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats. As the QuerySpec query API is flexible, the results of these are extended with more elements if needed for a particular situation. It's a way to re-use a critical part of functionality without running the risk of introducing a bug in one area which could lead to security being less tight.

There are a couple of poco classes defined in this project which could have been a Typed List. As this is an example of LLBLGen Pro we wanted to use this particular setup too, as it now illustrates how to use hand-written poco classes in projections. E.g.:

// We've to filter the list of attachments based on the forums accessable by the calling user, the list of forums the calling user has approval 
// rights on and by the forums on which the user can see other user's threads. We'll create a predicate expression for this, and will add 
// for each of these filters a separate predicate to this predicate expression and specify AND, so they all have to be true 
var q = qf.Create()
          .Select<AggregatedUnapprovedAttachmentRow>(ThreadFields.Subject,
                                                     ForumFields.ForumName,
                                                     MessageFields.MessageID,
                                                     AttachmentFields.AddedOn)
          .From(qf.Attachment
                  .InnerJoin(qf.Message).On(AttachmentFields.MessageID.Equal(MessageFields.MessageID))
                  .InnerJoin(qf.Thread).On(MessageFields.ThreadID.Equal(ThreadFields.ThreadID))
                  .InnerJoin(qf.Forum).On(ThreadFields.ForumID.Equal(ForumFields.ForumID)))
          .Where(MessageGuiHelper.CreateAttachmentFilter(accessableForums, forumsWithApprovalRight, forumsWithThreadsFromOthers, userId))
          .OrderBy(ForumFields.ForumName.Ascending(), AttachmentFields.AddedOn.Ascending())
          .Distinct();

using(var adapter = new DataAccessAdapter())
{
    return await adapter.FetchQueryAsync(q).ConfigureAwait(false);
}

which gets the data to list all messages with unapproved attachments, so they can be easily found. Administrators can see these in a simple list and approve them one at a time.

GuiCore project

For some the most interesting project of all: the Asp.net Core MVC project. It's a project that mainly focuses on rendering on the server, with some javascript based forms, but most javascript is ajax oriented, jquery based, and frankly not that exciting. The initial HnD used Session and Application objects throughout the code and although Asp.net core does support sessions, the Application object was gone. Some data in the system is just read once and barely changes, and the Application object was ideal for that.

In the GuiCore project we utilize a static object to store the read once data, and use a wrapper class for easy control over that. It's data that's read once, at startup, so nothing fancy. Data that is volatile is moved from the old Application object to the Asp.net Core cache. HnD has a handy wrapper for that called the CacheManager (yes we do love our managers ;) ).

To be able to work with the Asp.net Core session object we use a wrapper class called SessionAdapter which basically takes over the GlobalAsa oriented code of the webforms project. This adapter is also introduced to make it easier to migrate the old code to the new setup: e.g. previously we stored search results for someone in their session object. With the new HnD we wanted to get rid of that for obvious reasons so we store it now in the Asp.net core cache. The code interface to reach it is still the same: through the SessionAdapter. Sometimes when migrating to newer platforms you need to make compromises and refrain from rewriting everything.

Controllers are all asynchronous, and we followed the best practices for Asp.net Core MVC as much as possible. The controllers are grouping functionality by the kind of element they're working on, which is similar to what you see in the BL project.

So that's it, HnD in a small overview! If you want to contribute to this project, or perhaps use it for yourself, don't hesitate but go to the github repository and clone it. It's easy to make it your own with your own colors and logo.

Enjoy!