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

• t3chb0t: 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
• Bruno Costa: Answers containing only code are discouraged. Please explain why your approach is better than OP.
• t3chb0t: @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.
• Dmitry Nogin: 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.
• t3chb0t: 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 ;-]