Category: .NET

Whenever there is a new project with database, there is substantial amount of time spent on design of that database, as changes to database later cause much more overhead and problems than in beginning. In today’s conditions, however, it is very rare that database schema is not changed even during implementation (or immediately after deployment ). That is one of reasons why we now have CodeFirst workflow in EntityFramework.  It is great to have one common set of properties in one place (base object) inherited into all tables (i.e. UserCreated, DateCreated etc), and not having to think about it in individual objects.

It is even better to have all logic that uses those common properties in one place, but that tends to be hard to implement if repository pattern is used with all “common” logic implemented in common repository containing basic CRUD operations and data context as a dependency object. The problem is that each repository will inherit from common repository, and all will have “same” behavior, but EF allows you to save complex objects at once, and this can make your life bitter when you have common repository. The thing is, your custom behavior is executed for object you are saving, but not for referenced objects which are also saved when you add or update your main object. Workaround is to have instance of repository for each type which is referenced in your main object, save each first through “its” repository, and then save your main object which references all of them. As thing always can be made better (and better is more simple for me), I tried to find an answer for this problem. My answer is on github and if you are interested check it out (it has sample which works out of the box), and read description here.

There is a simple solution for this problem – common repository is code first context! It has couple of extension points (virtual methods left which can be overriden) where you can implement your own logic to handle journaling, validation, business rules… Almost everyone who worked with CF has used (overriden) OnModelCreating method, as it is very common in tutorials, but there are couple more:

bool ShouldValidateEntity(DbEntityEntry entityEntry)

System.Data.Entity.Validation.DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, IDictionary<object, object> items)

int SaveChanges()

ShouldValidateEntity and ValidateEntity are usefull places to call from your business rules and all possible validators, as there is no way that something will go to database by not passing through these (often ignored methods). These extension points are fairly simple to use and domain-specific, and I will not write about them in this post. My target of interest is SaveChanges.

go.DB.JournalingBase is simple project featuring usage of EntityFramework CodeFirst and overriding SaveChanges method of DBContext in order to keep record of changes on entities in database. There is a simple usage sample project and a test project with couple of test cases to confirm that everything is working. Database is SQLCompact, so no SQL Server is necessary and everything works on “F5”.

This is the idea: I have one “base” object – JEntity from my database objects inherit, and have corresponding DBContext, from which my repository(ies) inherit. Common object has Id property and other common properties I want all my journaling objects to have:

public abstract class JEntity
    {
        /// <summary>
        /// Gets or sets the id.
        /// </summary>
        /// <value>The id.</value>
        public int Id { get; set; }
 
        /// <summary>
        /// Gets or sets the original id.
        /// </summary>
        /// <value>The original id.</value>
        public int OriginalId { get; set; }
 
        /// <summary>
        /// Gets or sets the date created.
        /// </summary>
        /// <value>The date created.</value>
        public DateTime DateCreated { get; set; }
 
        /// <summary>
        /// Gets or sets the date deleted.
        /// </summary>
        /// <value>The date deleted.</value>
        public DateTime? DateDeleted { get; set; }
 
        /// <summary>
        /// Gets or sets the user created.
        /// </summary>
        /// <value>The user created.</value>
        public string UserCreated { get; set; }
 
        /// <summary>
        /// Gets or sets the user deleted.
        /// </summary>
        /// <value>The user deleted.</value>
        public string UserDeleted { get; set; }
    }

This object has OriginalId property, as a reference to “original” object – the first one that was added to database. When objects are updated, new version is created each time, “old” object is marked as deleted (DateDeleted is set in this case). All versions of same object share same OriginalId value, which is the same as Id value of oldest version.

Mapping to database is done using IEntityMap class, so this one is clean and simple, and does not have database metadata. The reason is that in real life you often have to refactor your code and change something. If model is created from metadata, then any change will cause your existing database to be “broken” and you will have to recreate it. This is good strategy in the beginning when you initialize your database with test data every time and test through unit tests, but if you have some staging environment where application is deployed and tested by users/testers they may have problems with that (whereee areee all thoose records I entered in friday?? ). This is especially important if you are making some serious enterprise application that will have longer lifecycle, and you want to make support easier (we do not want to have a “bomb” which will delete production database).

