dotConnect for Oracle 8.2 + EF6.0.2 Oracle 12C Identity column problem

[John Liu]

Hi
I'm using dotConnect for Oracle 8.2.90.0 with EntityFramework 6.0.2. It works great for Oracle 11g. I recently upgraded to Oracle 12c. The provider generates Identity keys for a composite key columns when "DatabaseGeneratedOption.None" was specified in the mapping.
public ContractPermitMap()
{
// Primary Key
this.HasKey(t => new { ContractId = t.ContractId, PermitSid = t.PermitSid });

this.Property(t => t.ContractId)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

this.Property(t => t.PermitSid)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

// Table & Column Mappings
this.ToTable("CONTRACTPERMIT", "AZ");
this.Property(t => t.ContractId).HasColumnName("CONTRACTID");
this.Property(t => t.PermitSid).HasColumnName("PERMITSID");
}

The generated script for Oracle 12c
CREATE TABLE AZ.CONTRACTPERMIT (
CONTRACTID NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL,
PERMITSID NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL,
PRIMARY KEY (CONTRACTID, PERMITSID)
)
Oracle Error
ORA-30669: table can have only one identity column

The script generated for Oracle 11g
CREATE TABLE AZ.CONTRACTPERMIT (
CONTRACTID NUMBER(10) NOT NULL,
PERMITSID NUMBER(10) NOT NULL,
PRIMARY KEY (CONTRACTID, PERMITSID)
)
This is what I expected and it works great.

Thanks
JL

[MariiaI]

We couldn't reproduce this issue in our environment. Please send us a small test project, with which this issue is reproducible, so that we are able to investigate it in more details.

[John Liu]

I looked into this problem a little bit more in detail. It's not a problem for a composite primary key. It generates a primary key with GENERATED ALWAYS AS IDENTITY for every one of my tables even when I configure the mapping as "DatabaseGeneratedOption.None"
this.Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
Basically, I don't want to define identity column for my primary key to be backward compatible. How do I turn it off? I don't understand your Documentation
----------------------------------------
The behaviour is changed: Code-First/Code-First Migrations generate columns with the identity clause 'GENERATED AS IDENTITY' for entity key properties with 'Store Generated Pattern=Identity' (only for Oracle 12c) - See more at: http://www.devart.com/dotconnect/oracle ... XUT7O.dpuf
----------------------------------------
please help!

Thanks
JL

[MariiaI]

We still couldn't reproduce this issue in our environment. Please send us a small test project, with which this issue is reproducible, so that we are able to investigate it in more details.
As for the dotConnect for Oracle revision history:

The behaviour is changed: Code-First/Code-First Migrations generate columns with the identity clause 'GENERATED AS IDENTITY' for entity key properties with 'Store Generated Pattern=Identity' (only for Oracle 12c)

this related to the fact that starting from the Oracle 12c the possibility to use identity instead of sequences and triggers is available, and we've added support for this feature for entity key properties with "Store Generated Pattern=Identity" or
".HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)".

[John Liu]

OK. I created a sample project and will send to you.
The model
public class Action
{
public Action()
{
}
public int ActionId { get; set; }
public string ActionType { get; set; }
public DateTime? DateTimeProcessed { get; set; }
public string Status { get; set; }
}
The Mapping
public class ActionMap : EntityTypeConfiguration<Action>
{
public ActionMap()
{
// Primary Key
this.HasKey(t => t.ActionId);

// Properties
this.Property(t => t.ActionId)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

this.Property(t => t.ActionType)
.IsRequired()
.HasMaxLength(20);

this.Property(t => t.Status)
.HasMaxLength(50);

// Table & Column Mappings
this.ToTable("ACTION", "AZ");
this.Property(t => t.ActionId).HasColumnName("ACTIONID");
this.Property(t => t.ActionType).HasColumnName("ACTIONTYPE");
this.Property(t => t.DateTimeProcessed).HasColumnName("DATETIMEPROCESSED");
this.Property(t => t.Status).HasColumnName("STATUS");
}
}
The DbContext
public class MyDbContext : DbContext
{
public MyDbContext()
{
InitCwDbContext();
}

public MyDbContext(string connectionStringName)
: base(connectionStringName)
{
InitCwDbContext();
}

private void InitCwDbContext()
{
Database.SetInitializer<MyDbContext>(null);
this.Configuration.ProxyCreationEnabled = false;
this.Configuration.AutoDetectChangesEnabled = false;
this.Configuration.ValidateOnSaveEnabled = false;

//var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
//config.CodeFirstOptions.ColumnTypeCasingConventionCompatibility = true;
//config.CodeFirstOptions.TruncateLongDefaultNames = true;
//config.Workarounds.IgnoreDboSchemaName = true;
//config.Workarounds.DisableQuoting = false;

}
public DbSet<Action> Actions { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new ActionMap());
}
}

The Configuration
internal sealed class Configuration : DbMigrationsConfiguration<Oracle12C.MyDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
MigrationsAssembly = Assembly.GetExecutingAssembly();
MigrationsNamespace = "Oracle12C";
SetSqlGenerator(OracleConnectionInfo.InvariantName,new OracleEntityMigrationSqlGenerator());
}

protected override void Seed(Oracle12C.MyDbContext context)
{
}
}
The Migration
public partial class V1 : DbMigration
{
public override void Up()
{
CreateTable(
"AZ.ACTION",
c => new
{
ACTIONID = c.Int(nullable: false),
ACTIONTYPE = c.String(nullable: false, maxLength: 20),
DATETIMEPROCESSED = c.DateTime(),
STATUS = c.String(maxLength: 50),
})
.PrimaryKey(t => t.ACTIONID);

}

public override void Down()
{
DropTable("AZ.ACTION");
}
}
The generated sql script
-- Script was generated by Devart dotConnect for Oracle, Version 8.2.90
-- Product home page: http://www.devart.com/dotconnect/oracle
-- Script date 3/6/2014 9:27:46 AM

