Showing posts with label entityframework. Show all posts
Showing posts with label entityframework. Show all posts

Tuesday 5 July 2022

UnitOfWork pattern in Entity Framework

This article will present code regarding the 'Unit of Work' pattern in Entity Framework, EF. I will also show updated code for a generic repository pattern of the same code. Note that the code in this article is very general and can be run against your DbContext regardless of how it looks. It is solved general, using generics and some usage of reflection. Note that this code goes against DbContext and is written in a solution that uses Entity Framework 6.4.4. However, the code here should work also in newer EF versions, that is - EF Core versions - such as EF Core 6, as the code is very general and EF Core also got the similar structure at least concerning the code shown here.. First off, the code for the interface IUnitOfWork looks like this:
 
 IUnitOfWork.cs

using SomeAcme.Common.Interfaces;

namespace SomeAcme.Data.EntityFramework.Managers
{
    public interface IUnitOfWork
    {
        UnitOfWork AddRepository<T>() where T : class;
         UnitOfWork AddCustomRepository<T>() where T : class;
        int Complete();
        void Dispose();
        UnitOfWork RemoveRepository<T>() where T : class;
        IRepository<T> Repository<T>() where T : class;
    }
}
 
The method Complete is important, as it will commit the transaction and perform changes to the database which the UnitOfWork implementation will work against. The code for UnitWork implementation looks like this:
 
 UnitOfWork.cs

using SomeAcme.Common.Interfaces;
using System;
using System.Collections.Generic;
using System.Linq;

namespace SomeAcme.Data.EntityFramework.Managers
{

    public class UnitOfWork : IUnitOfWork, IDisposable
    {

        private readonly System.Data.Entity.DbContext _dbContext;

        public UnitOfWork(System.Data.Entity.DbContext dbContext)
        {
            _dbContext = dbContext;
            _repositories = new Dictionary<Type, object>();
        }

        public UnitOfWork AddRepository<T>() where T : class
        {
            if (!_repositories.ContainsKey(typeof(T)))
            {
                var repoObj = Activator.CreateInstance(typeof(Repository<T>), _dbContext);
                Repository<T> repo = repoObj as Repository<T>;
                if (repo == null)
                {
                    throw new ArgumentNullException($"Could not instantiate repository of type {typeof(T).Name}");
                }
                _repositories[typeof(T)] = repo;
            }
            return this;
        }

        public UnitOfWork AddCustomRepository<T>() where T : class
        {
            if (!_repositories.ContainsKey(typeof(T)))
            {
                bool checkImpementationPassesGenericInterfaceCheck = typeof(T).GetInterfaces().Any(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IRepository<>));
                if (!checkImpementationPassesGenericInterfaceCheck)
                {
                    throw new ArgumentException($"The type {typeof(T).Name} must implement IRepository<T> to be added as a custom repository");
                }
                var repoObj = Activator.CreateInstance(typeof(T), _dbContext);

                if (repoObj == null)
                {
                    throw new ArgumentNullException($"Could not instantiate repository of type {typeof(T).Name}");
                }
                _repositories[typeof(T)] = repoObj;
            }
            return this;
        }

        public UnitOfWork RemoveRepository<T>() where T : class
        {
            if (_repositories.ContainsKey(typeof(T)))
            {
                _repositories.Remove(typeof(T));
            }
            return this;
        }

