When we design a database, we sometimes need to add columns to track when a record created/changed and who made the change. For this we add the following columns:
1. CreatedOn
2. CreatedBy
3. UpdatedOn
4. UpdatedBy
We use default values and triggers to handle the CreatedAt and LastUpdatedAt columns. Creating this is boring and we don't want to do it by ourselves. Also it can be hard to set the user name because in a web context, there is not only one user that connects to the database. So, we cannot set the value of the tracking columns using the CURRENT_USER function.
Here Entity Framework to do it automatically for you. So, the solution is to automatically set the value of these properties before calling SaveChangesAsync or SaveChanges methods. There are multiple ways to handle these properties.
1. You can add R/W properties to the model.
2. You can also use R/O properties.
3. You can use shadow properties.
4. In the last case, the columns are not mapped to a property in the model. Only Entity Framework is aware of the additional properties.
Let's see how to do this practically!
|
CREATE TABLE [dbo].[Blog](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AuthorId] [nvarchar](50) NULL,
[Title] [nvarchar](50) NULL,
[Content] [nvarchar](max) NULL,
[CreatedOn] [datetime2](7) NULL,
[CreatedBy] [int] NULL,
[UpdatedOn] [datetime2](7) NULL,
[UpdatedBy] [int] NULL
)
|
Project Setup
We need to create a three projects:
1. The main project that called TrackingColumns.
2. The project for business logic is called TrackingColumnsBusinessLayer.
3. The project to create a model and DbContext where we can perform the SQL operations is called TrackingColumnsDataLayer.
To start, we will create an ASP.NET Core 6.0 web Application with a project type of web API and call it TrackingColumns.
Require packages:
1. TrackingColumns
Install-Package Microsoft.EntityFrameworkCore.Design-Version 6.0.3
2. TrackingColumnsDataLayer
Install-Package Microsoft.EntityFrameworkCore.Design-Version 6.0.3
Install-Package Microsoft.EntityFrameworkCore.SqlServer-Version 6.0.3
Next for the project setup we need to add a reference to the TrackingColumnsDataLayer project in our TrackingColumnsBusinessLayer project. And add a reference to the TrackingColumnsBusinessLayer in our TrackingColumns project. The Final Setup will look like this:
Set up the connection string in the appsettings.json file
Generate an Application DB context class.
|
dotnet ef dbcontext scaffold "Server=your server name;User Id=yourid;Password=yourpassword;Database=your database name;" Microsoft.EntityFrameworkCore.SqlServer -f -o Context -c BlogContext -t Blog
|
Let's implement the 3 ways using Entity Framework!
R/W properties
In this case, the entity has read/write properties. Means, the value of the property can be changed by our code.
Now, let’s change the default behavior of the SaveChanges & SaveChangesAsync method in the DB context class to set the values of the tracking properties. The code iterates over the entities tracked by the context and depending on the state, sets the value of the tracking properties.
Common Functions in the BlogContext class
The bother of this method is, the properties have a setter and we don't want to set the value in our code.
R/O properties
Entity Framework Core allows mapping of the column to a field. We can use read-only properties.
Let’s update the Blog class to set properties in read-only mode.
Properties are now read-only so we need to instruct Entity Framework to use the fields to set the value of the column.
We can now use the ChangeTracker to indicate the Entity Framework the value to set for the properties. This way there is no need to access the property or the field.
No properties (Shadow Properties)
Sometimes, we may want to set column values without exposing properties in the model. You may expose the last updated date, but not who made the changes. Entity Framework Core can handle this by using shadow property. Shadow properties are declared in the OnModelCreating method, but do not exist in your class. You can read and write the value of the shadow properties using the ChangeTracker.
The final step is to create a simple endpoint that will call the BlogService to call insert and update APIs.
BlogController.cs
This is the BlogModel class we are sending from request.
As you can see in the service file, we are not assigning any value to the tracking columns. The entity framework core will do that work for us!
Create API output
Update API output
Table Data in Blog table(Create API)
Table Data in Blog table(Update API)
By using Entity Framework Core, you can map the database tables to your object model the way we want. This allows us to create clean classes and we don't have to expose properties we don't want.
Enjoy…!!!