Entity Framework Core 6 Problem

Kevin Marois

Windows Auth

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

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 Kreskowiak

Kevin Marois

Public & SysAdmin

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

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 Kreskowiak

Kevin Marois

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.

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 Kreskowiak

Kevin Marois

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.

Dave Kreskowiak

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

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 Kreskowiak

Kevin Marois

Ya, 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.

Kevin Marois

Thanks alot!

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Kevin Marois

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.EntityFrameworkC

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 Kreskowiak

Kevin Marois

Dave 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.

Kevin Marois

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 update

and 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.

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 Kreskowiak

Kevin Marois

OK, 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.

Kevin Marois

How do you get a Prod DB update to date with all the migration changes?

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.