3
\$\begingroup\$

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);
\$\endgroup\$
2
\$\begingroup\$

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 :)

\$\endgroup\$
  • \$\begingroup\$ This is amazing. Thank you. If it doesn't work I'll make it work ;-) I know all this patterns and constructs but to use them like this, just wow... well I need to think harder next time ;-D \$\endgroup\$ – t3chb0t Jun 24 '16 at 20:03
  • \$\begingroup\$ Answers containing only code are discouraged. Please explain why your approach is better than OP. \$\endgroup\$ – Bruno Costa Jun 25 '16 at 7:09
  • \$\begingroup\$ @BrunoCosta oh it's waaaay better :-) and I don't mind Dmitry writing any comments. I perfectly understand every line. Sorry Dmitry that your great answer doesn't get more appretiation. \$\endgroup\$ – t3chb0t Jun 25 '16 at 11:49
  • 1
    \$\begingroup\$ Thank you @t3chb0t. Well, SOLID, immutable and thread safe solution is definitely adding some overhead using an inefficient language like C#, so it is not suitable for every project culture, but it greatly improves maintainability. Let’s say it this way: when it is time to start factoring out infrastructure code, then it is time to become SOLID, so here it is :) P.S. I feel pain for EF7 design… It will require the same wrapping. \$\endgroup\$ – Dmitry Nogin Jun 25 '16 at 19:21
  • \$\begingroup\$ Today I added a transaction extension and tested it live and works like a charm ;-) Luckily there are rarely situations where you need to sacrifice design in favor of performance but then you at least have a good excuse ;-] \$\endgroup\$ – t3chb0t Jun 30 '16 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.