public IEntityMap()
	{
		//// Primary key
		this.HasKey(t => t.Id);
 
		//// Properties
 
		this.Property(t => t.OriginalId)
			.IsRequired();
 
		this.Property(t => t.DateCreated)
			.IsRequired();
 
		this.Property(t => t.DateDeleted)
			.IsRequired();
 
		this.Property(t => t.UserCreated)
			.IsRequired()
			.HasMaxLength(50);
 
		this.Property(t => t.UserDeleted)
			.IsOptional()
			.HasMaxLength(50);
 
		//// Column mappings
		this.Property(t => t.Id).HasColumnName("Id");
 
		this.Property(t => t.DateCreated).HasColumnName("DateCreated");
 
		this.Property(t => t.OriginalId).HasColumnName("OriginalId");
 
		this.Property(t => t.DateDeleted).HasColumnName("DateDeleted");
 
		this.Property(t => t.UserCreated).HasColumnName("UserCreated");
 
		this.Property(t => t.UserDeleted).HasColumnName("UserDeleted");
	}

So,  separate mapping class allows you to refactor properties without having to change their names in database, or to manually sync your database with your model, as everything is clean and readable.

The context – JContext overrides SaveChanges method, which takes care of actions necessary for journaling:

public override int SaveChanges()
{
	// We need same time for all entities updated in same transaction.
	DateTime now = DateTime.Now;
	// Get reference to user to avoid multiple getter calls.
	string user = System.Threading.Thread.CurrentPrincipal.Identity.Name;
 
	//// Detect changes in case that change tracking is turned off
	if (!this.Configuration.AutoDetectChangesEnabled)
	{
		this.ChangeTracker.DetectChanges();
	}
	List<JEntity> insertedList = new List<JEntity>();
	List<JEntity> invalidList = new List<JEntity>();
	foreach (var entry in this.ChangeTracker.Entries())
	{
		// Make sure that this customized save changes executes only for entities that
		// inherit from our base entity (IEntity)
		var entity = (entry.Entity as JEntity);
		if (entity == null) continue;
 
		switch (entry.State)
		{
			// In case entity is added, we need to set OriginalId AFTER it was saved to
			// database, as Id is generated by database and cannot be known in advance.
			// That is why we save reference to this object into insertedList and update
			// original id after object was saved.
			case System.Data.EntityState.Added:
				entity.UserCreated = user;
				entity.DateCreated = now;
				insertedList.Add(entity);
				break;
 
			// Deleted entity should only be marked as deleted.
			case System.Data.EntityState.Deleted:
				if (!entity.IsActive(now))
				{
					invalidList.Add(entity);
					continue;
				}
				entry.Reload();
				entity.DateDeleted = now;
				entity.UserDeleted = user;
				break;
 
			case System.Data.EntityState.Detached:
				break;
 
			case System.Data.EntityState.Modified:
				if (!entity.IsActive(now))
				{
					invalidList.Add(entity);
					continue;
				}
				entity.UserCreated = user;
				entity.DateCreated = now;
 
				JEntity newVersion = this.Set(entity.GetType()).Create(entity.GetType()) as JEntity;
				newVersion = this.Set(entity.GetType()).Add(newVersion) as JEntity;
				this.Entry(newVersion).CurrentValues.SetValues(entity);
 
				this.Entry(entity).Reload();
 
				entity.DateDeleted = newVersion.DateCreated;
				entity.UserDeleted = user;
				break;
			case System.Data.EntityState.Unchanged:
				break;
			default:
				break;
		}
	}
	if (invalidList.Count == 1)
	{
		throw new InvalidJournalingEntityException(invalidList[0], invalidList[0].GetType().Name);
	}
	else if (invalidList.Count > 1)
	{
		throw new MultipleInvalidJournalingEntityException(invalidList);
	}
	int result = base.SaveChanges();
 
	if (insertedList.Count > 0)
	{
		insertedList.ForEach(t => t.OriginalId = t.Id);
		base.SaveChanges();
	}
 
	return result;
}

The method handles cases when object is added, modified or deleted. When added, it must save reference to it and set OriginalId after it was assigned Id. As object does not have Id until sql server generates one for it, this has to be done after SaveChanges. Theese few lines should in serious system be inside one transaction, just in case.

When object is changed, change is canceled and  only DateDeleted and UserDeleted is set (same as when object is deleted). Instead, new object is added as new version of changed object.

All this is used from sample project, where new entities and context are created, with no code for usage of theese features except inheritance from JEntity and JContext.

If something is not clear, the best option is to start test project and set breakpoints at places of interest, or ask your question in comments!