In this article I will describe a way to detect changes to database models with the help of the Entity Framework change tracker. The resulting possibilities include automatic logging of any database changes, reacting upon specific changes and using database changes as a way to interconnect different parts of your application.

The example will be based on Visual Studio 2017, Microsoft SQL Server 2016, Microsoft Entity Framework 6.0 and the Entity Framework Code-First approach.

You can find the full source code on GitHub.

In the first part, we will cover the basic setup. We will create a database, a visual studio solution and have a working data access layer with some simple business models.

In the second part, we will extend the solution to enable change detection and will take a look on what we can do with it.

Create a Test Database

First of all create a new database. I will describe the way to do this with Microsoft SQL Management Studio (MSSQLMS).

Open MSQLMS and connect to your MS SQL instance. Right click on Databases and select New Database.

In the popup, select a name for the new database. For this article, we will call it EntityFramework.ChangeDetectionTest. Click OK and we have a new, but still empty database. To be able to access it from the application, we need a SQL user.

In the tree view, open Security, right click on Logins and select New Login.

Now we need to select SQL Server authentication and enter a new username and the password.

In order to connect the new login to our database, we switch to the User Mapping page on the left side.

Select the freshly created database from the list and check the db_owner flag at the bottom. This way we allow the created user full access to the database. This will be necessary, since we want the application to create the tables.

Create a New Visual Studio Project

Let’s get startet and create a new, blank Visual Studio solution. We will call it EntityFrameworkChangeDetection. After this, create the following projects under the solution:

.NET Framework Console Application – ConsoleStarter

.NET Framework Class Library – Data

.NET Framework Class Library – Data.Models

The ConsoleStarter project will kick off our application and will be the entry point of the program. The Data.Models project will contain our business models and the Data project will be responsible to provide access to the database.

Create the Entity Models

Open the Data.Models project and add the following two directories:

Models

Interfaces

In the Interfaces directory, add the new interface IModelWithId.

IModelWithId.cs:

namespace Data.Models.Interfaces { public interface IModelWithId { long Id { get; set; } } }

This will be our common denominator for all entities, since they all have a primary key (in this case a long / bigint). Having an interface / base class for all entities has some advantages, which will we see later on.

Now create two new classes in the Models directory: Customer and Contract.

Customer.cs:

using Data.Models.Interfaces; using System.Collections.Generic; namespace Data.Models.Models { public class Customer : IModelWithId { public Customer() { this.Contracts = new HashSet<Contract>(); } public long Id { get; set; } public string Firstname { get; set; } public string Lastname { get; set; } public ICollection<Contract> Contracts { get; set; } } }

Contract.cs:

using Data.Models.Interfaces; namespace Data.Models.Models { public class Contract : IModelWithId { public long Id { get; set; } public string ContractNumber { get; set; } public string Note { get; set; } public long CustomerId { get; set; } public virtual Customer Customer { get; set; } } }

There is nothing special about the models. We have modeled a simplified customer – contract relationship with some properties, so we can test our database change detection later on.

Database Access Layer

Open the Data project and add the following directories:

Constants

Controllers

I don’t really like to put my database connection details into the app.config, there is too much magic acting in the background when the Entity Framework initializes the database context from the app.config in my opinion. Furthermore, only the app.config of the executing assembly is used to extract the connection string. Since I have typically both a windows service and a console starter project, I usually have to manage the connection details in two separate places, which I would like to avoid. Therefore, I prefer to place them into a configuration file and have full control about the access and control flow. This way, I can also easily extract the connection details, if I would like to access the database in another way (for instance with Dapper.NET).

For prototype reasons, we will just put them into a static class which will hold our constants. Create a new class DatabaseConnection in the directory Constants:

DatabaseConnection.cs:

namespace Data.Constants { public static class DatabaseConnection { public const string Server = @"localhost"; public const string UserId = "some_user"; public const string Password = "password123"; public const string ApplicationDatabase = "EntityFramework.ChangeDetectionTest"; } }

Modify the connection details according to your server and database setup from the step Create a Test Database. In order to build the connection string, we will add a little helper class into the directory Controllers called ConnectionStringBuilder.

