Entity Framework Database first multiple schemas duplicate table names

Question

I'm working with an existing database that has multiple schemas. In addition, different schemas duplicatate table names. Code First deployment is not an option.

When generating my edmx, I'm hitting multiple issues, but the main one I'm concerned about is naming convention - I'd like to prefix all entities with the schema name unless the schema name is dbo.

So for example, I have a Person table in both the emp schema and the dbo schema, I'd like to have two entities - emp_Person and Person.

I know it's possible to add prefixes (I found an example at codeplex) but it looks like it's always going to apply the same prefix everywhere.

Any suggestions or links?

Answer 1

Code First can meet your needs. Prefix your classes as you mention and then let EF handle the mapping behind the scenes. You can do this in a single context:

public class Person
{
    public int PersonId { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    ...
}

public class empPerson
{
    public int PersonId { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    ...
}

Now just use fluent configuration in your context to map:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Person>()
       .ToTable("Person", "schema1")
       .HasKey(p => p.PersonId);

    modelBuilder.Entity<empPerson>()
       .ToTable("Person", "emp")
       .HasKey(p => p.PersonId);

    // other fluent code for relationships, etc.
}

See https://msdn.microsoft.com/en-us/data/jj591617.aspx#2.3