Currently I am deploying my application to a shared hosting environment and code-first with migrations has been working great except for one minor hiccup. Everytime I want to push the site I have to use the "Update-Database -script" option because I have to prepend every table name with [dbo]
because by default the shared host creates a default schema name that is the same name as the database username.
If I log into my shared host and create a database, I then have to create a user. If I name that user admin, then the tables code-first creates while logged in as admin look something like this "[admin].[BlogPosts]". When the application runs all the tables are created but I get an EF exception because it says "[dbo].[BlogPosts]" is invalid. If I rename the table's schema name to "[dbo]" instead of "[admin]" that fixes it.
To get around this I have to generate a migrations script to be executed manually and add "[dbo]" in front of all the table names because the script only references the tables by their name, not by their schema and their name.
Is there an easy way to get around this? It would be so nice if all I had to do was publish the application and everything just worked. If it wasn't for the schema name discrepancy it would be a one click deploy and everything would be glorious.
There are 2 ways to change the schema, either by applying the TableAttribute or by implementing the interface IEntityTypeConfiguration<TEntity> . The first option won't help us because the schema is hard-coded. The second option gives us the ability to provide the schema from DbContext to the EF model configuration.
First step is to create a partial class that allows you to pass a value to the constructor of your entities, by default it uses the values from your config file. Next create the function that will modify your store schema . ssdl file in memory.
The default schema for a user is solely used for object-reference in case the user omits the schema when querying objects. When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema.
For those using Entity Framework 6, just use the HasDefaultSchema
method:
public class Contexto : DbContext { public DbSet<User> Users { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.HasDefaultSchema("MyDefaultDbSchema"); } }
You could use the ToTable
method to specify the schema name. If you do not specify the schema name, EF will by convention use dbo
.
public class MyContext { private string schemaName = "Foo"; protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<MyEntity>().ToTable("MyTable", schemaName); } }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With