        public IRepository<T> Repository<T>() where T : class
        {
            //find suitable repo - possibly a custom repo ..
            IRepository<T> repoFound = null; 

            foreach (var item in _repositories)
            {
                if (item.Key == typeof(T))
                {
                    repoFound = _repositories[typeof(T)] as IRepository<T>;
                    break; 
                }
                bool checkImpementationPassesGenericInterfaceCheck = item.Key.GetInterfaces().Any(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IRepository<>));
                if (checkImpementationPassesGenericInterfaceCheck)
                {
                    //the repo implements IRepository<T> - this is the one to use
                    repoFound = item.Value as IRepository<T>;
                    break;
                }
            }
            if (repoFound == null)
            {
                throw new ArgumentNullException($"Could not retrieve repositiory defined inside the UnitOfWork for entity of type: {typeof(T).Name}. Is it registered into the UnitOfWork. Use method 'AddRepository'");
            }
            return repoFound; 
        }

        private readonly IDictionary<Type, object> _repositories;

        public int Complete()
        {
            if (_dbContext == null)
            {
                throw new ArgumentNullException($"The db context object of the UnitOfWork class is null, cannot complete the UnitOfWork as the db context is not initialized! No changes was performed in DB !"); 
            }
            int numStateEntriesWritten = _dbContext.SaveChanges();
            return numStateEntriesWritten;
        }

        public void Dispose()
        {
            _dbContext?.Dispose(); //dispose the passed in _shared_ db context instead of 
            //disposing the db context inside each repository to dispose only once.. 
            GC.SuppressFinalize(this);
        }

    }
}

 
Note - this implementation focuses on being able to define repositories to the UnitOfWork before running the unit work in case you want to be able to specify which tables / entities got repositories in the db context. Many other implementations will specify the repositories to expose in the UnitOfWork via property getters for example. This will make it easier to implement such a UnitOfWork implementation without this ability to expanding which repositories the UnitOfWork is supporting. Also note that in case you want to add a custom repository that implements IRepository<T> you need to cast into that custom repository when you call the 'Repository' method. A closed abstraction instead, where you do not add repositories like in this code may be more desirable. But perhaps there are scenarios where you want to add repositories that can take part in the UnitOfWork dynamically as shown here. The downside is that you need to initialize the UnitOfWork in addition to performing the database 'steps' before calling the 'Complete' method. What is the most practical UnitOfWork implementation in many cases most probably will be a closed abstraction where you specify which repositories the UnitOfWork supports and avoid having to add repositories like shown here. I did this as an academic exercise to see if such an implementation was possible. The unit tests passes and it looks okay to implement. Note though that you should probably have some default initialization here, i.e. in the constructor of UnitOfWork specify some default repositories and consider if you want to allow adding or removing repositories in the UnitOfWork class. Also, removing repositories could be considered an anti-pattern, so you could disallow this - only allowing adding custom repositories implementating the IRepository<T> in addition to listing up some implemented repositories. Important - always just pass in ONE db context. Each repository must use the same db context instance so the change tracking works as expected. Also note - the UnitOfWork and repositories implement IDisposable. When UnitOfWork goes out of scope, the db context is disposed. In case you want to add the UnitOfWork as a service in a DI container, remember to set up a scoped instance so it will be disposed. In case you use Singleton - this will cause the db connection to hang around.. the Dispose method is exposed as a public method anyways and can be called to dispose on-demand.. Finally, here are some tests that passes testing out the UnitOfWork together with the generic repository pattern !

 
        [Test] 
        public void UnitOfWorkPerformsExpected()
        {
            var dbContext = GetContext();
            var unitOfWork = new UnitOfWork(dbContext);
     
            unitOfWork.AddRepository<OperationExternalEquipment>()
                .AddRepository<OperationDiagnoseCode>();

            var operationExternalEquipment = new OperationExternalEquipment
            {
                OperationId = 10296,
                EquipmentText = "Stent graft type ABC-123",
                OrderedDate = DateTime.Today
            };

            var operationDiagnoseCode = new OperationDiagnoseCode
            {
                OperationId = 10296,
                DiagnoseCodeId = "A09.9",
                IsCodePreFabricated = true                
            };
            
            //act 
            unitOfWork.Repository<OperationExternalEquipment>().Add(operationExternalEquipment);
            unitOfWork.Repository<OperationDiagnoseCode>().Add(operationDiagnoseCode);
            int savedResult = unitOfWork.Complete();

            savedResult.Should().Be(2); 

            //assert 
            var savedOperationEquipmentsForOperation = unitOfWork.Repository<OperationExternalEquipment>().Find(x => x.OperationId == 10296).ToList(); 
            savedOperationEquipmentsForOperation.Any(x => x.EquipmentText == "Stent graft type ABC-123").Should().BeTrue();

            var savedOperationDiagnoseCodesForOperation = unitOfWork.Repository<OperationDiagnoseCode>().Find(x => x.OperationId == 10296).ToList();
            savedOperationDiagnoseCodesForOperation.Any(x => x?.DiagnoseCodeId == "A09.9").Should().BeTrue();

            //cleanup
            unitOfWork.Repository<OperationExternalEquipment>().Remove(operationExternalEquipment);
            unitOfWork.Repository<OperationDiagnoseCode>().Remove(operationDiagnoseCode);
            savedResult = unitOfWork.Complete();

            savedResult.Should().Be(2);

        }

        [Test]
        public void UnitOfWorkCustomRepoPerformsExpected()
        {
            var dbContext = GetContext();
            var unitOfWork = new UnitOfWork(dbContext);
            unitOfWork.AddCustomRepository<OperationExternalEquipmentCustomRepo>();
            var operationExternalEquipment = new OperationExternalEquipment
            {
                OperationId = 10296,
                EquipmentText = "Stent graft type DEF-456",
                OrderedDate = DateTime.Today
            };         

            //act 
            unitOfWork.Repository<OperationExternalEquipment>().Add(operationExternalEquipment);
       
            int savedResult = unitOfWork.Complete();

            savedResult.Should().Be(1);

            //assert 
            var savedOperationEquipmentsForOperation = unitOfWork.Repository<OperationExternalEquipment>().Find(x => x.OperationId == 10296).ToList(); 
            savedOperationEquipmentsForOperation.Any(x => x.EquipmentText == "Stent graft type DEF-456").Should().BeTrue();

            //check if we can use a custom repo method !
            var equipmentRepo = unitOfWork.Repository<OperationExternalEquipment>() as OperationExternalEquipmentCustomRepo;
            var equipmentTexts = string.Join(",", equipmentRepo.GetEquipmentTexts(10296));
            bool foundText = equipmentTexts.Contains("Stent graft type DEF-456"); 
            foundText.Should().BeTrue();

            //cleanup
            unitOfWork.Repository<OperationExternalEquipment>().Remove(operationExternalEquipment);
            savedResult = unitOfWork.Complete();

            savedResult.Should().Be(1);

        }
 
 
The adjusted implementation of Repository now looks like this - I have renamed many of the methods to be more standard compared to other implementations of the repository pattern demonstrated online in different videos on Youtube for example. The updated interface for repository now looks like this:
 
  IRepository.cs
  
  using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace SomeAcme.Common.Interfaces
{

    /// <summary>
    /// Generic implementation of repository pattern for (should maybe have been implemented a decade ago to save some development time .. :-) to save some code in DAL-layer Data.EntityFramework ) 
    /// </summary>
    /// <typeparam name="T">Entity T (POCO for table)</typeparam>
    public interface IRepository<T> where T : class
    {

        /// <summary>
        /// Performs an insert of an entity
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="keyValues">If set, these key values are used to locate entity in db after the insertion has been performed if specifed by other param for saveImmediate</param>
        /// <param name="saveImmediate">Save immediately in db after adding the entity</param>
        T Add(T entity, bool saveImmediate = false);

        /// <summary>
        /// Performs an insert of multiple entities
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="saveImmediate">Save immediately after adding item in db</param>
        IEnumerable<T> AddRange(IEnumerable<T> entity, bool saveImmediate = false);

        /// <summary>
        /// Saves changes. Commits the data to the database.
        /// </summary>
        /// <param name="dbContext">Db context</param>
        void SaveChanges(object dbContext);

        /// <summary>
        /// Delete an entity specified by <paramref name="keyValues"/> to look up entity
        /// </summary>
        /// <param name="keyValues"></param>
        /// <returns></returns>
        T Remove(bool saveImmediate, params object[] keyValues);

        /// <summary>
        /// Deletes an entity specified by <paramref name="entity"/> from the database
        /// </summary>
        /// <param name="entity"></param>
        void Remove(T entity, bool saveImmediate = false);

        /// <summary>
        /// Removes entities specified by <paramref name="entities"/> from the database
        /// </summary>
        /// <param name="entities"></param>
        void RemoveRange(IEnumerable<T> entities, bool saveImmediate = false);

        /// <summary>
        /// Update <paramref name="entity"/>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="saveImmediate">Save immediate if set to true</param>
        /// <returns></returns>
        T Update(T entity, bool saveImmediate, params object[] keyValues);

        /// <summary>
        /// Equivalent to a 'GetById' method, but tailored for generic use. 
        /// Retrieves <paramref name="idSelector"/> specified by <paramref name="idValue"/>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyValues">Key values to use to find entity</param>
        T Get(bool asNoTracking = true, params object[] keyValues);

        /// <summary>
        /// Retrieves entities of type <typeparamref name="T"/> via predicate <paramref name="condition"/>.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        /// <param name="asNoTracking">If true, does not track items (less chance of db locks due to turning off change tracking) </param>
        IEnumerable<T> Find(Expression<Func<T, bool>> condition, bool asNoTracking = true);

        /// <summary>
        /// Retrieves an entity of type <typeparamref name="T"/> via predicate <paramref name="condition"/>.
        /// If not found, null is returned.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        T GetByCondition(Expression<Func<T, bool>> condition);

        /// <summary>
        /// Retrieve all the entities specified by <typeparamref name="T"/>.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        /// <param name="asNoTracking">If true, does not track items (less chance of db locks due to turning off change tracking) </param>
        IEnumerable<T> GetAll(bool asNoTracking = true); 

    }
}

 

