Mutiple-DB support with EF6 Code first and conditional column mapping.

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
ivoryguard
Posts: 8
Joined: Wed 13 Oct 2010 01:11

Mutiple-DB support with EF6 Code first and conditional column mapping.

Post by ivoryguard » Mon 21 Nov 2016 03:52

Hello.

I am using dotConnect for Oracle, MySQL and SQLite professional edition.

I have succeeded in supporting MSSQL, Oracle, MySQL at my web app by changing DB connection string only.

My web app was developed with EF 4.1 DB First way.

Now, I want to change EF 4.1 DB First to EF 6 code first in order to use EF6 code first auto migration feature.



I have a few questions.

1) I have implemented EF6 Code First with dotConnect for SQLite. Is it possible to support multiple DBs at the single Model of EF6 Code First by changing DB connection string only?

2) DB schema of my web app is slightly different between MSSQL and Oracle, because the length of all IDs must be equal or less than 30 in Oracle. So, I had to shorten some column names in Oracle Schema. Fortunately, I succeeded in multi-DB support by changing auto-generated model binding XML (MSL, CSDL, SSDL) of EF 4.1 DB First.

I want to change it to EF6 code first with EF code first migration feature. In order to implement it, I think that it must be possible to set conditional column name at EF6 code first or devart products, because actual DB column name mapped from a c# property is different between MSSQL and Oracle.



Is it possible to implement my requirements with devart products?
If it is possible, please let me know how I can do it.


Best regards.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Mutiple-DB support with EF6 Code first and conditional column mapping.

Post by Shalex » Tue 22 Nov 2016 14:28

ivoryguard wrote:1) I have implemented EF6 Code First with dotConnect for SQLite. Is it possible to support multiple DBs at the single Model of EF6 Code First by changing DB connection string only?
Yes, it is possible. Please refer to viewtopic.php?t=30068#p103272.
ivoryguard wrote:2) DB schema of my web app is slightly different between MSSQL and Oracle, because the length of all IDs must be equal or less than 30 in Oracle. So, I had to shorten some column names in Oracle Schema.
To solve the issue, please set TruncateLongDefaultNames to true in code or via *.config:

a) in code

Code: Select all

            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig;
            config.CodeFirstOptions.TruncateLongDefaultNames=true;
b) via *.config

Code: Select all

  <configSections>
    <section name="Devart.Data.Oracle.Entity" type="Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfigurationSection, Devart.Data.Oracle.Entity.EF6, Version=9.1.131.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </configSections> 
  <Devart.Data.Oracle.Entity xmlns="http://devart.com/schemas/Devart.Data.Oracle.Entity/1.0">
    <CodeFirstOptions TruncateLongDefaultNames="true" />
  </Devart.Data.Oracle.Entity>
The Devart products you may be interested in for this task are the following:
  • dotConnect for Oracle Professional
  • dotConnect for MySQL Professional
  • dotConnect for SQLite Professional
  • Entity Developer for Entity Framework (works with SQL Server via standard provider System.Data.SqlClient)

ivoryguard
Posts: 8
Joined: Wed 13 Oct 2010 01:11

Re: Mutiple-DB support with EF6 Code first and conditional column mapping.

Post by ivoryguard » Wed 23 Nov 2016 01:35

Thank you for the reply.

I read your answers and reference posting and sample source codes.

I do not want to use "DB Context template", because I want that my codes are simple and flexible for DB schema changes. So I will use fluent mapping with DbModelBuilder.



I have a few questions.

1) The character cases of Oracle DB Schema are same to that of MSSQL. My all Table, column and constraint names are case sensitive, because my app uses auto-generated Entities of EF DB-first EDMX for MSSQL, Oracle and MySQL.

In this case, how does dotConnect for Oracle/MySQL work for Column and Table attributes?

For examples, is [Table(“Employ”)] translated to "Employ" for Oracle and `Employ` for MySQL, or EMPLOY for Oracle and employ for MySQL, in generated SQL script?



2) I cannot use TruncateLongDefaultNames, because the column names were shortened like BlablablaConfigurations=> BlablablaCfgs.

In this case, can I override DB mapping partially for a few columns in “protected override void OnModelCreating(DbModelBuilder modelBuilder)”? I will add Column/Table attribute for remaining properties.



