In previous articles, we have seen how the entity framework works, how to create models for EF core, how to define relationships, etc. In this article, we will use all that information to demonstrate how a blog management database.

We will try to first understand the schema details, then we will create C# entities and then we will use DbContext to define tables. We will create these in a class library and then we will use this class library with .NET Core API in future posts.

Create a Class Library

Create a .NET Core class library and add all necessary NuGet packages. Please refer this blogpost for more details. We are going to use SQL Server as the target database for this demo.

Schema Design

We are considering blog management system, because most of us know what information blog stores. Generally bog management system has below entities and relationships.

• Every blogging site has multiple posts
• Every post has one or more categories assigned
• Every post has zero or more tags associated with it
• A tag can be associated with zero or more posts.
• Every post has some metadata associated with it, which can be used for setting page title, or SEO information.
• Every post can have zero or more comments.
• Every comment can have replies, nested up to 3 levels.

Optionally, blogging site may allow multiple users to contribute articles. But, for simplicity, we will not consider user table in this demo.

Now, let’s see what attributes should be present in the entities

• Category can have a title and description
• Tag can have a title and a description
• A post can have title, summary, and contents. Also, a post can be either in draft or published status.
• A comment can have contents, a parent comment and posted by user. Ideally, blog systems asks users to login via some identity provider to avoid spams and security attacks. For simplicity of demo, we would ignore this requirement. But if you want to take inspiration from here, make sure you consider performance and security requirement and get your design reviewed from that perspective.

We may also want to save some other attributes like when an entity was created or published or modified.

One other thing is, every blog management system uses a dynamic URL for every post. So for every post, we will consider creating one more property, ‘slug’, which is the dynamic URL for the post. Blog management systems also

For the sake of this article, we are not really interested in creating and saving the records. The only part covered here in this article, is how to setup the empty database.

EF Core Entities

We are going to use fully qualified relationship approach to define the entities. We are not going to use manual configurations approach , hence we do not need to override OnModelCreating method from DbContext. Instead, we need to make sure that appropriate properties are define in entities.

If we look at the data model, some properties are very straight forward, e.g. title, description, contents etc. Some properties are common to most of the entities. Those properties include Id, IsPublished, PublishedOn, CreatedOn, LastModifiedOn. We can create a BaseEntity to define these common properties and then derive other entities from this class.

But some advanced tricks would be required for defining relationships.

One To Many

Let’s consider relationship between posts and comment. It is one to many relationship, a post can have many comments. As we are using conventions, this relationship definition can be defined by:

• configuring a reference navigation property in Comment class
• configuring a collection navigation property in Post class

Many To Many

There are two many to many relationships in this design – post to categories and post to tags. In SQL tables, such relationship is represented by three tables – first entity table, second entity table and a relationship table. Each of such relationship can be defined by using below steps:

• define a new entity, let’s say PostTags
• define a primary key for relationship table. It can be either Id or it can be a composite key
• relationship entity should have a foreign key and reference navigation property for pointing to Post entity
• relationship entity should have a foreign key and reference navigation property for pointing to Tag entity
• a collection navigation property PostTags in Post class

Same steps can be applied to configure PostCategories relationship.

Rest of the model is pretty straight forward. Below code snippet shows all entities.

Implement the DbContext

In this case, we are not using any manual configurations and hence DbContext implementation is also very easy. We just need to define a constructor and DbSet properties for each of the entities.

Design Time Support

When a DbContext implementation does not have a default parameter-less public interface, generating migration is not easy. The EF Core tools would not be able to create instance of concrete DbContext (i.e. BlogContext) and hence they cannot generate migrations.

So, to facilitate creation of such DbContext, it is important to implement IDesignTimeDbContextFactory. Connection string is hardcoded here, but in real world applications you may want to read connection strings from some configuration store.

Apply Migrations

We are going to use dotnet CLI tools for creating and applying migrations. Please refer this post for more details about commands.

Verify

If migration is applied successfully, then we can open SSMS and check that all the tables are created as expected. Below is the ER diagram created from the generated database.

So, we have database and EF core models ready. In future posts, we will use this design to demonstrate how to perform CRUD operations.

I hope you find this information useful. Let me know your thoughts.