entity framework number类型 oracle,c# – Oracle CHAR数据类型不适用于Entity Framework

我有一个问题是使用WHERE子句从Oracle数据库返回数据,该子句以CHAR列为目标.

我已经提供了以下步骤,这些步骤应该允许重新创建问题：

数据库设置

运行以下SQL以创建数据库表并插入模拟数据：

创建表订单(ORDER_NUMBER CHAR(10 BYTE));

插入订单价值(‘123456’);

从ORDERS中选择REPLACE(ORDER_NUMBER,”,’#’)作为ORDER_NUMBER

ORDER_NUMBER 123456 ####

正如您所看到的,存储在表中的值将填充为10个字符的空格(替换为“#”以使其显而易见).

实体框架设置

public class Order

{

public string OrderNumber { get; set; }

}

public class OrderMap : EntityTypeConfiguration

{

public OrderMap()

{

// Primary Key

HasKey(t => t.OrderNumber);

Property(t => t.OrderNumber)

.HasColumnName("ORDER_NUMBER")

.IsRequired()

.IsFixedLength()

.HasMaxLength(10)

.HasColumnType("CHAR")

.IsUnicode(false);

}

}

public class OrdersContext()

{

static OrdersContext()

{

Database.SetInitializer(null);

}

public DbSet Orders { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Configurations.Add(new OrderMap());

}

}

问题

class Program

{

static void Main(string[] args)

{

using (var context = new OrdersContext())

{

var order1 = context.Orders

.FirstOrDefault(o => o.OrderNumber == "123456");

// This works, the record is found in the database and I did not have to pad the filter sting to 10 characters

Debug.WriteLine("OrderNumber:" + order1.WorksOrder);

var orderFilter = "123456";

var order2 = context.Orders

.FirstOrDefault(o => o.WorksOrder == orderFilter);

// This fails. Using a variable to specify the filter does not work. No record is found.

Debug.WriteLine("OrderNumber:" + order2.WorksOrder);

}

}

}

所以,问题是当我想使用变量(orderFilter)时,该语句不会返回记录.我可以通过填充orderFilter来完成这项工作,如下所示,但我不认为我应该这样做：

var orderFilter = "123456".PadRight(10);

var order2 = context.Orders.FirstOrDefault(o => o.WorksOrder == orderFilter);

// This now works

Debug.WriteLine("OrderNumber:" + order2.WorksOrder);

通过使用绑定变量生成的SQL看起来不会在绑定变量上设置正确的数据类型.如果我启用跟踪,我们会看到以下SQL：

Opened connection at 22/04/2015 12:15:12 +01:00

SELECT “Extent1″.”ORDER_NUMBER” AS “ORDER_NUMBER”, FROM “ORDERS”

“Extent1” WHERE (“Extent1″.”ORDER_NUMBER” = :p__linq__0) AND (ROWNUM

<= (1) )

— p__linq__0: ‘123456 ‘ (Type = Object)

— Executing at 22/04/2015 12:15:12 +01:00

— Completed in 13 ms with result: OracleDataReader

我也尝试过使用DbFunctions.AsNonUnicode(orderFilter),但这看起来效果不大.

谁能解释一下这里发生了什么？如何在不填充过滤器或修剪列数据的情况下过滤CHAR数据类型？

注意

我无法更改数据库中列的数据类型.

此问题记录在几个地方,但没有明确的答案：

版本所有东西的数字：

>实体框架6.1.3

> .Net Framework 4.5.1

> Oracle.ManagedDataAccess 4.121.2.0 ODAC 12c版本3

> Oracle.ManagedDataAccess.EntityFramework 6.121.2.0

> Oracle数据库是11.2.0.3.0