3) For fluent mapping, does foreign key name affect navigation properties? Some foreign key names are different between MSSQL and Oracle because of 30 character limit. It does not make error at EF DB-first because mapping information is stored to SSLD, which is generated automatically by VS2010 EDMX builder.



4) Please, let me know how to set connection string dynamically according to DB type setting. Your sample code uses app.config. I have suffered setting connection string dynamically for EF code first with dotConnect for SQLite Pro before.



5) I know that EF6 in VS2013 provides DB-to-codeFirst code generation. Can I use it for Oracle with dotConnect For Oracle Pro?


6) I have read the latest version of docConnect for Oracle support Batch update.
viewtopic.php?f=30&t=21039&p=69541&hili ... ate#p69541

I think that the main problem of EF is that I have to select first and update later instead of “UPDATE” statement of SQL.

Could you let me know “Batch update” support in detail?

In additionally, dose dotConnect support Entity Framework Extended Library -https://github.com/loresoft/EntityFramework.Extended ?


If I have confidence that the migration from DB-first to Code first of my existing app is possible, I will renew my all devart products in a few days.


Best regards.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Mutiple-DB support with EF6 Code first and conditional column mapping.

Post by Shalex » Thu 24 Nov 2016 16:15

ivoryguard wrote:1) The character cases of Oracle DB Schema are same to that of MSSQL. My all Table, column and constraint names are case sensitive, because my app uses auto-generated Entities of EF DB-first EDMX for MSSQL, Oracle and MySQL.

In this case, how does dotConnect for Oracle/MySQL work for Column and Table attributes?

For examples, is [Table(“Employ”)] translated to "Employ" for Oracle and `Employ` for MySQL, or EMPLOY for Oracle and employ for MySQL, in generated SQL script?
By default, all identifiers in all queries and commands are quoted. You can turn off quoting in dotConnect providers via config.Workarounds.DisableQuoting = true.
ivoryguard wrote:2) I cannot use TruncateLongDefaultNames, because the column names were shortened like BlablablaConfigurations=> BlablablaCfgs.

In this case, can I override DB mapping partially for a few columns in “protected override void OnModelCreating(DbModelBuilder modelBuilder)”? I will add Column/Table attribute for remaining properties.
Yes, you can.

JIC
config.CodeFirstOptions.TruncateLongDefaultNames -> When set to true, long default names of triggers, sequences, indexes, primary keys, and foreign keys generated by Code-First Migrations will be truncated to 30 symbols. When set to false, an exception will be raised for long default names. Be careful when setting the property to true, because checks for uniqueness of names are not performed. Default value is false.
ivoryguard wrote:3) For fluent mapping, does foreign key name affect navigation properties? Some foreign key names are different between MSSQL and Oracle because of 30 character limit. It does not make error at EF DB-first because mapping information is stored to SSLD, which is generated automatically by VS2010 EDMX builder.
The question is not clear. You are going to create navigation properties manually, aren't you?
ivoryguard wrote:4) Please, let me know how to set connection string dynamically according to DB type setting. Your sample code uses app.config. I have suffered setting connection string dynamically for EF code first with dotConnect for SQLite Pro before.
In your case when mapping customization in OnModelCreating depends on the DB type, please create database-specific classes-descendants from the base context class which contains the model. Refer to the sample available at http://blog.devart.com/entity-framework ... ml#Samples.
ivoryguard wrote:5) I know that EF6 in VS2013 provides DB-to-codeFirst code generation. Can I use it for Oracle with dotConnect For Oracle Pro?
We recommend using DbContext template to create a detailed complete mapping (http://blog.devart.com/entity-developer ... plate.html). It was designed for the purpose you are asking about.
ivoryguard wrote:6) I have read the latest version of docConnect for Oracle support Batch update.
viewtopic.php?f=30&t=21039&p=69541&hili ... ate#p69541

I think that the main problem of EF is that I have to select first and update later instead of “UPDATE” statement of SQL.

Could you let me know “Batch update” support in detail?
Please refer to
http://blog.devart.com/new-features-of- ... html#Batch
https://www.devart.com/dotconnect/oracl ... dates.html
https://www.devart.com/dotconnect/mysql ... dates.html
https://www.devart.com/dotconnect/sqlit ... dates.html
ivoryguard wrote:In additionally, dose dotConnect support Entity Framework Extended Library -https://github.com/loresoft/EntityFramework.Extended ?
We didn't test this third party library. Please refer to viewtopic.php?t=32567.

Post Reply