Entity Framework Trackable entities (VB.NET)
Introduction
In part 1 of this series, an introduction to why using Entity Framework and VB.NET over using conventional methods to interact with persisting data in a Windows Form solution coupled with the following article on recommendations for structuring a Visual Studio solution and overcoming Entity Framework inability to work well with data binding to a DataGridView remedied in the following article. This part of the series will focusing on tracking changes automatically by writing code in the DbContext class for modified, deleted and additions to models in the DbContext. And finally an introduction to working with Entity Framework Core 3.1 which has many breaking changes over Entity Framework 6 with common workarounds.
Source code is provided in the following GitHub repository which has more code than discussed in this article which is for upcoming articles on working with Entity Framework which can be explored to learn how to perform common operations with data interaction. In many cases the code is extremely basic for easy learning while a small percentage of the code is to be considered advance level coding.
Data model
Rather than use a relational model e.g. Customers, Orders, Order details the base code sample uses a non-relational approach, one table, either a contact or person table. When working with a relational model simply apply the same logic used for a single table regarding creation/modified date and user details.
Trackable entities
Trackable entities are creation/updated dates and user identifier when a new record is added or modified which provides basic auditing of data, in this case done by overloading the SaveChanges and/or SaveChangesAsync method of a DbContext. In the code samples discussed soft deletions are performed rather than a hard deletion of data which sets the modified date and user identifier.
User identifier in these code samples are hard coded, for a production application either setup a authorized user table with a primary key and a string column for the user name and other details add additional columns. Optionally the username can come from Environment.UserName if a user table is not desired.
Implementation
First decide how to identify the user, if from a user table use an int which represents the primary key for a user while for a username nvarchar(N) where N is the length which is commonly 10, 50 or MAX. Identifying the user can come from an attribute from Active Directory or a login to the application. There are no examples in the code samples as there are too many variations to cover in this article.
Example using a Contact table with username for an identifier
CREATE TABLE [dbo].[Contact](
[ContactId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](max) NULL,
[LastName] [nvarchar](max) NULL,
[LastUpdated] [datetime2](7) NULL,
[LastUser] [nvarchar](max) NULL,
[CreatedAt] [datetime2](7) NULL,
[CreatedBy] [nvarchar](max) NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_Contact1] PRIMARY KEY CLUSTERED
(
[ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Example using a Contact table with user identifier instead of user name.
CREATE TABLE [dbo].[Contact](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](max) NULL,
[LastName] [nvarchar](max) NULL,
[IsDeleted] [bit] NULL,
[CreatorUserId] [int] NULL,
[CreatedUtc] [datetime2](7) NULL,
[UpdaterUserId] [int] NULL,
[UpdatedUtc] [datetime2](7) NULL,
[DeleterUserId] [int] NULL,
[DeletedUtc] [datetime2](7) NULL,
CONSTRAINT [PK_Contact2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Note that in both cases there should be a auto-incrementing primary key as user names name.
Model which in this case has been generated by reverse engineering process from Entity Framework.
Partial Public Class Contact
Public Property ContactId As Integer
Public Property FirstName As String
Public Property LastName As String
End Class
Adding in the create and modify properties.
Partial Public Class Contact
Public Property ContactId As Integer
Public Property FirstName As String
Public Property LastName As String
<Column(TypeName:="datetime2")>
Public Overloads Property LastUpdated As Date?
Public Overloads Property LastUser As String
<Column(TypeName:="datetime2")>
Public Overloads Property CreatedAt As Date?
Public Overloads Property CreatedBy As String
End Class
This works great for one table while in a relational model this means these properties need to be added to each table, instead they can be implemented via a Interface or base class as in this case a base class is used which also includes IsDeleted for soft deletes.
Public Class BaseEntity
Public Property CreatedAt() As Date?
Public Property CreatedBy() As String
Public Property LastUpdated() As Date?
Public Property LastUser() As String
Public Property IsDeleted As Boolean?
End Class
Which changes the Contact class as follows.
Partial Public Class Contact
Inherits BaseEntity
Public Property ContactId As Integer
Public Property FirstName As String
Public Property LastName As String
End Class
Next open the project's DbContext class which with one table looks like this.
Partial Public Class Context
Inherits DbContext
Public Sub New()
MyBase.New("name=Context")
End Sub
Public Overridable Property Contact1 As DbSet(Of Contact1)
Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
End Sub
End Class
Next add the following method to the DbContext class which will be used with one or both SaveChanges overloads.
''' <summary>
''' Responsible for setting created, last updated
''' and soft delete property before SaveChanges or SaveChangesAsync
''' </summary>
Private Sub BeforeSave()
ChangeTracker.DetectChanges()
For Each currentEntry As DbEntityEntry In ChangeTracker.Entries()
If currentEntry.State = EntityState.Added OrElse currentEntry.State = EntityState.Modified Then
currentEntry.Property("LastUpdated").CurrentValue = Date.Now
currentEntry.Property("LastUser").CurrentValue = Environment.UserName
If TypeOf currentEntry.Entity Is Contact1 AndAlso currentEntry.State = EntityState.Added Then
currentEntry.Property("CreatedAt").CurrentValue = Date.Now
currentEntry.Property("CreatedBy").CurrentValue = Environment.UserName
End If
ElseIf currentEntry.State = EntityState.Deleted Then
currentEntry.Property("LastUpdated").CurrentValue = Date.Now
currentEntry.Property("LastUser").CurrentValue = Environment.UserName
' Change state to modified and set delete flag
currentEntry.State = EntityState.Modified
CType(currentEntry.Entity, BaseEntity).IsDeleted = True
End If
Next
End Sub
Add the two overloads for SaveChanges and SaveChangesAsync.
Public Overrides Function SaveChangesAsync() As Task(Of Integer)
BeforeSave()
Return MyBase.SaveChangesAsync()
End Function
Public Overrides Function SaveChanges() As Integer
BeforeSave()
Return MyBase.SaveChanges()
End Function
Updated DbContext
Imports System
Imports System.Data.Entity
Imports System.ComponentModel.DataAnnotations.Schema
Imports System.Data.Entity.Infrastructure
Imports System.Linq
Partial Public Class Context1
Inherits DbContext
Public Sub New()
MyBase.New("name=Context") ' or secondary connection string
End Sub
Public Overridable Property Contact1 As DbSet(Of Contact1)
Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
End Sub
''' <summary>
''' Responsible for setting created, last updated
''' and soft delete property before SaveChanges or SaveChangesAsync
''' </summary>
Private Sub BeforeSave()
ChangeTracker.DetectChanges()
For Each currentEntry As DbEntityEntry In ChangeTracker.Entries()
If currentEntry.State = EntityState.Added OrElse currentEntry.State = EntityState.Modified Then
currentEntry.Property("LastUpdated").CurrentValue = Date.Now
currentEntry.Property("LastUser").CurrentValue = Environment.UserName
If TypeOf currentEntry.Entity Is Contact1 AndAlso currentEntry.State = EntityState.Added Then
currentEntry.Property("CreatedAt").CurrentValue = Date.Now
currentEntry.Property("CreatedBy").CurrentValue = Environment.UserName
End If
ElseIf currentEntry.State = EntityState.Deleted Then
currentEntry.Property("LastUpdated").CurrentValue = Date.Now
currentEntry.Property("LastUser").CurrentValue = Environment.UserName
' Change state to modified and set delete flag
currentEntry.State = EntityState.Modified
CType(currentEntry.Entity, BaseEntity).IsDeleted = True
End If
Next
End Sub
Public Overrides Function SaveChangesAsync() As Task(Of Integer)
BeforeSave()
Return MyBase.SaveChangesAsync()
End Function
Public Overrides Function SaveChanges() As Integer
BeforeSave()
Return MyBase.SaveChanges()
End Function
End Class
In BeforeSave, the first line ask the POCO entries if there are any changes followed by iterating the changes in ChangeTracker.Entities. Important note, Entries in Entity Framework 6 are DbEntityEntry while in Entity Framework 3.1 the iterator is EntityEntry (there are many breaking changes between EF 6 and EF Core prior to EF Core 3.1).
While iterating entries the state of the entry is checked, if added then set created fields and if modified set modified fields. In the delete check the entry state is changed to Modified so it will not be deleted then the IsDeleted property is set to True.
This is the EF Core 3.1 version of BeforeSave.
Private Sub BeforeSave()
ChangeTracker.DetectChanges()
' DbEntityEntry in EF6
For Each currentEntry As EntityEntry In ChangeTracker.Entries()
If currentEntry.State = EntityState.Added OrElse currentEntry.State = EntityState.Modified Then
currentEntry.Property("UpdatedUtc").CurrentValue = Date.Now
currentEntry.Property("UpdaterUserId").CurrentValue = 5
If TypeOf currentEntry.Entity Is Person AndAlso currentEntry.State = EntityState.Added Then
currentEntry.Property("CreatedAt").CurrentValue = Date.Now
currentEntry.Property("CreatedBy").CurrentValue = Environment.UserName
End If
ElseIf currentEntry.State = EntityState.Deleted Then
currentEntry.Property("UpdatedUtc").CurrentValue = Date.Now
currentEntry.Property("UpdaterUserId").CurrentValue = 1
currentEntry.State = EntityState.Modified
CType(currentEntry.Entity, BaseEntity).IsDeleted = True
End If
Next
End Sub
Soft deletes: Using soft deletes means if those records should not be seen then add the following method to the DbContext class above to filter out soft deletes.
''' <summary>
''' Simple method to filter out IsDeleted records
''' </summary>
''' <typeparam name="TEntity"></typeparam>
''' <param name="query"></param>
''' <returns></returns>
''' <remarks>
''' This can be done in a generic method also,
''' keeping things simple here for starters.
''' </remarks>
Public Function ApplyActiveFilter(Of TEntity As Contact1)(query As IQueryable(Of TEntity)) As IQueryable(Of TEntity)
Return query.Where(Function(entity) entity.IsDeleted.Value = False)
End Function
Usage in a form, create a private form level variable for the DbContext.
Private dbContext As New Context
Load records as follows passing through the model
Dim activeContacts = dbContext.ApplyActiveFilter(dbContext.Contact).ToList()
Once the following has been implemented try adding, editing and removing records with SSMS (SQL-Server Management Studio) open to the table which trackable entities have been implemented to see the dates, user information and delete flag set.
If the table(s) will be presented outside of the application setup a view to filter out IsDeleted, otherwise deleted records will be shown which can be a drawback with soft deletes.
Running included code sample
Although following instructions above is the best way to try out trackable entities the next option is to click on the source code link at the bottom of this article and fork the entire solution from GitHub. Once forked and loaded into Visual Studio;
- Right click on the top node in solution explorer, select "restore NuGet packages"
- Use the following script to create the database and tables.
- If running the script "as is" first check line 7 for the path which the database will be created as this path may be different on a development box with a different version of SQL-Server.
- Build the following two projects followed by running ContactsForm project.
Additional projects included for upcoming articles
These projects are together in one solution so as this series progresses only one solution is needed rather than having to open and close several Visual Studio solutions. Note that projects listed below have no documentation other than code comments as documentation will be presented in future articles. Bookmark this page for a growing list of Entity Framework classic and Entity Framework Core for Windows Forms.
- AuditableEntitiesFrontEnd and BackendAuditableEntities use Awesome Open Source Entityframework.commonfools library to implement trackable entities which is another option instead of rolling your own.
- FrontEndRelational and BackEndRelational are for an upcoming article working with relational data using Entity Framework 6 in a Windows Form project demonstrating/showcasing how to work with Entity Framework 6 to mimic MS-Access table editor, make a change, move off the active DataGridView cell and save the change immediately with data annotation validation. This project utilizes a custom BindingList in tangent with a BindingSource.
Summary
Using methods discussed provide an easy method to implement trackable entries which remember creation and modification date and optionally user name or identifier along with implementing soft deletes in Windows Form projects using VB.NET.
See also
VB.NET Entity Framework: Wiki portal
External references
Microsoft: Fluent API with VB.NET