EntityFramework 4.1 Code First Oracle Inserting PL/SQL Code

Question

My project has two EF 4.1 contexts, one is model-first and the other is code-first. Both contexts extend DbContext and are connecting to an Oracle database.

When executing an update to a large table (5M records), the model-first context produces SQL as one would expect and it runs quickly (milliseconds):

update <schema.table_name> set field = 'value' where id = 1234

When executing an update to a large table (4.7M records), the code-first context produces some PL/SQL that is odd:

declare
"UWI" nvarchar2(
                128)
;
begin
update
                "SCHEMA"."TABLE"
set "FIELD" = 'VALUE' /* :p0 */,

where ("UWI" = '2224434' /* :p37 */)

returning
"UWI" into
"UWI";
open '' /* :p38 */ for select

"UWI" as "UWI" 
from dual;
end;

This update statement takes 3 seconds to complete.

Here is the code-first EntityTypeConfiguration for the code-first context:

public WellEntityConfiguration()
{
    this.ToTable("TABLE", "SCHEMA");

    this.HasKey(entity => entity.Uwi);
    this.Property(entity => entity.Uwi).HasColumnName("UWI");

    ... //lots of properties being set
}

Is there a configuration I can set to force EF to generate the simple update statement instead of the crazy PL/SQL?

score 1 · Accepted Answer · answered Apr 09 '13 at 15:09

The answer to this is two-fold.

Removing the PL/SQL from the Generated SQL

The column name in the database is in all-caps ("UWI") while the property on the class is camel-case ("Uwi"). I changed the property name to all-caps and EF removes the PL/SQL code and produces just the SQL:

UPDATE "SCHEMA"."TABLE"
SET "FIELD" = "VALUE"
WHERE ("UWI" = '2224434')

However, this does not improve performance.

Why the Update Was Slow

After working with the DBA to trace the query, we found that EF was binding the Uwi's value to a hex string instead of "2224434." This was causing Oracle to do a full table scan and impacting performance. I needed to specify the column type on the Uwi property, like so:

this.Property(entity => entity.Uwi).HasColumnName("UWI").HasColumnType("VARCHAR2");

The HasColumnType was the magic bullet and my update statement is returning in 80 milliseconds.

EntityFramework 4.1 Code First Oracle Inserting PL/SQL Code

1 Answers1