在 Oracle 数据库情况下高效插入数据

引言

关于“如何在 Entity Framework 中提高插入新实体时的性能？”有很多文章和帖子。通常，答案中会包含一些建议，例如：调整 Configuration.AutoDetectChangesEnabled，将数据分成几部分，并为每个部分使用单独的 Context 并将其处理掉，以及许多其他方法。例如，在 MS SQL 的情况下，我使用了一个非常出色的库 BulkInsert：https://efbulkinsert.codeplex.com/，但如果我们使用 Oracle 数据库该怎么办？我将在本文中尝试回答这个问题。

需要安装什么

幸运的是，有一个项目是从上述提到的 BulkInsert 库分支出来的：https://github.com/Thorium/EntityFramework.BulkInsert，借助它，我们将解决主要问题——在 Oracle 数据库的情况下实现 BulkInsert 方法以提高插入性能。

我将解释每个包的原因

1. EntityFramework.MappingAPI 用于 BulkInsert 的内部功能。
2. Simple.Data.Oracle 和依赖的 Simple.Data。包包含 BulkInsert 程序集将使用的 Oracle 提供程序。
3. Oracle.ManagedDataAccess。包的必要性将在稍后描述。
4. EntityFramework 是核心包，确实是必需的。

解决方案

上下文

现在，让我们创建我们的 Context

internal class OracleContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    
    //other stuff...
}

internal class OracleContextBulkInsert : OracleContext 
{
    public OracleContextInsert()
    {
        //this extension comes from BulkInsert library!
        EntityFrameworkContextExtensions.RegisterDevartBulkInsertProvider(this);
    }
}

但有一个限制：OracleContextBulkInsert 方法仅适用于执行 BulkInsert 操作。如果我们尝试执行常见的 EF 操作，例如检索某些数据，将会抛出异常。这就是前面提到的 Oracle.ManagedDataAccess. 包出现的原因。实际上，我们将使用两个 Context：OracleContext 和 OracleContextBulkInsert。它们将分别使用来自 Oracle.ManagedDataAccess。包和 Simple.Data 包的提供程序。您可以将其视为类似于 CQRS 方法的东西：OracleContext 用于常见操作，OracleContextBulkInsert 用于高效插入。

存储库

我认为实现存储库模式来包装和隐藏我们的两个 Context 是个好主意，因为谁想了解它们和后面的内部逻辑呢？这就是为什么 Context 类具有 internal 访问修饰符的原因。

public interface IOracleRepository
{
    void Insert<TContext>(IEnumerable<TContext> entities) where TContext : class;

    List<TDto> GetList<TContext, TDto>(Expression<Func<TContext, bool>> predicate, 
               Expression<Func<TContext, TDto>> selector) where TContext : class;
    //other common methods...
}

public class OracleRepository : IOracleRepository
{        
    private OracleContext CreateContext(bool isSelect = true)
    {
        return isSelect ? new OracleContext() : new OracleContextBulkInsert();
    }

    private DbSet<T> Table<T>(OracleContextBase context) where T : class
    {
        return context.Set<T>();
    }

    public List<TDto> GetList<TContext, TDto>(Expression<Func<TContext, bool>> predicate, 
                      Expression<Func<TContext, TDto>> selector) where TContext : class
    {
        using (var context = CreateContext())
        {
            return Table<TContext>(context).Where(predicate).Select(selector).ToList();
        }
    }

    public void Insert<TContext>(IEnumerable<TContext> entities) where TContext : class
    {
        using (var context = CreateContext(false))
        {
            context.BulkInsert(entities);
        }
    }
}

如您所见，这是存储库模式的相当标准的实现，除了一个重要事项——CreateContext 方法的结果取决于它在哪个方法中被调用。

用法

正如我已经说过的，我们不会直接使用我们的 Context，而是通过存储库，而不必担心内部实现的逻辑。这是一个简单的例子

var repo = new OracleRepository();
var sales = repo.GetList<Sale, SalesDto>(x => x.Amount > 100,
              x => new SalesDto { Id = x.Id, Amount = x.Amount});

//BulkInsert behind the scene
repo.Insert(new List<Sales> { 
       new Sale{ Amount = 200 }, 
       new Sale{ Amount = 300 }
});

App.config

<configuration>
  <!-- other stuff -->
  <appSettings>    
    <add key="InnerDbProviderName" value="Devart.Data.Oracle" />    
  </appSettings>
  <connectionStrings>    
    <add name="OracleContextBulkInsert" connectionString="your connection string" 
                                        providerName="Devart.Data.Oracle" />
    <add name="OracleContext" connectionString="your connection string" 
                              providerName="Oracle.ManagedDataAccess.Client"/>        
  </connectionStrings>
</configuration>

这里有两件非常重要的事情

1. 您应该指定：<add key="InnerDbProviderName" value="Devart.Data.Oracle"/> ——这是 BulkInsert 项目的一个要求。
2. 现在您有两个连接字符串，因为有两个 Context 和两个对应的提供程序，如 App.config 中所示（当然，连接字符串必须相同，我希望您不会很难指定两次）。

问题

