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?

score 10 · Accepted Answer · edited Feb 04 '11 at 18:10

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.

Sadly this no longer works, looks like EF 4.1 removed `IConfigurationConvention`. — Omar, Aug 13 '11 at 21:47
You can now override OnModelCreating in your DbContext and map an entity to a table by using modelBuilder.Entity().ToTable("MyTableName", "MySchemaName"); — hnafar, Jun 10 '14 at 18:08

Tom Halladay · Answer 2 · 2014-10-13T14:47:34.717

7

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

edited Oct 13 '14 at 14:47

answered Mar 16 '12 at 19:22

Tom Halladay

5,651
6
46
65

score 1 · Answer 3 · answered Mar 03 '11 at 01:06

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

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

3 Answers3

Linked