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:

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

score 14 · Accepted Answer · answered Aug 25 '11 at 03:01

14

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

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

answered Aug 25 '11 at 03:01

Adam Maras

26,269
6
65
91

You sir, are a genius! You know, this is actually a much better answer than the one provided for this question: http://stackoverflow.com/questions/6399443/entityframework-using-wrong-tablename – Ryan Kirkman Aug 25 '11 at 03:09
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. – Adam Maras Aug 25 '11 at 03:14
5

Is there a way to set this globally? EF uses dbo by deafult wihtout us specyfing it.. so annoying changing all my classes – Piotr Kula Aug 03 '12 at 13:52
1

@ppumkin check [the answer by Bill below](http://stackoverflow.com/a/26549837/148412). – ANeves Nov 06 '14 at 12:57

score 7 · Answer 2 · answered Oct 24 '14 at 14:20

With EF6, you can now do this.

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

score 7 · Answer 3 · answered Aug 25 '11 at 03:13

7

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");

answered Aug 25 '11 at 03:13

Erik Funkenbusch

92,674
28
195
291

Thanks! I wasn't aware that the `ToTable` method was overloaded to allow specification of a schema name. Very useful! – Ryan Kirkman Aug 25 '11 at 03:16
This will ruin pluralization. – ANeves Nov 06 '14 at 12:58

score 4 · Answer 4 · answered Feb 08 '13 at 21:59

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.

Thank you ! and waht a spectacular answer that is +1 +beer – Piotr Kula Feb 08 '13 at 23:24 — Piotr Kula, Feb 08 '13 at 23:24

Charles Graham · Answer 5 · 2012-08-10T14:28:00.150

3

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

edited Aug 10 '12 at 14:28

answered Aug 25 '11 at 03:02

Charles Graham

1,157
5
6

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

5 Answers5

Linked