If you have followed my previous article in this series, we already have setup API, Business and Data access layers for a blog application. This blog application uses entity framework core code first approach. The article demonstrated how to save entities, but we have not yet saved and retrieved the related data (using foreign key relationships).

In this article, let’s have a look at how related data can be saved and retrieved.

Post and Related Data

The database entities might share some relationships with one another. This relationship is represented by a foreign key constraint. The relationship can be required or optional – indicating whether the child record presence is mandatory or not. The relation ship can be one to one or one to may or many to many.

In case of blog application, the Post entity has relationships with Comment, Category and Tag.

• A Post can have one or more than one categories associated
• A Post can have zero or more tags
• A Post can have zero or more comments
• Tags or Categories can be created beforehand, so a tag or category does not always need to be associated with a Post.

Design Considerations – Saving

When it comes to saving related data, there are some questions which need to be answered before.

An association cannot exist if any one of the two – a parent row and a child row – does not exist. Sometimes, an association is saved while creating child entities. For example, in an existing post, when you add a tag, you can either add an existing tag or you can add a non-existing tag. If existing tag is added, then only association would be saved to database. If non-existing tag is added, then a new tag is created and association is saved to database as a part of same operation.

So you need to know whether an operation should save associations only or whether an operation would save relationship and a new record or whether it is mix of both.

Similarly, another design consideration is to know what happens when a relationship is deleted. So, in our case, let’s say a post is deleted, do we want to delete associated tags and categories ? This would decide how the database schema should be defined. Sometimes, databases may choose to not have integrity constraints and integrity handling is completely done by the application code which interacts with database. It is more common if application is using NoSQL data stores.

In case of the entity Post, we may not want to delete the tags and categories associated with a post as those can be associated with other posts too. But deleting a post should also delete all the comments associated with it – as comments are tied to one post only.

Design Considerations – Reading

An entity can be probably read and shown on multiple pages. e.g. there can be a page listing all tags (IDs and Titles) and when clicked on view details button, it would show all properties of a tag on detail screen. Also, a post page might be interested in showing only tag title.

So depending on this, the models defined in API should be defined. Sometimes a single model might be required on all the screens. But some applications may choose to have different models for different purposes even though underlying entity is same. This might be because the application want to return only the data which is required by the caller. For example, blog page might need just ID and title of a tag. But tag details page may need ID, title, description and all other properties too.

It is generally good practice to design API models based on UI requirements as that would optimize the bandwidth usage. Depending on application architecture, some of these models can be shared as well to reduce code duplication.

Coming back to code

We already have defined all database entities appropriately to support relationships. But the API models – which are used by clients to interact with API – are not refined yet. We just copied all data entities and created same structure for API models.

So, let’s refine the API model.

Dates – Default Values

First things first, currently API models are exposing CreatedOn, LastModifiedOn, PublishedOn dates to clients. Ideally these dates are just a metadata which system can generate on its own and ideally it should not rely on callers for such data. Hence,

• Let’s set default values for those column in database entities (defined in Blog.Data.EF).
• Once default values for dates are defined, let’s remove those properties from API BaseModel.

If you make these three datetime fields as nullable (as DateTime?), then the database schema will allow NULLs. If you specify DateTime type for these columns, they would make columns not null but they will take default DateTime value from C# (01-01-0001) and will insert that value in database

If you want both NOT NULL and still want DateTime to take current date and time value, then it is better to override OnModelCreating and specify default value constraint on these columns as shown below.

Posts Controller

In this section, we are going to take example of many to many relationship between a post and categories in insert new post scenario. We already have defined mapping between PostModel (API Layer) to Post (database entity). But it does not map related entities.

The API model has collection of categories (List<CategoryModel>), while the Post database entity expects collection of List<PostCategories> where PostCategories is a table which stores the associations. This association table needs ID of a category and a post. A CategoryModel instance would have Id of category, but as the post is getting inserted, it does not have an Id yet. So how to specify association ?

In this case, we can use the reference navigation property PostCategories.Post instead of foreign key property PostCategories.PostId.

Modified controller code is shown below. The code only shows how to map categories, but same logic can be applied for tags. For comments, we can write similar logic in create new action of ConmmentsController to associate new comment with existing post.

Note that mapping can also be specified in AutoMapper, but I skipped it to save some time and keep this article focused on EF core part.

I hope this article provides information about how related data can be saved. If you have any comments / queries, feel free to comment on this post. In next article, we will see how to load related data.