Code First - Many to Many wrong table name generation

[Feneck91]

I'm using Entity framework (6.0 Beta) + Code First with dotConnect and Oracle.
I have 2 Tables : User and Profile.
I'm french, the database is written in french too (strange but it is like that), it is important to explain my problem.
Each user (Utilisateur in french) can reference more than one profile.
All C# classes for EF begin with Entity but the table begin with DM (Data Manager).

Code: Select all

    [Table("DMUtilisateur", Schema = "GDD")]
    public class EntityUtilisateur
    {
        public EntityUtilisateur()
        {   // Create relationship many to many
            ListProfiles = new HashSet<EntityProfile>();
        }

        [Key]
        public Int64                    UtilisateurID               { get; set; }

        [Required][MaxLength(64)]
        public String                   Name                        { get; set; }

        [Required][MaxLength(32)]
        public String                   Password                    { get; set; }

        /// <summary>
        /// Make relationship many to many.
        /// </summary>
        public ICollection<EntityProfile> ListProfiles              { get; set; }
    }

And :

    /// <summary>
    /// Table des profiles. Contient la liste des profiles
    /// </summary>
    [Table("DMProfile", Schema = "GDD")]
    public class EntityProfile
    {
        /// <summary>
        /// Constructeur.
        /// </summary>
        public EntityProfile()
        {   // Création des la relation many to many
            ListUtilisateurs = new HashSet<EntityUtilisateur>();
        }

        /// <summary>
        /// Clef primaire de la table.
        /// </summary>
        /// <value>Identifiant unique de la table</value>
        [Key]
        public Int64                    ProfileID               { get; set; }

        /// <summary>
        /// Clef primaire de la table.
        /// </summary>
        /// <value>Nom du profile</value>
        [Required][MaxLength(128)]
        public String                   Nom                     { get; set; }

        /// <summary>
        /// Permet de faire une relation de type many to many entre les utilisateurs et leurs profiles.
        /// </summary>
        /// <value>Liste des utilisateurs contenant ce profile.</value>
        public ICollection<EntityUtilisateur> ListUtilisateurs  { get; set; }
    }

Code first create automatically a new Table that contains 2 foreigners keys, one on each table (PM Command = "Add-Migration Version_1 -force"). This works fine... but...

Several problems :

1. My both Tables are prefix with data annotation that indicate the real name of the table : [Table("DMUtilisateur", Schema = "GDD")] and [Table("DMProfile", Schema = "GDD")] => These prefix are not taken into account when generating the new table, it generate : dbo.EntityUtilisateurEntityProfiles instead of GDD.DMUtilisateurDMProfiles
   1. dbo is not good, both table use GDD as schema.
   2. EntityUtilisateurEntityProfiles is not good because it should generated DMUtilisateurDMProfile if it correctly use the data annotations.
2. EntityUtilisateurEntityProfiles is not good because it makes more than 30 char length and Oracle don't want it (OracleEntityProviderConfig.Instance.CodeFirstOptions.TruncateLongDefaultNames = true has no effect but it's work fine on simple Foreign keys.

I set :
In Configuration class, derived from DbMigrationsConfiguration<DataManager.DatabaseManagerContextApp>
I wrote :

Code: Select all

SetSqlGenerator(OracleConnectionInfo.InvariantName, new OracleEntityMigrationSqlGenerator());
            AutomaticMigrationsEnabled = false;
OracleEntityProviderConfig.Instance.CodeFirstOptions.TruncateLongDefaultNames = true;

In my Context, I wrote :

Code: Select all

 [DbConfigurationType(typeof(Devart.Data.Oracle.Entity.OracleEntityProviderServicesConfiguration))]
    public class DatabaseManagerContext : DbContext
...
...
...

Into the constructor :

            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;  
            //config.Workarounds.IgnoreSchemaName = true; <<-- Whould I set It to true ?
            config.Workarounds.ColumnTypeCasingConventionCompatibility = true;
            Configuration.LazyLoadingEnabled = false;

I can easylly make it work but it's just bad Workarounds !

• Rename Table 'Utilisateur' to 'User' but strange to write all in french and one in english.
• Modify the source code generated by EF by modify the name of the tables (may be some problems after ?). And I make lot of new generation from empty database (begin of developpement), I can waste a lot of time to modify the code each time I generate it.

Is it a bug of devart driver, a bug of Entity Framework ?
Do you have a good workaround ? It'll be corrected in future version ?

Thanks for your reply.

[Shalex]

