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?

Parke

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.

Burchfield

Sadly this no longer works, looks like EF 4.1 removed IConfigurationConvention. – Amie

You can now override OnModelCreating in your DbContext and map an entity to a table by using modelBuilder.Entity<MyCustomType>().ToTable("MyTableName", "MySchemaName"); – Myoglobin

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

Ashil

Answer 3

Here is an example how to make entity framework use table schemas based on namespace(s) of your models. For example if you have a model RiaLib.Data.Models.Membership.User then corresponding db table will be called [Membership].[User] instead of [dbo].[User].

public class DatabaseContext : DbContext
{
    public DbSet<Membership.User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new TableSchemaConvention());

        base.OnModelCreating(modelBuilder);
    }
}

https://github.com/rialib/efextensions > TableSchemaConvention.cs

Togetherness