Entity Framework 4: Code First - Creating db in another schema? MapSingleType?

Question

I have a database and I am using two different schemas. Schemas are like namespaces (correct me if I am wrong). This way I have one database and currently two schemas, so the tables in one schema can be named the same as the tables in the other schema because they are in separate schemas.

How do I get EF Code first to talk to a different schema and not the default schema?

Is it something to do with MapSingleType and overriding a method or can I do something else?

Answer 1

You can implement the following convention:

public class DefaultSchemaConvention :
             IConfigurationConvention<Type, EntityTypeConfiguration>
{
    string defaultSchema;
    public DefaultSchemaConvention(string defaultSchema)
    {
        if (String.IsNullOrWhiteSpace(defaultSchema))
            throw new ArgumentException("defaultSchema");
        this.defaultSchema = defaultSchema;
    }

    void IConfigurationConvention<Type, EntityTypeConfiguration>.Apply(
         Type memberInfo, Func<EntityTypeConfiguration> configuration)
    {
      EntityTypeConfiguration cfg = configuration();
      string tableName = cfg.EntitySetName;
      if (String.IsNullOrEmpty(tableName))
          tableName = memberInfo.Name;
      cfg.ToTable(tableName, this.defaultSchema);
    }
}  

Usage:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.Edm.Db.ColumnTypeCasingConvention>();
    modelBuilder.Conventions.Add(new DefaultSchemaConvention("TEST"));
}  

There is a couple of side notes by Arthur Vickers here concerning TPT inheritance and many-to-many relations.

Answer 2

I don't know about the CodeFirst model, but the DataAnnotation for "Table" includes a schema option. My code reads like this:

<Table("Product", Schema:="SalesLT")>
Public Class Product

End Class

This helped me deal with alternate schemas