Code First Custom Convention for schema Naming

[Remco Blok]

Hello,

Just started playing with the new dotConnect for Oracle version that supports EF Code First CTP5. By default EF Code First conventions want to create all objects in a dbo schema. For Oracle I want to specify the schema to create all objects in. So far I had to do the following to get it to work:

protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);

modelBuilder.Conventions.Remove();

modelBuilder.Conventions.Remove();

modelBuilder.Entity().ToTable("MYSCHEMA.EdmMetadata");

modelBuilder.Entity().ToTable("MYSCHEMA.MyEntities");
}

To avoid me having to call modelBuilder.Entity().ToTable("MySchema.MyEntities"); on every entity in my model I would like to create a custom convention for this. Does anybody know how to do this? I also started a thread on the EF forum here http://social.msdn.microsoft.com/Forums ... 9739422049

kind regards

Remco

[AndreyR]

Take a look at this example:

Code: Select all

 public class DefaultSchemaConvention : IConfigurationConvention {

    string defaultSchema;

    public DefaultSchemaConvention(string defaultSchema) {

      if (String.IsNullOrWhiteSpace(defaultSchema))
        throw new ArgumentException("defaultSchema");

      this.defaultSchema = defaultSchema;
    }

    #region IConfigurationConvention Members

    void IConfigurationConvention.Apply(Type memberInfo, Func configuration) {

      EntityTypeConfiguration cfg = configuration();
      string tableName = cfg.EntitySetName;
      if (String.IsNullOrEmpty(tableName))
        tableName = memberInfo.Name;
      cfg.ToTable(tableName, this.defaultSchema);
    }

    #endregion
  }

After implementing you can use this convention in the following way:

Code: Select all

 modelBuilder.Conventions.Add(new DefaultSchemaConvention("MYSCHEMA"));

[Remco Blok]

Fantastic! That does the trick. And it does not override the PluralizingTableNameConvention and I won't have to remove the IncludeMetadataConvention as the DefaultSchemaConvention applies to the EdmMetadata table as well.

One other thing I noticed though is that DbContext.Database.Delete() does not delete any tables that are no longer in the model. So when you rename an entity and do DbContext.Database.Delete() the old tables are not dropped. I suppose DbContext.Database.Delete() was designed for dropping a database and recreating one in SQL Server whereas in Oracle the schema is not dropped, instead the tables that are known in the current version of the model are dropped, leaving any other tables that are no longer known in the model.

Also, does Devart dbMonitor support EF Code First? I cannot get it to work, but then I am new to Devart dbMonitor

[AndreyR]

Yes, this is the current behaviour.
We will investigate the possibility of adding the SQL Server-like DbContext.Database.Delete() functionality (delete all objects from all Schemas the tables are mapped in).
Yes, Devart DbMonitor provides support for Code First.
You should add the OracleMonitor instance to the code, set the IsActive property to true and either add a handler to your code or run the DbMonitor application. Take a look at this article for details.

[francischie]

Hi,

I used the DataAnnotation on my POCO class but it's is not used to set the tablename, instead the value of tableName is the name of the Property of the DBContext of DBSet type.

Here's the example:

Code: Select all

 

 public class SampleDB : DbContext
    {
        public DbSet Customers { get; set; }

        protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove();
            modelBuilder.Conventions.Add(new DefaultSchemaConvention("MYSCHEMA"));
        }
    }

    [Table("CUSTOMER")]
    public class CustomerEntity
    {
        [Column(Name = "CUSTOMER_ID")]
        public int Id { get; set; }

        [Column(Name="FIRST_NAME")]
        public string FirstName { get; set; }
    }

This will generate the SQL code

Code: Select all

SELECT 
"Extent1".CUSTOMER_ID AS CUSTOMER_ID, 
"Extent1".FIRST_NAME AS FIRST_NAME
FROM MYSCHEMA."Customers" "Extent1"

What I'm expecting is:

Code: Select all

SELECT 
"Extent1".CUSTOMER_ID AS CUSTOMER_ID, 
"Extent1".FIRST_NAME AS FIRST_NAME
FROM MYSCHEMA."CUSTOMER" "Extent1"

The only way I can make it work is to repeatedly specify the schema on each POCO class or on Fluent API. Like this one:

POCO:

