Oracle char datatypes do not work with Entity Framework

Question

I have a problem returning data from an Oracle database using the WHERE clause that is for the CHAR column.

I presented the steps below that should allow you to recreate the problem:

Database setup

Run the following SQL to create the database table and insert the data layout:

CREATE TABLE ORDERS (ORDER_NUMBER CHAR(10 BYTE));
INSERT INTO ORDERS VALUES ('123456');
SELECT REPLACE(ORDER_NUMBER, ' ', '#') as ORDER_NUMBER from ORDERS

ORDER_NUMBER
123456 ####

As you can see, the value stored in the table is filled with spaces up to 10 characters (replaced with "#" to make them visible).

Entity Platform Configuration

public class Order
{
    public string OrderNumber { get; set; }
}

public class OrderMap : EntityTypeConfiguration<Order>
{
    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<OrderContext>(null);
    }

    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)  
    {
        modelBuilder.Configurations.Add(new OrderMap());
    }
}

Problem

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:

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)

- 22/04/2015 12:15:12 +01: 00 - 13 : OracleDataReader

DbFunctions.AsNonUnicode(orderFilter), , , .

- , ? CHAR ?


.

, :

• Oracle, CHAR
• ODP.NET/EF6 - CHAR WHERE
• https://community.oracle.com/thread/3654585

:

• Entity Framework 6.1.3
• .Net Framework 4.5.1
• Oracle.ManagedDataAccess 4.121.2.0 ODAC 12c Release 3
• Oracle.ManagedDataAccess.EntityFramework 6.121.2.0
• Oracle - 11.2.0.3.0

Answer 1

, . , CHAR ( , , ) .

Tom Kyte CHAR "Expert Database Database Architecture" . 2-9- 499-502.

, - .

( SQLPlus 12.1.0.2 ( 12c)

CHAR VARCHAR2:

CREATE TABLE ORDERS (ORDER_NUMBER_CHAR       CHAR(10 BYTE),
                     ORDER_NUMBER_VARCHAR2   VARCHAR2(10 BYTE));

INSERT INTO ORDERS(ORDER_NUMBER_CHAR, 
                   ORDER_NUMBER_VARCHAR2)
VALUES ('123456',
        '123456');

, VARCHAR2 WHERE, , , .

SELECT *
  FROM orders
 WHERE order_number_varchar2 = '123456';

WHERE CHAR. , , CHAR (6) CHAR (10)

SELECT *
  FROM orders
 WHERE order_number_char = '123456';

, , ,

SELECT *
  FROM orders
 WHERE order_number_char = order_number_varchar2;

, VARCHAR2 , ,

variable vc2 VARCHAR2(10 BYTE);
exec :vc2 := '123456'; 

SELECT *
  FROM orders
 WHERE order_number_char = :vc2;

CHAR, , , .

variable the_char CHAR(10 BYTE);
exec :the_char := '123456'; 

SELECT *
  FROM orders
 WHERE order_number_char = :the_char;

, , CHAR - , .. 20, PadRight.

, , , CHAR .

- , , , - CHAR . , . VARCHAR2 (1) CHAR (1) . CHAR , , , "", CHAR (1).

Answer 2

Trim() ....