Entity Framework Core 6 Problem
-
I'm using EF 6 Core Code First. I'm trying to set up some basic code and I'm getting an exception at runtime. Here's my Context class:
using Microsoft.EntityFrameworkCore;
namespace EFCoreDBFirstExample.Models
{
public partial class ModelContext : DbContext
{
public virtual DbSet Departments { get; set; }
public virtual DbSet Employees { get; set; }protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { var connectionString = @"Server=MAROIS\_KEVIN\_1\\SQLEXPRESS;Database=Test;Trusted\_Connection=true;Encrypt=false;"; optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure()); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(entity => { entity.ToTable("Departments", "public"); entity.HasKey(e => e.DepartmentId) .HasName("DeptartmentId"); entity.Property(e => e.DepartmentId) .HasColumnName("DeptartmentId"); entity.Property(e => e.DepartmentName) .HasColumnName("DepartmentName") .HasColumnType("varchar") .HasMaxLength(30); }); modelBuilder.Entity(entity => { entity.ToTable("EmployeeName", "public"); entity.HasKey(e => e.EmployeeId) .HasName("EmployeeId"); entity.Property(e => e.EmployeeId) .HasColumnName("EmployeeId"); entity.Property(e => e.EmployeeName) .HasColumnName("EmployeeName") .HasColumnType("varchar") .HasMaxLength(30); }); } }}
Here's how I'm using it
using EFCoreDBFirstExample.Models;
using (var db = new ModelContext())
{
// Creating a new department and save it to the database
var newDept = new Departments();
newDept.DepartmentId = 1;
newDept.DepartmentName = "Development";db.Departments.Add(newDept); var count = db.SaveChanges(); //<======================= ERROR HERE Console.WriteLine("{0} records saved to database", count); // Retrieve and display the data Console.WriteLine(); Console.WriteLine("Departments:"); foreach (var dept in db.Departments) -
Have you checked the file permissions for the database? I suppose that for a Trusted Connection that is not relevant. Can you open the database using that connection string in SSMS, or VS I suppose?
No I didn't check. There is no DB. EF is supposed to create ut
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Yes, Kevin is my Windows login, but all my other apps use Trusted Connection without a user specified.
connectionString="Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Jayhawk;Trusted_Connection=true;"
I do have a user Kevin in SQL and I tried it with the password. I also just tried creating a new user and password and got the same error. Something else is wrong. Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
When you created the "kevin" account in SQL Server Manager, did you tell it to use Windows Authentication or SQL Server Authentication?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
When you created the "kevin" account in SQL Server Manager, did you tell it to use Windows Authentication or SQL Server Authentication?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakWindows Auth
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Windows Auth
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
Next up, on the Server Roles tab, which roles have checkmarks next to them?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Next up, on the Server Roles tab, which roles have checkmarks next to them?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakPublic & SysAdmin
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Public & SysAdmin
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
OK, based on your answers, the login should work. I would go back to SQL Server Manager and create a new SQL account, password, and permissions of dbcreator and public. Change your connection string and remove TrustedConnection and put in the username and password of the account you create.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
OK, based on your answers, the login should work. I would go back to SQL Server Manager and create a new SQL account, password, and permissions of dbcreator and public. Change your connection string and remove TrustedConnection and put in the username and password of the account you create.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakThat's what I thought too. I have created a new login and still get the same error. Here's my Conn string
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = @"Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Test;User Id=FatALbert;Password=AlbertIsFat!;;Encrypt=false;";
optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure());
}and the exception
Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException
HResult=0x80131500
Message=The maximum number of retries (6) was exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'. See the inner exception for the most recent failure.
Source=Microsoft.EntityFrameworkCore
StackTrace:
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Program.$(String[] args) in C:\Projects\SandBox\Learning\EF6Core\Database First\EFCoreDBFirstExample\Program.cs:line 11
This exception was originally thrown at this call stack:
[External Code]Inner Exception 1:
SqlException: Cannot open database "Test" requested by the login. The login failed.
Login failed for user 'FatAlbert'.I also tried creating the DB 'Test' first in SQL and assigning it to FatAlbert and the same exception. Again, I have other apps running on my Dev PC that don't use SQL Authentication and they connect just fine. So I has to be something with EF.
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
That's what I thought too. I have created a new login and still get the same error. Here's my Conn string
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = @"Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Test;User Id=FatALbert;Password=AlbertIsFat!;;Encrypt=false;";
optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure());
}and the exception
Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException
HResult=0x80131500
Message=The maximum number of retries (6) was exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'. See the inner exception for the most recent failure.
Source=Microsoft.EntityFrameworkCore
StackTrace:
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Program.$(String[] args) in C:\Projects\SandBox\Learning\EF6Core\Database First\EFCoreDBFirstExample\Program.cs:line 11
This exception was originally thrown at this call stack:
[External Code]Inner Exception 1:
SqlException: Cannot open database "Test" requested by the login. The login failed.
Login failed for user 'FatAlbert'.I also tried creating the DB 'Test' first in SQL and assigning it to FatAlbert and the same exception. Again, I have other apps running on my Dev PC that don't use SQL Authentication and they connect just fine. So I has to be something with EF.
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
Wait a minute. Did you put the connection string ONLY in the OnConfiguring method?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Wait a minute. Did you put the connection string ONLY in the OnConfiguring method?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakYes. The examples I followed show it there. Isn't that what OnConfiguring does? A one time set up?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Yes. The examples I followed show it there. Isn't that what OnConfiguring does? A one time set up?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
This works differently from the older Entity Frameworks. The reason you're getting the login failure is because the database does not exist in SQLEXPRESS yet. You cannot create the database just by running the code you have, as is. You first have to enable migrations in the project, then create your first migration ("InitialCreate"). Once that is done, you can add the following line your program:
using (var db = new ModelContext())
{
// Update the database to the latest migration
db.Database.Migrate();// Creating a new department and save it to the database var newDept = new Departments(); newDept.DepartmentId = 1; newDept.DepartmentName = "Development";I highly recommend AGAINST doing this! You are far better off managing and applying migrations using the EF command line tools! You can EASILY make a mistake that will destroy a production database just by running your code at the wrong time and with the wrong connection string! Migrations Overview - EF Core | Microsoft Learn[^] On top of that, there's a few mistakes in your code in your initial post above. For example, every DbSet should be DbSet<type> ...
public virtual DbSet Departments { get; set; } public virtual DbSet Employees { get; set; }...and there are mispellings in your modelBuilder code, like
entity.ToTable(**"Employees"**, "public");Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Yes. The examples I followed show it there. Isn't that what OnConfiguring does? A one time set up?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
In a production environment, you would NEVER hardcode a connection string. This would prevent you from developing against a dev version of the database and testing code and migrations without impacting the production database. Read the entire section on Migrations, not just the Overview: Migrations Overview - EF Core | Microsoft Learn[^]
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
In a production environment, you would NEVER hardcode a connection string. This would prevent you from developing against a dev version of the database and testing code and migrations without impacting the production database. Read the entire section on Migrations, not just the Overview: Migrations Overview - EF Core | Microsoft Learn[^]
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakYa, I Put it there just for testing. Thanks for all your help
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
This works differently from the older Entity Frameworks. The reason you're getting the login failure is because the database does not exist in SQLEXPRESS yet. You cannot create the database just by running the code you have, as is. You first have to enable migrations in the project, then create your first migration ("InitialCreate"). Once that is done, you can add the following line your program:
using (var db = new ModelContext())
{
// Update the database to the latest migration
db.Database.Migrate();// Creating a new department and save it to the database var newDept = new Departments(); newDept.DepartmentId = 1; newDept.DepartmentName = "Development";I highly recommend AGAINST doing this! You are far better off managing and applying migrations using the EF command line tools! You can EASILY make a mistake that will destroy a production database just by running your code at the wrong time and with the wrong connection string! Migrations Overview - EF Core | Microsoft Learn[^] On top of that, there's a few mistakes in your code in your initial post above. For example, every DbSet should be DbSet<type> ...
public virtual DbSet Departments { get; set; } public virtual DbSet Employees { get; set; }...and there are mispellings in your modelBuilder code, like
entity.ToTable(**"Employees"**, "public");Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakThanks alot!
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
This works differently from the older Entity Frameworks. The reason you're getting the login failure is because the database does not exist in SQLEXPRESS yet. You cannot create the database just by running the code you have, as is. You first have to enable migrations in the project, then create your first migration ("InitialCreate"). Once that is done, you can add the following line your program:
using (var db = new ModelContext())
{
// Update the database to the latest migration
db.Database.Migrate();// Creating a new department and save it to the database var newDept = new Departments(); newDept.DepartmentId = 1; newDept.DepartmentName = "Development";I highly recommend AGAINST doing this! You are far better off managing and applying migrations using the EF command line tools! You can EASILY make a mistake that will destroy a production database just by running your code at the wrong time and with the wrong connection string! Migrations Overview - EF Core | Microsoft Learn[^] On top of that, there's a few mistakes in your code in your initial post above. For example, every DbSet should be DbSet<type> ...
public virtual DbSet Departments { get; set; } public virtual DbSet Employees { get; set; }...and there are mispellings in your modelBuilder code, like
entity.ToTable(**"Employees"**, "public");Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakOK, so I went through the Migrations Oerview and installed the necessary packages. I then ran Add Migration and Update Database. Now I get this
Applying migration '20221127185110_Initial'.
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF SCHEMA_ID(N'public') IS NULL EXEC(N'CREATE SCHEMA [public];');
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'public' in the database.
CREATE SCHEMA failed due to previous errors.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkC -
OK, so I went through the Migrations Oerview and installed the necessary packages. I then ran Add Migration and Update Database. Now I get this
Applying migration '20221127185110_Initial'.
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF SCHEMA_ID(N'public') IS NULL EXEC(N'CREATE SCHEMA [public];');
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'public' in the database.
CREATE SCHEMA failed due to previous errors.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCChange "public" to "dbo" in your code. Where did you find this tutorial you're following? The problem with Linq-To-Sql is that it is a dead product and no longer under development.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Change "public" to "dbo" in your code. Where did you find this tutorial you're following? The problem with Linq-To-Sql is that it is a dead product and no longer under development.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakDave Kreskowiak wrote:
Where did you find this tutorial you're following?
I have the book I mentioned in my other reply, as well as a couple of YouTube vids I followed.
Dave Kreskowiak wrote:
The problem with Linq-To-Sql is that it is a dead product and no longer under development.
Yup. Thats why I'm doing this.
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
Change "public" to "dbo" in your code. Where did you find this tutorial you're following? The problem with Linq-To-Sql is that it is a dead product and no longer under development.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakOK, I think I have this now. I did the migration and update, and the DB was created and Department and Employee tables added. Next, I added a Companies entity, linked it to Departments, and ran
dotnet ef migrations add AddCompanies
dotnet ef database updateand the Db is now up to date. So I need to run these 2 commands each time I make a change?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
OK, I think I have this now. I did the migration and update, and the DB was created and Department and Employee tables added. Next, I added a Companies entity, linked it to Departments, and ran
dotnet ef migrations add AddCompanies
dotnet ef database updateand the Db is now up to date. So I need to run these 2 commands each time I make a change?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
Yes. You can make a ton of changes and wrap them all in a single migration. Open the migration file and take a look at what's generated.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Yes. You can make a ton of changes and wrap them all in a single migration. Open the migration file and take a look at what's generated.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakOK, I get it now. That book I referenced doesn't show any of this, so far. Now I get why nothing was happening when I ran my console app. I was under the impression that creating the DBContext would do all of this
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.