如果您使用 Glimpse:http://getglimpse.com/ 这样的 Web 诊断平台，其与 BulkInsert 库的兼容性将存在问题（将抛出异常：BulkInsertProviderNotFoundException ）。对于 MS SQL，此问题在此处修复，但遗憾的是，对于 Oracle 数据库没有现有的解决方案，因此您应该停止使用 Glimpse 或自行修复此问题。另一方面，您可以使用 MiniProfiler (http://miniprofiler.com/) 作为替代方案，它与我们的情况完全兼容。

另外，您的 Oracle 客户端（应安装在目标 PC 上）的位数应与您的应用程序相同。例如，在 IIS 的情况下，您可以为应用程序池调整一个选项——将*允许 32 位应用程序*设置为 true，这将修复问题，如果您有 x86 Oracle 客户端但您的应用程序是 x64 位。

数组绑定解决方案（不使用 Entity Framework）

如果您不想处理 BulkInsert 或者您不使用 Entity Framework，还有另一种解决方案，可以提供足够好的结果。您可以在此处找到完整的描述。主要思想是您创建一个非常简单的查询，如下所示

insert into mytable(columnName1, columnName2, ..) values(:columnName1, :columnName2, ...)

其中 :columnName1, :columnName2 是参数，但它们不是简单的原子值，而是数组。就是这样，但您还应该将 OracleCommand 变量的 ArrayBindCount 属性设置为相应的值。基于上述链接中的代码并添加一些修改：反射和泛型方法，完整的解决方案将如下所示

private static Dictionary<Type, OracleDbType> dotNet2Oracle { get; set; } = 
    new Dictionary<Type, OracleDbType> {
        [typeof(string)] = OracleDbType.Varchar2,
        [typeof(int)] = OracleDbType.Int32,
        [typeof(DateTime)] = OracleDbType.Date,
        [typeof(decimal)] = OracleDbType.Decimal,
    };

public void BatchInsert<TContext>(IEnumerable<TContext> entities) where TContext : class
{
    if (entities.Count() == 0)
        return;            

    using (var con = new OracleConnection
          (ConfigurationManager.ConnectionStrings["OracleContextInsert"].ConnectionString))
    {
        con.Open();
        var cmd = con.CreateCommand();
        var type = entities.GetType().GetGenericArguments()[0];
        var tableName = (type.GetCustomAttributes
        (typeof(TableAttribute), false).FirstOrDefault() as TableAttribute)?.Name ?? type.Name;

        cmd.CommandText = $@"insert into {tableName} ({type.GetProperties().Select(x => x.Name)
            .Aggregate((a, b) => $"{a}, {b}")}) values 
            ({type.GetProperties().Select(x => $":{x.Name}")
            .Aggregate((a, b) => $"{a}, {b}")})";

        cmd.ArrayBindCount = entities.Count();

        var oracleTypesType = typeof(OracleDbType);
        foreach (var prop in type.GetProperties())
        {
            var param = new OracleParameter();
            param.ParameterName = prop.Name;
            var customType = (prop.GetCustomAttributes
            (typeof(ColumnAttribute), false).FirstOrDefault() as ColumnAttribute)?.TypeName;

            if (customType != null)
            {
                OracleDbType enumVal;
                if (Enum.TryParse(customType, out enumVal))
                    param.OracleDbType = enumVal;
                else
                    throw new Exception($@"Custom column's type ({customType}) has 
                       no corresponding projection at OracleDbType!");
            }                        
            else
            {
                var targetType = prop.PropertyType
                       .GetGenericArguments().FirstOrDefault() ?? prop.PropertyType;

                if (!dotNet2Oracle.ContainsKey(targetType))
                    throw new Exception($@"Corresponding Oracle type for this .Net type
                    ({targetType.Name}) not founded, 
                    add necessary pair to dotNet2Oracle dictionary!");
                param.OracleDbType = dotNet2Oracle[targetType];
            }                         
            
            param.Value = entities.Select(x => type.GetProperty(prop.Name).GetValue(x)).ToArray();
            cmd.Parameters.Add(param);
        }

        cmd.ExecuteNonQuery();             
   }
}

默认情况下，表名与 TContext 类型的名称相同，要更改它，只需添加 TableAttribute，指定所需的 Name 属性值，列名就是属性名。dotNet2Oracle 字典包含 .NET 类型和 Oracle 类型之间的映射，您可以简单地向其中添加其他必需的值。此外，还可以通过 ColumnAttribute 及其 TypeName 属性直接指定 Oracle 类型。对于此解决方案，您只需要从 BulkInsert 列表中安装第 3 项。性能比较——此方法比 BulkInsert 差约 1.5 倍，但没有上一节中描述的问题。

结论

在本文中，我向您展示了如何使用库 https://github.com/Thorium/EntityFramework.BulkInsert 在 Oracle 数据库中实现 BulkInsert 方法。有人可以在代码优先、模型优先或数据库优先的方法中使用此解决方案，当然，在具体变体的情况下可能需要进行一些更改和附加内容，但核心方法将保持不变。此外，您还可以使用 数组绑定 方法（如果您不使用 Entity Framework），这是一个更简单但效率较低的解决方案。