Specify an SQL username other than dbo in Code First Entity Framework ( C# ASP.NET MVC 3 )

Question

I'm trying to connect to an SQL Server 2008 database in a shared hosting environment from C# from within an ASP.NET MVC 3 application connecting via EF (code first).

My problem is that the generated SELECT statement looks like so:

SELECT ... FROM [dbo].[TableName]

which throws the error Invalid object name, but works fine when I do:

SELECT ... FROM [mySQLUserName].[TableName]

How do I specify a username other than dbo (e.g. mySQLUserName)?

---

EDIT:

The closest articles I have found that are relevant to this issue are:

• http://weblogs.asp.net/scottgu/archive/2010/08/03/using-ef-code-first-with-an-existing-database.aspx
• http://weblogs.asp.net/scottgu/archive/2010/07/23/entity-framework-4-code-first-custom-database-schema-mapping.aspx

with specific emphasis on the second article, however it doesn't specify how to set a username other than dbo

Brie

Answer 1

You can specify the schema using a property on the TableAttribute that decorates your entity classes.

[Table("TableName", Schema = "mySQLUserName")]

Sulphur

You sir, are a genius! You know, this is actually a much better answer than the one provided for this question: #6399943 – Brie

The OnModelCreating/ToTable solution is good for more advanced mapping scenarios, but since you know the schema name at compile time, the simple option should work just fine. – Sulphur

Is there a way to set this globally? EF uses dbo by deafult wihtout us specyfing it.. so annoying changing all my classes – Disturbance

@ppumkin check the answer by Bill below. – Neglectful

Answer 2

You don't say which version of EF you're using. If you're using Code First (4.1) you can specify the schema on a table attribute:

[Table("Users", Schema = "myschema")]
public class User { .. }

You can use Scott's article (the second one) as a basis, but you add an additional parameter. ie.:

modelBuilder.Entity<YourType>().ToTable("TableName", "SchemaName"); 

Sclerodermatous

Thanks! I wasn't aware that the ToTable method was overloaded to allow specification of a schema name. Very useful! – Brie

This will ruin pluralization. – Neglectful

Answer 3

With EF6, you can now do this.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("logs");  //set default schema
        modelBuilder.Configurations.Add(new LogMap());
        ...
    }

Twister

Answer 4

I know this question is a bit old, but I came across it in my research and came up with a solution that may benefit others, and have discussed it privately with @ppumkin.

The schema name can be passed as a string to the ToTable() method, so essentially using a member of the containing class instead of a hard-coded value allows you to dynamically specify the schema name upon context creation.

This is a dumbed down version of what I have:

public class FooDbContext : DbContext
{
    public string SchemaName { get; set; }

    static FooDbContext()
    {
        Database.SetInitializer<FooDbContext>(null);
    }

    public FooDbContext(string schemaName)
        : base("name=connString1")
    {
        this.SchemaName = schemaName;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new City_Map(this.SchemaName));
        modelBuilder.Configurations.Add(new Customer_Map(this.SchemaName));
        modelBuilder.Configurations.Add(new CustomerSecurity_Map(this.SchemaName));
        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<City> Cities { get; set; }

}

And the mapping abstract class:

public abstract class SchemaNameEntityTypeConfiguration<TEntityType> : EntityTypeConfiguration<TEntityType> where TEntityType : class
{
    public string SchemaName { get; set; }
    public SchemaNameEntityTypeConfiguration(string schemaName)
        : base()
    {
        this.SchemaName = schemaName;
    }

    public new void ToTable(string tableName)
    {
        base.ToTable(tableName, SchemaName);
    }
}

Implementation:

public class City_Map : SchemaNameEntityTypeConfiguration<City>
{
    public City_Map(string schemaName)
        : base(schemaName)
    {
        ToTable("City");
        HasKey(t => t.Code);

        Property(t => t.Code)
            .HasColumnType("integer")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        Property(t => t.CityName)
            .HasColumnName("City")
            .HasMaxLength(50);

        Property(t => t.State)
            .HasMaxLength(2);
    }
}

The key thing to note here is the ToTable() method in SchemaNameEntityConfiguration. It overrides the superclass' method so when the implementations call ToTable(tableName) it supplies the schema name as well.

*Important note: EntityTypeConfiguration.ToTable() is non-virtual, and the abstract SchemaNameEntityTypeConfiguration hides that method with its own and thus won't be called virtually if the _Map object is type as EntityTypeConfiguration.

It was one concern of mine but there's an easy (and only slightly annoying) work around: instead of implementing a base class that supplies it automatically, just ensure in the _Map classes you pass the schemaName to ToTable().

Usage:

using (FooDbContext context = new FooDbContext("theSchemaName"))
{
    foreach (
        var customer in context.Customers
                .Include(c => c.City)
            .Where(c => c.CustomerName.StartsWith("AA"))
            .OrderBy(c => c.CustomerCode)
        )
    {
        Console.WriteLine(string.Format(
            "{0:20}: {1} - {2}, {3}",
            customer.CustomerCode,
            customer.CustomerName,
            customer.City.CityName,
            customer.City.State));
    }
}

Disclaimer: I haven't tested using multiple contexes within the same program. It shouldn't have an issue, but if the DbContext caches the model at a static class level (rather than at the instance-level), it could be a problem. That may be resolved by creating separate subclasses of the context though, each specifying a different schema name.

Unmoving

Thank you ! and waht a spectacular answer that is +1 +beer – Disturbance

Answer 5

You can either decorate your class with the TableAttribute and specify the Schema, or you could try what this post describes.

Temuco