My both Tables are prefix with data annotation that indicate the real name of the table : [Table("DMUtilisateur", Schema = "GDD")] and [Table("DMProfile", Schema = "GDD")] => These prefix are not taken into account when generating the new table, it generate : dbo.EntityUtilisateurEntityProfiles instead of GDD.DMUtilisateurDMProfiles

• dbo is not good, both table use GDD as schema.
• EntityUtilisateurEntityProfiles is not good because it should generated DMUtilisateurDMProfile if it correctly use the data annotations.

You have set the Table attribute only for two classes, and the corresponding two tables were generated with "DMUtilisateur" and "DMProfile" names, weren't they? But many-to-many association assumes creting the additional (third) table in the database for storing relations. Your code doesn't include entries to change a default name for this table. You can do that with fluent mapping in the OnModelCreating method:

Code: Select all

            modelBuilder.Entity<EntityUtilisateur>()
                .HasMany(p => p.EntityProfiles)
                    .WithMany(c => c.EntityUtilisateurs)
                .Map(manyToMany => manyToMany
                    .ToTable("Name_For_Third_Table", "GDD")
                    .MapLeftKey("UtilisateurID")
                    .MapRightKey("ProfileID"));

Code: Select all

Into the constructor :
            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance; 
            //config.Workarounds.IgnoreSchemaName = true; <<-- Whould I set It to true ?

You can set IgnoreSchemaName to True if the database objects should be created in the default schema (the value of the User ID connection string parameter).

EntityUtilisateurEntityProfiles is not good because it makes more than 30 char length and Oracle don't want it (OracleEntityProviderConfig.Instance.CodeFirstOptions.TruncateLongDefaultNames = true has no effect but it's work fine on simple Foreign keys.

When config.CodeFirstOptions.TruncateLongDefaultNames=true, long default names of triggers, sequences, indexes, primary keys, and foreign keys generated by Code-First Migrations will be truncated to 30 symbols.
This setting does not change the names of tables and columns because this would breake fluent mapping.

[Shalex]

I'm using Entity framework (6.0 Beta) + Code First

You can configure the default database schema name (to use it for database objects that do not have explicitly configured schema name) in the OnModelCreating method:

Code: Select all

modelBuilder.HasDefaultSchema("GDD");

[Feneck91]

You have set the Table attribute only for two classes, and the corresponding two tables were generated with "DMUtilisateur" and "DMProfile" names, weren't they?

This is OK.

But many-to-many association assumes creting the additional (third) table in the database for storing relations. Your code doesn't include entries to change a default name for this table. You can do that with fluent mapping in the OnModelCreating method:

I know that a third table must be created but its name doesn't take the name of the two table written with data annotation but the real class name, I prefer that the thrid table take the same name as the 2 others tables in the database.

You can do that with fluent mapping in the OnModelCreating method

For the moment, this code is generated by the Entity Framework migration.
My developpement is in progress with table that are often modified, I don't want to have to change the generated code each time I modify the database schema !

When config.CodeFirstOptions.TruncateLongDefaultNames=true, long default names of triggers, sequences, indexes, primary keys, and foreign keys generated by Code-First Migrations will be truncated to 30 symbols.
This setting does not change the names of tables and columns because this would breake fluent mapping.

This is certainly the problem !
Will see all this on Monday (not at work for the moment).

Thanks for the reply but I think it will not be solve my problem.

[Shalex]

I know that a third table must be created but its name doesn't take the name of the two table written with data annotation but the real class name, I prefer that the thrid table take the same name as the 2 others tables in the database.

This behaviour is defined by Entity Framework engine. Please contact Microsoft Support Team.

For the moment, this code is generated by the Entity Framework migration.
My developpement is in progress with table that are often modified, I don't want to have to change the generated code each time I modify the database schema !

Entity Framework 6 introduces custom conventions including lightweight conventions. You can use this feature to implement a logic for renaming table for storing many-to-many relations.

[Feneck91]

Only use fluent mapping in the OnModelCreating method you have written:

Code: Select all

modelBuilder.Entity<EntityUtilisateur>()
                .HasMany(p => p.EntityProfiles)
                    .WithMany(c => c.EntityUtilisateurs)
                .Map(manyToMany => manyToMany
                    .ToTable("Name_For_Third_Table", "GDD")
                    .MapLeftKey("UtilisateurID")

That's all I need ! Thanks a lot for your reply !
We will buy your library soon.
Developer / Team.
After, no need to pay more en each software deployment ?

[Shalex]

We will buy your library soon.
Developer / Team.
After, no need to pay more en each software deployment ?

Our product is royalty-free. No fee is involved on your software deployment.

Frequently asked questions about End Users License Agreement (EULA) of dotConnect for Oracle: http://www.devart.com/dotconnect/oracle ... g-faq.html.