Code: Select all

 [Table("MYSCHEMA.CUSTOMER")]
    public class CustomerEntity
    {
        [Column(Name = "CUSTOMER_ID")]
        public int Id { get; set; }

        [Column(Name="FIRST_NAME")]
        public string FirstName { get; set; }
    }

or Fluent API:

Code: Select all

protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().ToTable("CUSTOMER", "MYSCHEMA");
            modelBuilder.Conventions.Remove();

        }

[francischie]

Sorry. Only the Fluent API is working not the DataAnnotation

Code: Select all

protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder) 
        { 
            modelBuilder.Entity().ToTable("CUSTOMER", "MYSCHEMA"); 
            modelBuilder.Conventions.Remove(); 

        } 

[AndreyR]

Here is the modified Apply code:

Code: Select all

    void IConfigurationConvention.Apply(Type memberInfo, Func configuration) {

      EntityTypeConfiguration cfg = configuration();
      string tableName = cfg.EntitySetName;
      if(String.IsNullOrEmpty(tableName))
        tableName = memberInfo.Name;
      if(cfg.IsTableNameConfigured) {
        var attribute = memberInfo.GetCustomAttributes(false).OfType().FirstOrDefault();
        if(attribute != null && !string.IsNullOrEmpty(attribute.TableName)) {
          if(!string.IsNullOrEmpty(attribute.SchemaName))
            return;
          tableName = attribute.TableName;
        }
      }
      cfg.ToTable(tableName, this.defaultSchema);
    }

[AndreyR]

We have written an article concerning CTP 5 support. It contains some peculiarities of Code First and Database creation, and some samples as well.

[Remco Blok]

The ado.net team just announced the scope of the EF 4.1 DbContext API & Code First RTW: http://blogs.msdn.com/b/adonet/archive/ ... t-rtw.aspx. No pluggable conventions in the 4.1 RTW. No DefaultSchemaConvention then. I think we'll have to map each table to the correct schema individually. Very disappointing.

Remco

[AndreyR]

Thank you for the information, we are already aware of this problem.
I suppose you are correct, the DefaultSchemaConvention class will not be useful in the upcoming EF 4.1.

[francischie]

Confirmed!

The IConfigurationConvention is dropped and the class ConventionsConfiguration does not have Add method anymore on EF 4.1.

Solution does not work anymore.

[AndreyR]

We will be working on a workaround for this problem as soon as Entity Framework 4.1 RTM is released.

[AndreyR]

We plan to add a workaround that will remove Schema name from all queries and commands. This workaround should solve the problem.
If you have any other suggestions, please visit our Entity Framework Support UserVoice. Please vote for the existing suggestions as well.

[francischie]

Code: Select all

public abstract class DataContext : DbContext
  {
    public string DefaultSchema { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      if (!String.IsNullOrEmpty(DefaultSchema))
      {
        var entityMethod = modelBuilder.GetType().GetMethod("Entity");
        foreach (PropertyInfo dbSet in GetType().GetProperties().Where(t => t.PropertyType.IsGenericType && t.PropertyType.GetGenericTypeDefinition().Equals(typeof(DbSet))))
        {
          var entityType = dbSet.PropertyType.GetGenericArguments();
          var entityMethodGeneric = entityMethod.MakeGenericMethod(entityType);
          var entityConfig = entityMethodGeneric.Invoke(modelBuilder, null);
          var toTableMethod = entityConfig.GetType().GetMethod("ToTable", new Type[] { typeof(string), typeof(string) });
          var tableName = GetTableName(entityType.FirstOrDefault());
          toTableMethod.Invoke(entityConfig, new object[] { tableName, DefaultSchema });
        }
     
      }
      base.OnModelCreating(modelBuilder);
    }
      

    private string GetTableName(Type type)
    {
      var tableAttribute = type.GetCustomAttributes(false).OfType().FirstOrDefault();
      return tableAttribute == null ? type.Name : tableAttribute.Name;
    }

    #endregion
  }

Usage:

Code: Select all

public class SampleContext : DataContext
 {
    public DbSet Students { get; set; }
    public DbSet Classes { get; set; }

    public SampleContext ()
    {
      DefaultSchema = "your_namespace";
    }
 }

[/code]

[AndreyR]

Thank you for sharing the workaround. However, there are several notes:
1. One should remove the IncludeMetadataConvention:

Code: Select all

modelBuilder.Conventions.Remove();

2. If you have a many-to-many relationship, you still need add the mapping explicitly for the link table.