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.
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
- Tag can have a
- A post can have
contents. Also, a post can be either in
- A comment can have
posted byuser. 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
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
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
reference navigation propertyfor pointing to Post entity
- relationship entity should have a
reference navigation propertyfor pointing to Tag entity
collection navigation propertyPostTags 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.
We are going to use dotnet CLI tools for creating and applying migrations. Please refer this post for more details about commands.
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.