ConnectionStringBuilder.cs:

using Data.Constants; using System.Data.SqlClient; namespace Data.Controllers { public static class ConnectionStringBuilder { public static string CreateDatabaseConnectionString() { var sqlBuilder = new SqlConnectionStringBuilder() { DataSource = DatabaseConnection.Server, InitialCatalog = DatabaseConnection.ApplicationDatabase, PersistSecurityInfo = true, IntegratedSecurity = false, MultipleActiveResultSets = true, UserID = DatabaseConnection.UserId, Password = DatabaseConnection.Password }; return sqlBuilder.ConnectionString; } } }

With the help of the ConnectionStringBuilder, we can dynamically generate a connection string, which otherwise would be found in the app.config. This is required to setup the Entity Framework DbContext class.

Last but not least we create the DatabaseContext class in the directory Controllers.

DatabaseContext.cs:

using Data.Models.Models; using System; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity; namespace Data.Controllers { public class DatabaseContext : DbContext { public DatabaseContext(string connectionString) : base(connectionString) { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2")); modelBuilder.Entity<Customer>().ToTable("Customers"); modelBuilder.Entity<Customer>().Property(x => x.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); modelBuilder.Entity<Customer>().HasKey(x => x.Id); modelBuilder.Entity<Customer>().Property(x => x.Firstname).IsRequired().HasMaxLength(DefaultStringLength); modelBuilder.Entity<Customer>().Property(x => x.Lastname).IsRequired().HasMaxLength(DefaultStringLength); modelBuilder.Entity<Customer>().HasMany(x => x.Contracts).WithRequired(x => x.Customer).HasForeignKey(x => x.CustomerId); modelBuilder.Entity<Contract>().ToTable("Contracts"); modelBuilder.Entity<Contract>().Property(x => x.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); modelBuilder.Entity<Contract>().HasKey(x => x.Id); modelBuilder.Entity<Contract>().Property(x => x.ContractNumber).IsRequired().HasMaxLength(DefaultStringLength); } private const int DefaultStringLength = 100; } }

We inherit from the DbContext class and in the constructor we pass the incoming connectionString argument into the base class. Furthermore, we setup our entities-to-database mapping in the OnModelCreating method.

Let’s try it out and add, modify and remove a customer in the ConsoleStarter project.

First Test

Open the Program class in the ConsoleStarter project and exchange it with the following lines.

Program.cs:

using Data.Controllers; using Data.Models.Models; using System; namespace ConsoleStarter { class Program { static void Main(string[] args) { var connectionString = ConnectionStringBuilder.CreateDatabaseConnectionString(); using (var dbContext = new DatabaseContext(connectionString)) { var customer = new Customer { Firstname = "Peter", Lastname = "Miller" }; var contract = new Contract { ContractNumber = "100-2017-00001", Note = string.Empty, Customer = customer }; dbContext.Set<Customer>().Add(customer); dbContext.Set<Contract>().Add(contract); if (dbContext.SaveChanges() <= 0) { throw new InvalidOperationException("Could not add customer"); } customer.Firstname = "Simon"; contract.Note = "Contract duration changed"; if (dbContext.SaveChanges() <= 0) { throw new InvalidOperationException("Could not update customer / contract"); } customer.Contracts.Remove(contract); dbContext.Set<Contract>().Remove(contract); if (dbContext.SaveChanges() <= 0) { throw new InvalidOperationException("Could not update customer"); } dbContext.Set<Customer>().Remove(customer); if (dbContext.SaveChanges() <= 0) { throw new InvalidOperationException("Could not remove customer"); } } } } }

We create a customer and a contract, modify some properties, modify the relationship and finally remove the customer.

If you start the application for the first time, the entity structure will automatically be created as tables in the database. It should create, update and remove the entities in the database – but we still don’t have any possibility to detect the changes. We will cover the missing steps in the next part.

Thanks for reading. Please leave a comment in the case you enjoyed reading this article, if you found any mistakes or have any suggestions for improvement.