And the implementation looks like this:
 
 
 using SomeAcme.Common.Interfaces;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

namespace SomeAcme.Data.EntityFramework.Managers
{
    public class Repository<T> : IRepository<T>, IDisposable where T : class
    {
        private readonly System.Data.Entity.DbContext _dbContext;

        public Repository(System.Data.Entity.DbContext dbContext)
        {
            _dbContext = dbContext;
        }

        public T Add(T entity, bool saveImmediate = false)
        {
            return ExecuteQuery((T obj, System.Data.Entity.DbContext dbContext) =>
            {
                var entityDb = dbContext.Set<T>().Add(entity);                
                if (saveImmediate)
                    SaveChanges(dbContext);
                return entityDb;

            }, entity);
        }

        public T Add(T entity, bool saveImmediate = false, params object[] keys)
        {
            return ExecuteQuery((T obj, System.Data.Entity.DbContext dbContext) =>
            {
                dbContext.Entry(obj).State = EntityState.Added;
                if (saveImmediate)
                    SaveChanges(dbContext);
                var entityInDb = dbContext.Set<T>().Find(keys);
                return entityInDb;
            }, entity);
        }

        public IEnumerable<T> AddRange(IEnumerable<T> entities, bool saveImmediate)
        {
            var entitites = _dbContext.Set<T>().AddRange(entities);
            if (saveImmediate)
                SaveChanges(_dbContext);
            return entitites;
        }

        public T Remove(bool saveImmediate = false, params object[] keyValues)
        {
            var entity = _dbContext.Set<T>().Find(keyValues);
            if (entity == null)
                return null;
            var entry = _dbContext.Entry(entity);
            if (entry == null)
                return null;
            entry.State = EntityState.Deleted;
            if (saveImmediate)
                SaveChanges(_dbContext);
            return entity;
        }

        public void Remove(T entity, bool saveImmediate = false)
        {
            _dbContext.Set<T>().Remove(entity);
            if (saveImmediate)
                SaveChanges(_dbContext);
        }

        public void RemoveRange(IEnumerable<T> entities, bool saveImmediate = false)
        {
            _dbContext.Set<T>().RemoveRange(entities);
            if (saveImmediate)
                SaveChanges(_dbContext);
        }

        /// <summary>
        /// Note - requiring here that we have defined primary key(s) on the target tables ! 
        /// </summary>
        /// <param name="keyValues"></param>
        /// <returns></returns>
        public T Get(params object[] keyValues)
        {
            var entity = _dbContext.Set<T>().Find(keyValues);
            _dbContext.Entry(entity).State = EntityState.Detached;
            return entity;
        }

        public IEnumerable<T> GetAll(bool asNoTracking = true)
        {
            return asNoTracking ? _dbContext.Set<T>().AsNoTracking() : _dbContext.Set<T>();
        }

        public IEnumerable<T> Find(Expression<Func<T, bool>> condition, bool asNoTracking = true)
        {
            IQueryable<T> query = asNoTracking ? _dbContext.Set<T>().AsNoTracking() : _dbContext.Set<T>();
            var entities = query.Where(condition);
            return entities;
        }

        public T GetByCondition(Expression<Func<T, bool>> condition)
        {
            IQueryable<T> query = _dbContext.Set<T>().AsNoTracking();
            var entities = query.Where(condition);
            return entities.FirstOrDefault();
        }

        public bool ExistsByCondition(Expression<Func<T, bool>> condition)
        {
            IQueryable<T> query = _dbContext.Set<T>().AsNoTracking();
            return query.Any(condition);

        }

        public T Get(bool asNoTracking, params object[] keyValues)
        {
            var entity = asNoTracking ? _dbContext.Set<T>().AsNoTracking().FirstOrDefault() : _dbContext.Set<T>().Find(keyValues);
            return entity;
        }

        public void SaveChanges(object context)
        {
            var dbContext = context as System.Data.Entity.DbContext;
            if (dbContext == null)
            {
                throw new ArgumentException($"dbContext object inside save method : Must be of type System.Data.Entity.DbContext", nameof(context));
            }
            dbContext.SaveChanges(); 
        }

        public T Update(T entity, bool saveImmediate = false, params object[] keyValues)
        {
            return ExecuteQuery((T obj, System.Data.Entity.DbContext dbContext) =>
            {
                var entityInDb = dbContext.Set<T>().Find(keyValues);
                if (entityInDb == null)
                    return null; 
                dbContext.Entry(entityInDb).CurrentValues.SetValues(obj);                
                if (saveImmediate)
                {
                    SaveChanges(dbContext);
                }
                return obj;
            }, entity);            
        }

        private T ExecuteQuery(Func<T, System.Data.Entity.DbContext, T> query, T entity)
        {
            T result = query(entity, _dbContext);
            return result;
        }

        public void Dispose()
        {
            Dispose(true); 
        }

        private void Dispose(bool isDisposing)
        {
            if (isDisposing)
            {
                _dbContext?.Dispose();
                GC.SuppressFinalize(this);
            }
        }

    }
}

 
 
Totally, the code of the unit of work and repository pattern is about 300 lines of code combined. It should match a lot of Data Access Layer (DAL) implementations of Entity Framework, possible we could reduce a lot of code here in many projects by following these patterns which are accepted data access patterns defined by the 'Gang of four' way back many decades ago. If I would adjust this code next I would do these modifications :
  • UnitOfWork should only allow adding custom repos in addition to some predefines repos. This should give an overall simplification of UnitOfWork
  • Specific transaction handling should be added, like setting the transaction isolation scope. Also speifically doing rollback in case anything crashes in UnitOfWork
  • Possible add some more shared utility methods inside Repository class if such should be added

Sunday 20 December 2020

Outputting runnable SQL from Entity Framework 6.x