CREATE TABLE AZ.ACTION (
ACTIONID NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL,
ACTIONTYPE NVARCHAR2(20) NOT NULL,
DATETIMEPROCESSED TIMESTAMP(7) NULL,
STATUS NVARCHAR2(50) NULL,
PRIMARY KEY (ACTIONID)
)
/

'GENERATED ALWAYS AS IDENTITY' added to the primary key field is not expected. Since the mapping specified as
this.Property(t => t.ActionId) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
and the migration object didn't specify Identity : true
ACTIONID = c.Int(nullable: false),
But the generated script always put identity for PK that is not what I wanted.

thanks
JL

[John Liu]

One more the generated Downward migration script which is same to what generated for oracle 11G
-- Script was generated by Devart dotConnect for Oracle, Version 8.2.90
-- Product home page: http://www.devart.com/dotconnect/oracle
-- Script date 3/6/2014 10:18:54 AM

DROP TABLE AZTECA.ACTION
/
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE AZTECA.ACTION_SEQ';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE <> -2289 THEN
RAISE;
END IF;
END;
/

I don't understand why a sequence was generated for oracle 11G when my option was
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None); I don't need the database to manage my primary key at all.
thanks
JL

[MariiaI]

Thank you for the sample projet. We have reproduced this issue. We will investigate it and inform you about the results as soon as possible.

[Dennis Wanke]

Please also have a look at http://forums.devart.com/viewtopic.php?f=1&t=29131. The two issues seem to be related.

[MariiaI]

The bug with generating Code-First Migrations for the properties with DatabaseGeneratedOption.None is fixed.
New build of dotConnect for Oracle 8.3.125 is available for download!
It can be downloaded from ht[url]tp://www.devart.com/dotconnect/oracle/download.html[/url] (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29243.

[John Liu]

I had everything worked great with EF6.0.2 + dotConnect 8.2.90 except for Oracle 12C. I installed EF6.1.0. Everything works great with the sql server provider. I installed dotConnect 8.3.125. It doesn't work with Oracle 11G any more. It's our main production environment. The exact same problem from my previous post comes back.
http://forums.devart.com/viewtopic.php?f=30&t=28276
The problem is on all of my DataTime fields.

The sql server provider doesn't detect any changes in model. But your provider gives the following message
update-database -verbose
Using StartUp project 'CW.DataAccess'.
Using NuGet project 'CW.DataAccess'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: '' (DataSource: 192.168.20.86, Provider: Devart.Data.Oracle, Origin: Configuration).
No pending explicit migrations.
Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.

Can you mark your release as beta or stable release in the future just like the Entity Frameworks releases? I've been your tester over one year. I want to make sure I can use your code in production

thanks
JL

[John Liu]

I updated the sample project I sent to you to use EF6.1.0 + dotConnect 8.3.125. I'll send the project to you. It works great with Oracle 12C.
update-database -verbose
Using StartUp project 'Oracle12C'.
Using NuGet project 'Oracle12C'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: '' (DataSource: Polyphony, Provider: Devart.Data.Oracle, Origin: Configuration).
Applying explicit migrations: [201403061609521_V1].
Applying explicit migration: 201403061609521_V1.

After I change the database connection to a Sql server database. Run the same command. I got the following message.

Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.

The problem seems to be a contradiction between the sql server provider (System.Data.SqlClient) and your dotConnect for oracle (8.3.125). I use the same code set to manage both sql server and oracle databases. Steps to reproduce the problem are listed in my previous post.
http://forums.devart.com/viewtopic.php?f=30&t=28276

thanks
JL

[MariiaI]

Thank you for the report on this. We have reproduced this issue with the latest build of dotConnect for Oracle and EF 6.1.0. We will post here when the results regarding this issue are available.

[John Liu]

Has this problem been fixed in version 8.3.135?
thanks
JL

[MariiaI]

We have investigated the issue in more details. The issue is in the specific scenario - the application is developing for SQL Server and Oracle with EF Code-First Migrations approach and one version of migrations in it. Generally, Entity Framework is not oriented on this approach and such scenarios should be implemented with several versions of the migration - separately for SqlClient and dotConnect for Oracle providers - because each EF migration stores an edmx file in a packed form in its resourses with the provider-specific part (for every Entity Framework provider this part differs).
For previous versions of Entity Framework there was an opportunity to avoid this. However, there is no possibility to make any changes to avoid this issue for Entity Framework 6.1.

The possible solution is to create application in the following way:
1) the base assembly with the base DbContext class and entity classes;
2) two additional assemblies with provider-specific classes, inherited from the base DbContext class, and their Configuration classes;
3) "add-migration" is performed specially for its provider-specific project;

We will prepare a sample project with such solution and send it to you as soon as it is available.

[John Liu]

As I have mentioned before, we have been using EF6.0.2 + dotConnect for Oracle 8.2 in production already. We have spent lot of time to test it. Everything works great for Sql server databases and Oracle 11G. The only problem is to work with Oracle 12C. I'm wondering if you can provide a fix on dotConnect for oracle 8.2 to work with Oracle 12C. So you have a stable version to work with EF6.0.2, and I have a fix in my production environment. I can consider your recommendation to restructure our software to use EF6.1 + dotConnect 8.3 in the future.
thanks
JL