Select query helper for nolock and no change tracking

Question

I often need to switch connection strings and databases (dev/prod) and I need to be able to execute queries with the NOLOCK flag.

For this purpose I create a few utilities that should make this a little bit easier.

---

The main class is the QueryService that encapsulates the enitre setup process that I had to repeat for each query/database. Most databases that I use are readonly so I do not need hte change tracking and thus the property allowing to disable it. I use model-first for all of them.

public abstract class QueryService<TDbContext> where TDbContext : DbContext
{
    protected QueryService(string connectionStringName, string environmentName = null)
    {
        if (string.IsNullOrEmpty(connectionStringName)) { throw new ArgumentNullException("connectionStringName"); }
        if (string.IsNullOrEmpty(environmentName)) { throw new ArgumentNullException("environmentName"); }

        ConnectionStringName = connectionStringName;
        EnvironmentName = environmentName;
        AutoDetectChangesEnabled = true;
    }
    public Action<string> Log { get; set; }

    public string ConnectionStringName { get; private set; }

    public string EnvironmentName { get; private set; }

    public bool AutoDetectChangesEnabled { get; set; }

    public TResult Execute<TResult>(Func<TDbContext, TResult> query, bool nolock = true)
    {
        if (query == null) { throw new ArgumentNullException("query"); }

        var connectionStringFullName = ConnectionStringName + (string.IsNullOrEmpty(EnvironmentName) ? string.Empty : "." + EnvironmentName);

        using (var context = DbContextFactory.Create<TDbContext>(connectionStringFullName))
        {
            context.Configuration.AutoDetectChangesEnabled = AutoDetectChangesEnabled;
            context.Database.Log = Log;
            return nolock ? context.AsNolock(query) : query(context);
        }
    }
}

It's supported by two other utilities.

a DbContextFactory - its job is to create the context that must have a constructor accepting a connection string name.

public class DbContextFactory
{
    public static TDbContext Create<TDbContext>(string connectionStringName) 
        where TDbContext : DbContext
    {
        var dbContext = (TDbContext)Activator.CreateInstance(
            typeof(TDbContext), connectionStringName);
        return dbContext;
    }
}

and an extension to for the DbContext that provides the nolock option

public static class DbContextExtensions
{
    public static T AsNolock<TDbContext, T>(this TDbContext context, Func<TContext, T> query) 
        where TDbContext : DbContext
    {
        using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions 
        { 
            IsolationLevel = IsolationLevel.ReadUncommitted 
        }))
        {
            var result = query(context);
            scope.Complete();
            return result;
        }
    }
}

---

Usually I use this by creating a static class for queries for the particular model:

// theoretical foo-context for a foo-model
public class FooContext : DbContext
{
    public virtual ICollection<string> Strings { get; set; }
}

// query service for the foo-model
public class FooQueryService : QueryService<FooContext>
{
    public FooQueryService(string environmentName) : base("Foo", environmentName) { }
}

// queries for the foo-model
public static class FooQueries
{
    // in the real app the "bar" is not a magic-string but a setting
    private static readonly FooQueryService FooQueryService = 
        new FooQueryService("bar") 
        {
            AutoDetectChangesEnabled = false 
        };

    public static List<string> GetStrings()
    {
        return FooQueryService.Execute(ctx => ctx.Strings.ToList(), nolock: true);
    }
}

---

I also created a static helper for when I test some queries in LINQPad:

internal class DynamicQueryService<TDbContext> : QueryService<TDbContext> 
    where TDbContext : DbContext
{
    public DynamicQueryService(string connectionStringName, string environmentName)
        : base(connectionStringName, environmentName) { }
}

public static class SelectQuery
{
    public static TResult Execute<TDbContext, TResult>(
        string connectionStringName,
        string environmentName,
        Func<TDbContext, TResult> query,
        bool nolock = true,
        bool autoDetectChangesEnabled = true,
        Action<string> log = null) where TDbContext : DbContext
    {
        return new DynamicQueryService<TDbContext>(connectionStringName, environmentName)
        {
            AutoDetectChangesEnabled = autoDetectChangesEnabled,
            Log = log
        }.Execute(query, nolock);
    }
}

there I can just call the Execute method and quickly change the connection string or environment:

var result = SelectQuery.Execute<FooContext>(
    "foo",
    "dev",
    ctx => ctx.Strings.ToList(),
    nolock: true,
    autoDetectChangesEnabled: false,
    log: null);

Answer 1

OK, let’s try to approach it in a SOLID way (everything is immutable and thread safe):

public class FooContext : DbContext
{
    public virtual ICollection<string> Strings { get; set; }
}

public static class FooQueries
{
    static IConnection Connection => new Connection("connectionName", "environment")
        .Log(Console.WriteLine)
        .NoTracking();

    public static IList<string> GetStrings() => Connection
        .Query((FooContext c) => c.Strings.ToList())
        .WithNoLock()
        .Execute();
}

Where we use:

public interface IConnection
{
    T ToContext<T>() where T : DbContext;
}

public interface IQuery<TResult>
{
    TResult Execute();
}

With extension class:

public static class Queries
{
    public static IConnection Log(this IConnection connection, Action<string> log) =>
        new ConfiguringConnection(connection, c => c.Database.Log = log);

    public static IConnection NoTracking(this IConnection connection) =>
        new ConfiguringConnection(connection, c => c.Configuration.AutoDetectChangesEnabled = false);

    public static IQuery<TResult> Query<TContext, TResult>(this IConnection connection, Func<TContext, TResult> selector)
        where TContext : DbContext =>
        new Query<TContext, TResult>(connection, selector);

    public static IQuery<TResult> WithNoLock<TResult>(this IQuery<TResult> query) =>
        new NoLockQuery<TResult>(query);
}

And:

public class Connection : IConnection
{
    public Connection(string name, string environment = null)
    {
        Name = name;
        Environment = environment;
    }

    public T ToContext<T>()
        where T : DbContext =>
        (T)Activator.CreateInstance(typeof(T), ToString());

    public override string ToString() =>
        string.IsNullOrEmpty(Environment) ? Name : Name + "." + Environment;

    string Name { get; }
    string Environment { get; }
}

And:

class ConfiguringConnection : IConnection
{
    public ConfiguringConnection(IConnection parent, Action<DbContext> setup)
    {
        Parent = parent;
        Setup = setup;
    }

    public T ToContext<T>() where T : DbContext
    {
        var context = Parent.ToContext<T>();
        Setup(context);
        return context;
    }

    protected IConnection Parent { get; }
    protected Action<DbContext> Setup { get; }
}

And:

class NoLockQuery<TResult> : IQuery<TResult>
{
    public NoLockQuery(IQuery<TResult> parent)
    {
        Parent = parent;
    }

    public TResult Execute()
    {
        using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadUncommitted
        }))
        {
            var result = Parent.Execute();
            scope.Complete();
            return result;
        }
    }

    IQuery<TResult> Parent { get; }
}

And:

class Query<TContext, TResult> : IQuery<TResult> 
    where TContext : DbContext
{
    public Query(IConnection connection, Func<TContext, TResult> selector)
    {
        Connection = connection;
        Selector = selector;
    }

    public TResult Execute() => Selector(Connection.ToContext<TContext>());
    IConnection Connection { get; }
    Func<TContext, TResult> Selector { get; }
}

P.S. I hope all this stuff works :)