This article will describe how you can output runnable SQL from Entity Framework. The output will be sent to the Console and Debug. You can easily modify this to output to other output sources, such as tracing or files for that matter. What is important is that we need to interpolate the parameters from Entity Framework so that we get a runnable SQL. Entity Framework parameterizes the SQL queries such that SQL injection is avoided. Where conditions and similar are inserted into parameters, notably with the p__linq naming convention. We will interpolate these parameters into runnable SQL such that you can paste SQL into SQL Server Management Studio (SMSMS). Or you could save the runnable SQL to a .sql file and let SQLCMD run it from command line. Either way, we must set up the DbContext to do this. I am using Entity Framework 6.2.0. It should be possible to use this technique with all EF 6.x version. In Entity Framework Core and Entity Framework Core 2, the techniques will be similar. First define a DbConfiguration and attribute the DbContext class you are using like this with the DbConfigurationType (we are not considering ObjectContext in this article, but DbContext is a wrapper around this class anyways, so you should be apply to techniques taught here to other scenarios).
SomeAcmeDbContext.cs
namespace SomeAcme.Data.EntityFramework { [DbConfigurationType(typeof(SomeAcmeDataContextConfiguration))] public partial class SomeAcmeDataContext : System.Data.Entity.DbContext, ISomeAcmeDataContext { ..
Ok, so our DbConfiguration just inherits from DbConfiguration and sets up a custom DatabaseLogFormatter like this:
  
SomeAcmeDataContextConfiguration.cs
using System.Data.Entity; namespace SomeAcme.Data.EntityFramework.DbContext { public class SomeAcmeDataContextConfiguration : DbConfiguration { public SomeAcmeDataContextConfiguration() { SetDatabaseLogFormatter((context, logAction) => new SomeAcmeDbLogFormatter(context, logAction)); } } }
SetDatabaseLogFormatter is a protected method o DbConfiguration. Our DatabaseLogFormatter implementation then looks like this:
 
SomeAcmeDbLogFormatter.cs
using System; using System.Data.Common; using System.Data.Entity.Infrastructure.Interception; using SomeAcme.Data.EntityFramework.DbContext.Extensions; namespace SomeAcme.Data.EntityFramework.DbContext { public class SomeAcmeDbLogFormatter : DatabaseLogFormatter { public SomeAcmeDbLogFormatter(System.Data.Entity.DbContext dbContext, Action<string> loggingAction) : base(dbContext, loggingAction) { } public override void LogCommand<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext) { string cmdText = command.CommandText; if (string.IsNullOrEmpty(cmdText)) return; if (cmdText.StartsWith("Openend connection", StringComparison.InvariantCultureIgnoreCase) || cmdText.StartsWith("Closed connection", StringComparison.InvariantCultureIgnoreCase)) return; Write($"--DbContext {Context.GetType().Name} is executing command against DB {Context.Database.Connection.Database}: {Environment.NewLine}{command.GetGeneratedQuery().Replace(Environment.NewLine, "")} {Environment.NewLine}"); } public override void LogResult<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext) { //empty by intention } } }
We also have a helper extension method called GetGeneratedQuery on DbCommand objects to help us get the cruft of this article - the interpolated runnable query. From my testing we can just interpolate the parameters as is in most use cases. However, some datatypes in the T-SQL world must be quoted (like, strings) and we need to adjust the date and time data types to a runnable format too. In case you find this helper method should be interpolated, please let me know. Our helper method GetGeneratedQuery looks like this:
  
SomeAcmeDbCommandExtensions.cs
using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Linq; using System.Text; namespace SomeAcme.Data.EntityFramework.DbContext.Extensions { public static class DbCommandExtensions { /// <summary> /// Returns the generated sql string where parameters are replaced by value. Generated a runnable /// SQL script. Note that this is an approximation anwyays, but gives us a runnable query. The database server query engine optimizer will possible rewrite /// even simple queries if it sees it possible to rearrange the query to predictively create a more efficient query. /// </summary> /// <param name="dbCommand"></param> /// <returns></returns> public static string GetGeneratedQuery(this DbCommand dbCommand) { DbType[] quotedParameterTypes = new DbType[] { DbType.AnsiString, DbType.Date, DbType.DateTime, DbType.DateTime2, DbType.Guid, DbType.String, DbType.AnsiStringFixedLength, DbType.StringFixedLength }; var sb = new StringBuilder(); sb.AppendLine(dbCommand.CommandText); var arrParams = new SqlParameter[dbCommand.Parameters.Count]; dbCommand.Parameters.CopyTo(arrParams, 0); //copy dbCommand parameters into another collection to avoid //mutating the query and be able to run a foreach loop foreach (SqlParameter p in arrParams.OrderByDescending(p => p.ParameterName.Length)) { string value = p.Value.ToString(); if (p.DbType == DbType.Date || p.DbType == DbType.DateTime || p.DbType == DbType.DateTime2) { value = DateTime.Parse(value).ToString("yyyy-MM-dd HH:mm:ss.fff"); } if (quotedParameterTypes.Contains(p.DbType)) value = "'" + value + "'"; sb.Replace("@" + p.ParameterName, value); } return sb.ToString(); } } }
We also need to activate database logging in the first place. Database logging to the console and debug should be avoided in production in ordinary cases, as they make a performance impact. Instead, it is handy to turn it on or off via an app setting. I have decided to only allow it while debugging so my constructors of my DbContext where I have tested it calls this method:
SomeAcmeDbContext.cs
(once more need to add some code) private void SetupDbContextBehavior() { Configuration.AutoDetectChangesEnabled = true; Configuration.LazyLoadingEnabled = true; ObjectContext.CommandTimeout = 10 * 60; #if DEBUG //To enable outputting database traffic to the console, set the app setting OutputDatabaseTrafficLogging in web.config to true //this must not be activated in production. To safe guard this, //this block below is wrapped in the debug preprocessor directive. bool outputDatabaseTrafficLogging = ConfigurationManagerWrapper.GetAppsetting(SomeAcme.Common.Constants.OutputDatabaseTrafficLogging); if (outputDatabaseTrafficLogging) { Database.Log = s => { if (s.StartsWith("Opened connection", StringComparison.InvariantCultureIgnoreCase) || s.StartsWith("Closed connection", StringComparison.InvariantCultureIgnoreCase)) return; Console.WriteLine(s); Debug.WriteLine(s); }; } #endif
Never mind the first three lines, they are just added here as tips for additional settings you CAN set if you want to. The important bit is the Database.Log delegate property, which acceps a lambda for example where you set up what to do with the logging. Here we just tell the DbContext that if the app setting OutputDatabaseTrafficLogging is set to true, we output the runnable SQL from Entity Framework to the console. That's all there is to it! You can now activate the app setting and see in the debug output (or in console) runnable SQL. And you can paste the SQL into SMSS for example to check for performance issues such as missing indexes and similar or tune up the size of the result sets and alter the SQL. You should also consider making your DbContext runnable in Linqpad for easier tuning of EF queries, but that is for another article. Happy coding!

Sunday 5 April 2020

Deploying an SQL Express database in Azure Devops pipeline with YAML and generating and updating the database with migrate scripts using EF Core Code First tools

Here a full example of how I achieved running Integration tests using Sql Express in Azure Devops. I had to use the YAML based pipelines so I could use simonauner's approach using Chocolatey to install Sql Express. Make note that I had to install EF Core tools since I use .Net Core 3.1 in this pipeline. Also note that I generate an EF Code First migration SQL file on the fly so that the rigged SQL Express instance is filled with contents. Deploy SQL Express instance in Azure Devops, install and generate and run EF Code first migration sql script to update database with schema and seed data using EF Code First tools.

# ASP.NET Core (.NET Framework)
# Build and test ASP.NET Core projects targeting the full .NET Framework.
# Add steps that publish symbols, save build artifacts, and more:
# https://docs.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- feature/testability

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- script: choco install sql-server-express
- task: NuGetToolInstaller@1

- task: VisualStudioTestPlatformInstaller@1
  displayName: 'Visual Studio Test Platform Installer'
  inputs:
    versionSelector: latestStable

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: DotNetCoreCLI@2
  displayName: Build
  inputs:
    command: build
    projects: '**/*.csproj'
    arguments: '--configuration Debug' # Update this to match your need

- script: 'dotnet tool install --global dotnet-ef'
  displayName: 'Generate EF Code First Migrations SQL Script Update script'

- script: 'dotnet ef migrations script -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql --project .\SomeAcme\SomeAcme.csproj'
  displayName: 'Generate EF Code First Migrations migrate.sql'

- script: 'sqlcmd -S .\SQLEXPRESS -Q "CREATE DATABASE [SomeAcmeDb]"'
  displayName: 'Create database SomeAcmeDb in Azure Devops SQL EXPRESS'

- script: 'sqlcmd -i %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql -S .\SQLEXPRESS -d SomeAcmeDb'
  displayName: ' Run migrate.sql on SQL EXPRESS in Azure Devops'

# PowerShell
# Run a PowerShell script on Linux, macOS, or Windows
- task: PowerShell@2
  inputs:
    targetType: 'inline' # Optional. Options: filePath, inline
    #filePath: # Required when targetType == FilePath
    #arguments: # Optional
    script: 'gci -recurse -filter *.dll' # Required when targetType == Inline
    #errorActionPreference: 'stop' # Optional. Options: stop, continue, silentlyContinue
    #failOnStderr: false # Optional
    #ignoreLASTEXITCODE: false # Optional
    #pwsh: false # Optional
    #workingDirectory: # Optional

- task: VSTest@2
  displayName: 'VsTest - testAssemblies'
  inputs:
    testAssemblyVer2: |
     **\*SomeAcme.Tests.dll
     !**\*TestAdapter.dll
     !**\obj\**
    vsTestVersion: toolsInstaller
    testFiltercriteria: 'Category=IntegrationTest'
    runInParallel: false
    codeCoverageEnabled: false
    testRunTitle: 'XUnit tests SomeAcme solution integration test starting'
    failOnMinTestsNotRun: true
    rerunFailedTests: false

Thursday 1 August 2019

Consistency guard of enums used in Entity Framework

This is a consistency guard for enums in Entity Framework. It is a mechanism for protecting an entity in Entity Framework or just EF, in case an enum value was loaded from the database with an illeagal value. An illeagal enum value would be any value of an enum that cannot be parsed into an enum. We use Enum.IsDefined method (at first running Convert.ChangeType) to check if the value for the enum is leagal or not. We define a helper class BrokenEnumValue to contain our metadata about enum values that are illeagal or 'broken'. The rest of the code in this article goes into the DbContext class (Or ObjectContext would also work) that EF uses. The ObjectMaterialized event is added in the constructor for example.
            var objectContext = ((IObjectContextAdapter) this).ObjectContext;
            _log = (ILog) AutofacHostFactory.Container.Resolve(typeof(ILog));
            objectContext.ObjectMaterialized += ObjectContext_ObjectMaterialized;
Our helper POCO:

    public class BrokenEnumValue
    {
        public string PropertyName { get; set; }
        public string PropertyTypeName { get; set; }
        public Guid? SchemaGuid { get; set; }
        public string OldValue { get; set; }
        public string CorrectedValue { get; set; }

        public override string ToString()
        {
            return $"{PropertyName} {PropertyTypeName} {SchemaGuid} {OldValue} {CorrectedValue}";
        }
    }



        private void ObjectContext_ObjectMaterialized(object sender, ObjectMaterializedEventArgs e)
        {
            var brokenEnumProperties = FixBrokenEnumProperties(e.Entity);
            if (brokenEnumProperties.Any())
            {
                Type objType = e.Entity.GetType();
                var idProperty = objType.GetProperty("Id");
                Guid? schemaGuid = idProperty?.GetValue(e.Entity, null) as Guid?;
                foreach (var brokenEnum in brokenEnumProperties)
                    brokenEnum.SchemaGuid = schemaGuid;
                string brokenEnumsInfo = string.Join(" ", brokenEnumProperties.Select(b => b.ToString()).ToArray());
                _log.WriteWarning($"Detected broken enum propert(ies) in entity and resolved them to default value if available in enum (None): {brokenEnumsInfo}");
            }
        }

           public IList<BrokenEnumValue> FixBrokenEnumProperties(object obj)
        {
            var list = new List<BrokenEnumValue>();
            try
            {
                if (obj == null) return list;
       
                PropertyInfo[] properties = obj.GetType().GetProperties();
                foreach (PropertyInfo property in properties)
                {
                    if (property.GetIndexParameters()?.Any() == true)
                        continue; //skip indexer properties
                    if (property.PropertyType.IsArray)
                    {
                        Array a = (Array) property.GetValue(obj);
                        for (int i = 0; i < a.Length; i++)
                        {
                            object o = a.GetValue(i);
                            list.AddRange(FixBrokenEnumProperties(o));
                        }
                        continue; //continue to next iteration
                    }
                    object propValue = property.GetValue(obj, null);
                    var elems = propValue as IList;
                    if (elems != null)
                    {
                        foreach (var item in elems)
                        {
                            list.AddRange(FixBrokenEnumProperties(item));
                        }
                    }
                    else
                    {
                        if (property.PropertyType.IsEnum && !IsEnumDefined(propValue, property.PropertyType))
                        {
                            var correctedValue = GetDefaultEnumValue(propValue, property.PropertyType);
                            list.Add(new BrokenEnumValue
                            {
                                CorrectedValue = correctedValue?.ToString(),
                                OldValue = propValue?.ToString(),
                                PropertyName = property.Name,
                                PropertyTypeName = property.PropertyType.FullName,
                            });
                            property.SetValue(obj, correctedValue);
                            
                        }
                        if (property.PropertyType.IsClass && (property.PropertyType.GetCustomAttributes(typeof(DataContractAttribute))?.Any() == true)
                                                          && !(property.PropertyType == typeof(string)) && !property.PropertyType.IsValueType)
                        {
                            list.AddRange(FixBrokenEnumProperties(propValue));
                        }
                    }
                }
            }
            catch (Exception err)
            {
                _somelog.WriteError($"Expection occurred trying to fix broken enum properties: {err}");
            }
            return list;
        }

           private static T GetDefaultEnumValue<T>(T entity, Type propertyType)
           {
               foreach (var enumValue in propertyType.GetEnumValues())
               {
                   if (String.Compare(enumValue.ToString(), "None", StringComparison.OrdinalIgnoreCase) == 0)
                   {
                       return (T)enumValue;
                   }
               }
               return entity;
           }

        private static bool IsEnumDefined(object entity, Type propertyType)
        {
            var castedValue = Convert.ChangeType(entity, propertyType);
            return Enum.IsDefined(propertyType, castedValue);
        }


With this guard, we can avoid that the entity does not load in case an illeagal value was loaded for a given enum. Note that our fallback is looking for the enum value mapping to the [None] enum member, so we fallback to the [None] enum value, if it exists. Mosts enum should have a [None] member mapping to the enum integer value 0. You can of course adjust the strategy used here. I believe such a consistency guard would be helpful for many applications using EF.

Thursday 2 May 2019

Regenerating precompiled views in Entity Framework

This article will present a solution to regenerate precompiled views in Entity Framework. Precompiled views can have a dramatic effect on the startup time of your DbContext / ObjectContext, especially the time to execute the first query against the database. First off, the following class can generate these precompiled views:

using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;

namespace PrecompiledViewGenerator
{
    /// <summary>
    /// Capable of generating pre compiled views that EF can use for quicker startup time when application domains starts.
    /// </summary>
    /// <typeparam name="TDbContext"></typeparam>
    /// <param name="dbContext"></param>
    /// <remarks>See https://github.com/ErikEJ/EntityFramework6PowerTools/tree/community/src/PowerTools Github page for the source code of EF Power tools.</remarks>
    /// <returns>A string containing the precompiled views that can be written to file or database for later use to gain optimized startup speeds of EF in application domains.</returns>
    public class EntityFrameworkPrecompiledViewGenerator
    {
        /// <summary>
        /// Generates pre compiled views from a db context. Uses EF Powertools runtime T4 template (.tt file) for precompiled view generation of views for EF 6 ObjectContext for C#.
        /// </summary>
        /// <typeparam name="TDbContext"></typeparam>
        /// <param name="dbContext"></param>
        /// <param name="viewContainerSuffix">The suffix to apply in the generated file containing the precompiled views</param>
        /// <remarks>See https://github.com/ErikEJ/EntityFramework6PowerTools/tree/community/src/PowerTools Github page for the source code of EF Power tools.</remarks>
        /// <returns>A string containing the precompiled views that can be written to file or database for later use to gain optimized startup speeds of EF in application domains.</returns>
        public string GeneratePrecompiledViews<TDbContext>(TDbContext dbContext, string viewContainerSuffix) where TDbContext : IObjectContextAdapter
        {
            if (string.IsNullOrEmpty(viewContainerSuffix))
                throw new ArgumentNullException(nameof(viewContainerSuffix));
            var objectContext = (dbContext as IObjectContextAdapter)?.ObjectContext; 
            if (objectContext == null)
                throw new ArgumentNullException(nameof(dbContext));
            var viewGenerator = new CSharpViewGenerator();
            var mappingCollection = (StorageMappingItemCollection) objectContext.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace);
            if (mappingCollection == null)
                throw new ArgumentNullException(nameof(dbContext));
            var listOfEdmSchemaErrors = new List<EdmSchemaError>();
            var views = mappingCollection.GenerateViews(listOfEdmSchemaErrors);
            listOfEdmSchemaErrors.ForEach(error =>
            {
                if (error.Severity == EdmSchemaErrorSeverity.Error)
                    throw new ArgumentOutOfRangeException($"An error occurred while trying to generate views for {dbContext.GetType().Name}. The error was: {error.ToString()}");
            });
            viewGenerator.ContextTypeName = dbContext.GetType().FullName;
            viewGenerator.MappingHashValue = mappingCollection.ComputeMappingHashValue();
            viewGenerator.ViewContainerSuffix = viewContainerSuffix;
            viewGenerator.Views = views;
            string precompiledViews = viewGenerator.TransformText();
            return precompiledViews;
        }
    }
}



The following runtime text template (.T4) is used to generate the precompiled views:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System.Data.Entity.Infrastructure.MappingViews;

[assembly: DbMappingViewCacheTypeAttribute(
    typeof(<#= ContextTypeName #>),
    typeof(Edm_EntityMappingGeneratedViews.ViewsForBaseEntitySets<#= ContextTypeName #>))]

namespace Edm_EntityMappingGeneratedViews
{
    using System;
    using System.CodeDom.Compiler;
    using System.Data.Entity.Core.Metadata.Edm;

    /// <summary>
    /// Implements a mapping view cache.
    /// </summary>
    [GeneratedCode("Entity Framework 6 Power Tools", "0.9.2.0")]
    internal sealed class ViewsForBaseEntitySets<#= ViewContainerSuffix #> : DbMappingViewCache
    {
        /// <summary>
        /// Gets a hash value computed over the mapping closure.
        /// </summary>
        public override string MappingHashValue
        {
            get { return "<#= MappingHashValue #>"; }
        }

        /// <summary>
        /// Gets a view corresponding to the specified extent.
        /// </summary>
        /// <param name="extent">The extent.</param>
        /// <returns>The mapping view, or null if the extent is not associated with a mapping view.</returns>
        public override DbMappingView GetView(EntitySetBase extent)
        {
            if (extent == null)
            {
                throw new ArgumentNullException("extent");
            }

            var extentName = extent.EntityContainer.Name + "." + extent.Name;
<#
    var index = 0;
    foreach (var view in Views)
    {
#>

            if (extentName == "<#= view.Key.EntityContainer.Name + "." + view.Key.Name #>")
            {
                return GetView<#= index #>();
            }
<#
        index++;
    }
#>

            return null;
        }
<#
    index = 0;
    foreach (var view in Views)
    {
#>

        /// <summary>
        /// Gets the view for <#= view.Key.EntityContainer.Name + "." + view.Key.Name #>.
        /// </summary>
        /// <returns>The mapping view.</returns>
        private static DbMappingView GetView<#= index #>()
        {
            return new DbMappingView(@"<#= view.Value.EntitySql #>");
        }
<#
        index++;
    }
#>
    }
}
<#+
    public string ContextTypeName { get; set; }
 public string ViewContainerSuffix { get; set; }
    public string MappingHashValue { get; set; }
    public dynamic Views { get; set; }
#>

This T4 file is compiled into the class CSharpViewGenerator. Our DbContext can now check the mapping hash value of the loaded precompiled views file in your assembly and compute in again to quickly assert if the database is in sync with the precompiled views. The following code can establish both a check that the precompiled views are in sync and run code that will regenerate the precompiled views file of which the developer can then copy from Notepad and into the precompiled view file again. Not perhaps an elegant solution, but it lets the developer easily check and keep the precompiled views file updated and in sync. And you do not need to install the EF Powertools extension either, as I use the .tt file and much of the source code from there which is part of the "Generate views" command anywyays! Sample DbContext is then:
using System;
using System.Data.Entity;
using System.Data.Entity.Core;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Core.Metadata.Edm;
using System.Diagnostics;
using System.IO;
using System.Runtime.CompilerServices;
using Edm_EntityMappingGeneratedViews;

using PrecompiledViewGenerator;

namespace SomeAcme.SomeRegistry.Data
{
    public class SomeDatabaseMigrationsFactory : DatabaseMigrationsFactory<SomeDatabase>
    {
    }

    public class SomeDatabase : SomeDatabaseBaseClass
    {
        public DbSet<SomeEntity> SomeEntity { get; set; }

        public SomeDatabase(ISomeDependency dep)
            : base(dep)
        {
            var someEntityPrecompiledViewsMapping = new ViewsForBaseEntitySetsSomeEntityDatabase();
            var mappingCollection = (StorageMappingItemCollection)ObjectContext.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace);
            string mappingHashValue =  mappingCollection.ComputeMappingHashValue();
            if (SomeEntityPrecompiledViewsMapping.MappingHashValue != mappingHashValue)
            {
                var precompiledViewGenerator = new EntityFrameworkPrecompiledViewGenerator();
                string precompiledViewsContents = precompiledViewGenerator.GeneratePrecompiledViews(this, this.GetType().Name);
                string viewFileForMainDb = "SomeEntityDatabase.Views.cs";
                string temporaryPrecompiledViewFile = Path.Combine(Path.GetTempPath(), viewFileForMainDb);
                try
                {
                    File.WriteAllText(temporaryPrecompiledViewFile, precompiledViewsContents);
                    Process.Start("c:\\windows\\notepad.exe", temporaryPrecompiledViewFile);
                }
                catch (Exception err)
                {
                    throw new Exception("An error occured while trying to regenerate precompiled views for EF since the database changed. Error is: " + err);
                }
                throw new EntityCommandCompilationException($"The precompiled views file is not in sync with the database any longer. Replace the file {viewFileForMainDb} with the generated new contents!");
            }
        }
    }
}

The overall execution of code is the following:
  • In the DbContext constructor - check that the computed mapping hash value matches to that of the of the CSSpace storagemapping collection that your precompiled views file contains (MappingHashValue)
  • If the hash values do not agree, it is necessary to regenerate the pre compiled views file again. The contents are generated and written to a temporary file.
  • Notepad or similar is launched telling the developer to replace the pre compiled views file contents with this new contents.
  • An EntityCommandCompilationException is thrown as this is the same type of exception that is thrown if the precompiled views file does not agree
  • Developer replaces the contents and rebuilds the solution
  • The next time the startup time of EF should be reduced significantly again and work, since our DbContext and precomplied views are in sync again
Below is a sample of an exception thrown when my DbContext was not in sync with the precompiled views file:


System.Data.Entity.Core.EntityCommandCompilationException

System.Data.Entity.Core.EntityCommandCompilationException
  HResult=0x8013193B
  Message=An error occurred while preparing the command definition. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
   at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   atProgram.cs:line 164

Inner Exception 1:
MappingException: The current model no longer matches the model used to pre-generate the mapping views, as indicated by the ViewsForBaseEntitySetsabf8c33ac61e42fc601fe7446b41eaf48c7577efe6d6e17ccccc2b434793c28e.MappingHashValue property. Pre-generated mapping views must be either regenerated using the current model or removed if mapping views generated at runtime should be used instead. See http://go.microsoft.com/fwlink/?LinkId=318050 for more information on Entity Framework mapping views.

 

Tuesday 24 July 2018

Getting started with Transactions in WCF

Transactions is a powerful concept in many parts of the software industry, they ensure that two or more procedures are either all carried out and persisted, or not any of them. This ensures that data is consistent, that the operation that performs the procedures are atomic, it is isolated and durable (ACID-principle). But what about WCF? Is it possible to easily support transactions in WCF in a full-stack scenario? Can you try to carry out two or more WCF service calls and either have all the calls changes on data in for example a SQL Server database persisted or not any of them? Is it possible to make a transaction that spans two or more WCF service calls? Yes it is! I have created a sample solution here that you can clone with Git:

git clone https://toreaurstad@bitbucket.org/toreaurstad/wcfdemotransactions.git

The repository with the code sample is available as a public repository on Bitbucket where you can view the code here: https://bitbucket.org/toreaurstad/wcfdemotransactions/src/master/ The code sample is a full-stack WPF application with a backend implemented in WCF serviced under WAS / IIS and the data layer uses Entity Framework and Model first (EDMX). This scenario will display that we can implement transactions that span multiple WCF calls and be able to either commit the update of data in the database that all these WCF calls inflict, or abort them all, i.e. a transaction. The GUI looks like this:


Enabling transactions for the WCF service

First off, enable transactionflow on the binding of the service (web.config)

  <system.serviceModel>

    <bindings>
      <wsHttpBinding>
        <binding name="wsHttpBindingWithTransactionFlow"  transactionFlow="true" >
          <security>
            <transport clientCredentialType="None"></transport>
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>

    <services>
      <service name="WcfTransactionsDemo.ServiceImplementation.SampleServiceImplementation" behaviorConfiguration="SampleServiceBehavior">
        <endpoint bindingConfiguration="wsHttpBindingWithTransactionFlow" binding="wsHttpBinding" address="http://localhost/WcfTransactionsDemo.Host/sampleservice.svc" contract="WcfTransactionsDemo.Common.ServiceContract.ISampleServiceContract"></endpoint>
      </service>
    </services>

 
Next, define that the transactionflow from the client is mandatory in the WCF methods that will support this in the Service Contract of the WCF service: (this is done setting the TransactionFlow attribute to Mandatory on the WCF service methods (operations) that will join a transaction flowed downstream from the client.

 [ServiceContract(Namespace = Constants.ServiceContractsNamespace)]
    public interface ISampleServiceContract
    {

        [OperationContract]
        [FaultContract(typeof(FaultDataContract))]
        [TransactionFlow(TransactionFlowOption.NotAllowed)]
        List GetAllCustomers();

        [OperationContract]
        [FaultContract(typeof(FaultDataContract))]
        [TransactionFlow(TransactionFlowOption.NotAllowed)]
        List GetAllProducts();

        [OperationContract]
        [FaultContract(typeof(FaultDataContract))]
        [TransactionFlow(TransactionFlowOption.Mandatory)]
        string PlaceOrder(OrderDataContract order);

        [OperationContract]
        [FaultContract(typeof(FaultDataContract))]
        [TransactionFlow(TransactionFlowOption.Mandatory)]
        string AdjustInventory(int productId, int quantity);

        [OperationContract]
        [FaultContract(typeof(FaultDataContract))]
        [TransactionFlow(TransactionFlowOption.Mandatory)]
        string AdjustBalance(int customerId, decimal amount);

    }
Next, specify the transaction isolation level of the WCF service implementation, using a ServiceBehavior attribute.

    [ServiceBehavior(TransactionIsolationLevel = IsolationLevel.Serializable, TransactionTimeout = "00:00:30")]
    public class SampleServiceImplementation : ISampleServiceContract
    {
Serializable is default in .NET and provides the consistency, it is though not recommended in high traffic scenarios as it causes too much database locking. (ReadCommitted can then be used for instance instead) Each WCF method in the service implementation that will join a transaction flowing from the client now specifies this with an OperationBehavior attribute, for example as the sample solution's AdjustBalance method:
         [OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)] 
        public string AdjustBalance(int customerId, decimal amount)
        {

Setting up a transaction at the client side

The WCF service is now configured to support transactions in our sample demo. Moving on next to the app.config file, updating service reference should set the transaction flow attribute correct. Note that the servicePrincipalName in this demo must be adjusted to match your computer's name (or use localhost).

<system.serviceModel>
        <bindings>
            <wsHttpBinding>
                <binding name="WSHttpBinding_ISampleServiceContract" transactionFlow="true"  />
            </wsHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://localhost/WcfTransactionsDemo.Host/sampleservice.svc"
                binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_ISampleServiceContract"
                contract="SampleService.ISampleServiceContract" name="WSHttpBinding_ISampleServiceContract">
                <identity>
                    <servicePrincipalName value="host/AlienHivemind" />
                </identity>
            </endpoint>
        </client>
    </system.serviceModel>

The demo of this article works in the following manner :
  • Client selects a row from the list of customers in the GUI
  • Client selects a row from the list of products in the GUI
  • Client enters a quantity of the selected product to order
  • Client clicks Place Order to place the order
If the client does this, three WCF service calls are invoked. If no transactions were done, the client could cause inconsistent data in the database. There are three WCF calls (this scenario is perhaps not so realistic, but it gives you the idea of WCF calls that are related and should all succeed or neither succeed. Ignore for now the semantics and just accept the fact that there are multiple WCF calls that must succeed or not any of them).

Now try to do the following: Do not select a customer but select a product. Also enter a quantity such as 10. Then click the button to place an order. What happens is that the WCF service expects the client to have selected a customer and a product. Since the client has not selected a customer, the AdjustBalance() methods throws a FaultException at the WCF service. The method AdjustInventory() however succeeds. If there was no transaction scope at the client side, you would see that the InStock / OnHand value is reduced, but there are no Balance reduction on either Customer, since the client forgot to select a Customer. Actually, using WCF transaction, it is possible to roll back data and get consistent data still - since the client defines a transaction. The client does it in the following manner:


    private void PlaceOrderCommandExecute(object obj)
        {
            using (var client = new SampleServiceContractClient())
            {

                using (var transactionScope = new TransactionScope())
                {
                    try
                    {


                        string orderPlacement = client.PlaceOrder(new OrderDataContract
                        {
                            CustomerId = SelectedCustomer != null ? SelectedCustomer.CustomerId : 0,
                            ProductId = SelectedProduct != null ? SelectedProduct.ProductId : 0,
                            Quantity = Quantity
                        });
                        MessageBox.Show("Placing order: " + orderPlacement);



                        string adjustedInventory = client.AdjustInventory(SelectedProduct != null ?
                            SelectedProduct.ProductId : 0, -1 * Quantity);

                        MessageBox.Show("Adjusting inventory: " + adjustedInventory);


                        string adjustedBalance = client.AdjustBalance(SelectedCustomer != null ?
                            SelectedCustomer.CustomerId : 0, -1 * (SelectedProduct != null && SelectedProduct.Price > 0 ? SelectedProduct.Price.Value : 0) * Quantity);

                        MessageBox.Show("Adjusting balance: " + adjustedBalance);

                        transactionScope.Complete(); 

                    }
                    catch (FaultException err)
                    {
                        MessageBox.Show("FaultException: " + err.Message);
                    }
                    catch (ProtocolException perr)
                    {
                        MessageBox.Show("ProtocolException: " + perr.Message);
                    }
                }

                try
                {
                    LoadCustomers();
                    LoadProducts();
                }
                catch (Exception err)
                {
                    Console.WriteLine(err.Message);
                }
            }
        }

As the client code shows, there are multiple WCF calls (three WCF calls) and the second call gave a FaultException when the client did not enter a Customer. The change inflicted was not persisted to the database and we managed to keep a consistent content of our two tables and the transaction rolled back the persistence of data data Entity Framework was about to inflict - accross multiple WCF calls. Transaction support is rather easy to add to your WCF services and at the client side there is little code that must be writted to ensure that multiple WCF calls inflict consistent change in data. Adding WCF transactions are a much more elegant way to add transaction support to your API accross multiple WCF methods than manually trying to undo WCF operations or refactor / rewrite much code to achieve what you always want with your API - to persist data supporting all four ACID principles. Note that you must use SQL Server database (I have used SqlExpress) and give access to the database I have added a SQL script for (Transactionsdemo.sql) to the app pool user so that the database TransactionsDemo can be accessed and updated (grant db_datareader and db_datawriter access in SQL Management Studio). Hope you found this sample interesting.