CODEWITHSUNDEEP
sql-serverentity-framework.net-coremigrationentity-framework-core
Lightbringer
Lightbringer

Reputation: 819

EF Core Update-Database command creates schema for user instead of using dbo

I have created a DbContext using EF Core 2.0 and already applied a lot of migrations in the development environment using Update-Database in PM-Console. The tables are created using the dboschema on my localDb as expected and as I know from EF6.

However, when i apply the Update-Database command to my production database on a MSSQL Server, it creates a schema named Domain\Username (my user from my machine). When I then run the server application with its designated domain account, it can not access the database. As a workaround I talked the IT infrastructure guys into allowing this account to execute software on my computer and applied the migritions by executing VisualStudio with this account. Then a schema for this special account is created and thereafter it can access the database fro the webserver application. It works since this special account is currently the only one that needs direct access to the database (besides myself). But this should ne be a permanent solution.

Does anyone know a solution for this? The database has been created in the exactly same manner as for previous EF6 projects. Why are the per user schemas only used when applying the migrations to the production system and not on my development machine and how can I control the schema manually? I tried modelBuilder.HasDefaultSchema("dbo"); but this does not work. Actually it seems to not having any effect at all.

Upvotes: 8

Views: 2704

Answers (2)

Kyle McClellan
Kyle McClellan

Reputation: 985

Calling modelBuilder.HasDefaultSchema("dbo") in the db context solved this problem for me (EF 3.1.7), but it needs to be there when you generate the migration (not when it gets applied). This makes EF add schema: "dbo" to calls to migrationBuilder.CreateTable(...), overriding the SQL default (which can vary based on the provider/implementation).

IMO it's always a good idea to specify a schema for your model.

Upvotes: 2

Pat
Pat

Reputation: 2750

I had the same problem and believe it was caused by having my domain\user listed under MyDatabase -> Security -> Users. I fixed the issue by deleting my account from the users (right click -> delete) and the next time I ran update-database it prefixed the tables correctly with 'dbo'.

enter image description here

Upvotes: 4

Related Questions