为nolock选择query，而不跟踪更改

Question

我经常需要切换连接字符串和数据库(dev/prod)，并且需要能够使用NOLOCK标志执行查询。

为此目的，我创建了一些实用程序，这些实用程序应该会使这一点变得更容易一些。

主要的类是QueryService，它封装了每个查询/数据库必须重复的enitre设置过程。我使用的大多数数据库都是只读的，因此我不需要更改跟踪，因此需要允许禁用它的属性。我用的是模型-首先对所有的人。

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);
        }
    }
}

它得到另外两个实用程序的支持。

DbContextFactory -它的任务是创建必须有接受连接字符串名称的构造函数的上下文。

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

以及提供nolock选项的DbContext的扩展。

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;
        }
    }
}

通常，我通过为特定模型的查询创建一个静态类来使用这个方法：

// 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);
    }
}

我还为在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);
    }
}

在那里，我只需调用Execute方法并快速更改连接字符串或环境：

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

Answer 1

好的，让我们尝试以一种可靠的方式来处理它(一切都是不可变的，线程是安全的)：

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();
}

在我们使用的地方：

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

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

具有扩展类：

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);
}

和：

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; }
}

和：

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; }
}

和：

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; }
}

和：

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; }
}

我希望所有这些东西都能用