如何使用EntityFramework5和ODP.NET托管提供程序使用字符串关键字搜索Oracle表

Question

我的表是这样的（伪代码）：创建表DOCUMENT_LOCATOR（DOCUMENT_ID VARCHAR2（6）主键）

当使用通过NuGet下载的最新ODP.NET托管驱动程序v121.1.2时，以下实体框架命令不使用索引：

context.DOCUMENT_LOCATOR.Find("ABC123");
context.DOCUMENT_LOCATOR.Find(EntityFunctions.AsNonUnicode("ABC123"));
context.DOCUMENT_LOCATOR.FirstOrDefault(i => i.DOCUMENT_ID == "ABC123");

以下命令确实使用索引：

context.DOCUMENT_LOCATOR.FirstOrDefault(i => i.DOCUMENT_ID == EntityFunctions.AsNonUnicode("ABC123"));

不幸的是，UPDATE和DELETE不使用索引，因此它们基本上使EF/Oracle对大型表毫无用处。有人想办法解决这个问题吗？

Answer 1

经过大量搜索，我找到了一个非常好的解决方案。它将所有实体的所有字符串属性设置为IsUnicode（false）。要使用它，只需在DbContext类的OnModelCreating方法中添加一行：

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new ASSUMED_NAME_FILESMap());
        modelBuilder.Configurations.Add(new ASSUMED_NAME_FILENAME_ONLYMap());
        modelBuilder.Configurations.Add(new DOCUMENT_LOCATORMap());
        modelBuilder.Configurations.Add(new IMS_IMAGE_TABLEMap());
        modelBuilder.DisableUnicodeForAllEntityStrings(this);
    }

正如您所看到的，我将代码更改为扩展。这是我修改后的代码：

public static class DbModelBuilderExtensions
{
    /// <summary>
    /// Disables unicode for all string properties on entities in the given context.
    /// MUST BE CALLED AFTER modelBuilder.Configurations.Add(map) CALLS.
    /// </summary>
    /// <remarks>
    /// Adapted from http://www.johncoder.com/Post/EFCodeFirstDisableUnicodeforallStringProperties
    /// </remarks>
    public static void DisableUnicodeForAllEntityStrings(this DbModelBuilder builder, DbContext context)
    {
        // Get all IDbSet<> properties from the context
        var entityTypes = from property in context.GetType().GetProperties()
                          where property.PropertyType.ImplementsInterface(typeof(IDbSet<>))
                          let entityType = property.PropertyType.GetGenericArguments()[0]
                          select entityType;
        // Disable Unicode support for each table
        foreach (var entityType in entityTypes)
            DisableUnicodeForEntityStrings(builder, entityType);
    }
    private static void DisableUnicodeForEntityStrings(DbModelBuilder modelBuilder, Type entityType)
    {
        // Get all string properties with setters
        var stringProperties = from property in entityType.GetProperties()
                               where property.PropertyType == typeof(string)
                                   && property.CanWrite
                               select property;
        // Each table field must be varchar for now,
        // so take the string property & call IsUnicode(false).
        foreach (var property in stringProperties)
        {
            // Don't remove this line.
            // Lambda might not work without it.
            PropertyInfo prop = property;
            // Create the correct expression type,
            // should be Expression<Func<TModel, string>>
            var exprType = typeof(Expression<>)
                .MakeGenericType(typeof(Func<,>)
                .MakeGenericType(prop.ReflectedType, typeof(string)));
            // Find and execute the Entity() method,
            // using TModel generic parameter
            var obj = modelBuilder.GetType()
                .GetMethod("Entity")
                .MakeGenericMethod(prop.ReflectedType)
                .Invoke(modelBuilder, null);
            // Runtime Lambda expression to represent
            // something like Property(p => p.Suffix)
            ParameterExpression pe = Expression.Parameter(prop.ReflectedType, "p");
            var expression = Expression.Lambda(Expression.Property(pe, prop.Name), pe);
            // Find the Property method that takes an expression as a parameter
            // and then invoke it using the expression that was just built
            var p = obj.GetType()
                .GetMethod("Property", new[] { exprType })
                .Invoke(obj, new[] { expression });
            // If all goes well, we'll have a StringPropertyConfiguration.
            var propertyConfig = p as StringPropertyConfiguration;
            if (propertyConfig != null)
                propertyConfig.IsUnicode(false);
        }
    }
    public static bool ImplementsInterface(this Type value, Type interfaceType)
    {
        return value.GetInterface(interfaceType.FullName) != null;
    }
}