title: Oracle CHAR datatypes dont work with Entity Framework
body: <p>I have an issue returning data from on Oracle database using a WHERE clause which targets a CHAR column.</p>
  
  <p>I have provided the steps below which should allow the issue to be recreated:</p>
  
  <p><strong>Database Setup</strong></p>
  
  <p>Run the following SQL to create the database table and insert mock data:</p>
  
  <p><code>CREATE TABLE ORDERS (ORDER_NUMBER CHAR(10 BYTE));</code><BR>
  <code>INSERT INTO ORDERS VALUES ('123456');</code><BR>
  <code>SELECT REPLACE(ORDER_NUMBER, ' ', '#') as ORDER_NUMBER from ORDERS</code></p>
  
  <p>ORDER_NUMBER<br> 123456####</p>
  
  <p>As you can see, the value stored in the table is padded out with spaces to 10 characters (replaced by '#' to make them apparent).</p>
  
  <p><strong>Entity Framework Setup</strong></p>
  
  <pre><code>public class Order
  {
      public string OrderNumber { get; set; }
  }
  
  public class OrderMap : EntityTypeConfiguration&lt;Order&gt;
  {
      public OrderMap()
      {
          // Primary Key
          HasKey(t =&gt; t.OrderNumber);
  
          Property(t =&gt; t.OrderNumber)
              .HasColumnName("ORDER_NUMBER")
              .IsRequired()
              .IsFixedLength()
              .HasMaxLength(10)
              .HasColumnType("CHAR")
              .IsUnicode(false);
      }
  }
  
  public class OrdersContext()
  {
      static OrdersContext()
      {
          Database.SetInitializer&lt;OrderContext&gt;(null);
      }
  
      public DbSet&lt;Order&gt; Orders { get; set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)  
      {
          modelBuilder.Configurations.Add(new OrderMap());
      }
  }
  </code></pre>
  
  <p><strong>The Problem</strong></p>
  
  <pre><code>class Program
  {
      static void Main(string[] args)
      {           
          using (var context = new OrdersContext())
          {
              var order1 = context.Orders
                                  .FirstOrDefault(o =&gt; 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 =&gt; o.WorksOrder == orderFilter);
  
              // This fails. Using a variable to specify the filter does not work. No record is found.
              Debug.WriteLine("OrderNumber:" + order2.WorksOrder);
          }
      }
  }
  </code></pre>
  
  <p>So, the issue is that when I want to use a variable (<code>orderFilter</code>), the statement does not return a record.  I can make this work by padding <code>orderFilter</code> as follows, but I don't think I should have to do this:</p>
  
  <pre><code>var orderFilter = "123456".PadRight(10);
  var order2 = context.Orders.FirstOrDefault(o =&gt; o.WorksOrder == orderFilter);
  
  // This now works
  Debug.WriteLine("OrderNumber:" + order2.WorksOrder);
  </code></pre>
  
  <p>It appears as through the SQL generated when using a bind variable does not set the correct datatype on the bind variable.  If I enable tracing, we see the following SQL:</p>
  
  <blockquote>
    <p>Opened connection at 22/04/2015 12:15:12 +01:00</p>
    
    <p>SELECT  "Extent1"."ORDER_NUMBER" AS "ORDER_NUMBER",  FROM "ORDERS"
    "Extent1" WHERE ("Extent1"."ORDER_NUMBER" = :p__linq__0) AND (ROWNUM
    &lt;= (1) )</p>
    
    <p>-- p__linq__0: '123456    ' (Type = Object)</p>
    
    <p>-- Executing at 22/04/2015 12:15:12 +01:00
    -- Completed in 13 ms with result: OracleDataReader</p>
  </blockquote>
  
  <p>I have also tried using <code>DbFunctions.AsNonUnicode(orderFilter)</code> but this seems to have little effect.</p>
  
  <p>Can anyone explain what is going on here?  How do I filter on CHAR datatypes without padding the filter or trimming the column data?
  <br><br>
  <strong>NOTE</strong><br>
  I cannot change the data type of the column in the database.</p>
  
  <p>This issue is logged in several places but with no definitive answer:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/18578545/oracle-selecting-char-datatype">Oracle selecting CHAR datatype</a></li>
  <li><a href="https://stackoverflow.com/questions/28244561/odp-net-ef6-char-datatype-in-where-clause">ODP.NET / EF6 - CHAR datatype in WHERE clause</a></li>
  <li><a href="https://community.oracle.com/thread/3654585" rel="nofollow noreferrer">https://community.oracle.com/thread/3654585</a></li>
  </ul>
  
  <p>Version Numbers of all the things:</p>
  
  <ul>
  <li>Entity Framework 6.1.3</li>
  <li>.Net Framework 4.5.1</li>
  <li>Oracle.ManagedDataAccess 4.121.2.0 ODAC 12c Release 3</li>
  <li>Oracle.ManagedDataAccess.EntityFramework 6.121.2.0</li>
  <li>Oracle database is 11.2.0.3.0</li>
  </ul>