Code First Custom Convention for schema Naming

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Remco Blok
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Code First Custom Convention for schema Naming

Post by Remco Blok » Wed 19 Jan 2011 08:54

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 19 Jan 2011 09:45

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"));
Last edited by AndreyR on Wed 19 Jan 2011 10:08, edited 1 time in total.

Remco Blok
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Post by Remco Blok » Wed 19 Jan 2011 10:03

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 19 Jan 2011 10:21

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
Posts: 5
Joined: Fri 04 Feb 2011 22:32
Location: United States
Contact:

custom defaultSchemaconvention does not work for correctly

Post by francischie » Fri 04 Feb 2011 22:54

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
Posts: 5
Joined: Fri 04 Feb 2011 22:32
Location: United States
Contact:

Post by francischie » Fri 04 Feb 2011 23:08

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 08 Feb 2011 14:28

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 02 Mar 2011 11:56

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
Posts: 25
Joined: Tue 14 Dec 2010 12:34

EF 4.1 DbContext API Code First RTW no pluggable conventions

Post by Remco Blok » Thu 03 Mar 2011 08:21

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 03 Mar 2011 10:30

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
Posts: 5
Joined: Fri 04 Feb 2011 22:32
Location: United States
Contact:

Post by francischie » Fri 18 Mar 2011 17:49

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 21 Mar 2011 09:30

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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 24 Mar 2011 13:54

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
Posts: 5
Joined: Fri 04 Feb 2011 22:32
Location: United States
Contact:

My alternative solution

Post by francischie » Thu 24 Mar 2011 20:42

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 25 Mar 2011 13:50

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.

Post Reply