Search code examples
c#entity-frameworklinqef-database-first

.NET Core with EF "Invalid object name 'ClassName" " with database first

I'm currently doing an API in .NET Core with EF using database first. Si I created my database and used the command :

Scaffold-DbContext "Server=server;Database=database;User Id=user;Password=pwd" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f

(Putting it since things are not working since I used the -f flag to overwrite my model folder).

My database looks like this :
enter image description here

And this looks exactly like it should be :
enter image description here

All the code generated by itself looks great, let's take the User per exemple :

modelBuilder.Entity<User>(entity =>
            {
                entity.ToTable("User");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.Admin).HasColumnName("admin");

                entity.Property(e => e.CanUpdate).HasColumnName("can_update");

                entity.Property(e => e.CryptoSessionKey).HasColumnName("crypto_session_key");

                entity.Property(e => e.Email)
                    .HasMaxLength(80)
                    .HasColumnName("email");

                entity.Property(e => e.IdCompany).HasColumnName("id_company");

                entity.Property(e => e.Password).HasColumnName("password");

                entity.Property(e => e.Salt).HasColumnName("salt");

                entity.HasOne(d => d.IdCompanyNavigation)
                    .WithMany(p => p.Users)
                    .HasForeignKey(d => d.IdCompany)
                    .HasConstraintName("FK_User_Company");
            });

But as soon as I call any function from my controller class, my context objets are all returning an "Invalid Object Name" exception while searching into them (even looking inside it during debugger). This is the line it fails on even if it doesn't have a lot of important in my opinion :

var userInDb = _context.Users.Single(c => c.Email == email);

I tried renaming all contexts' lists of objects by the name in singular (like in db) but same result. There is the exceptions :

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (63ms) [Parameters=[@__email_0='td@netbee.be' (Size = 80)], CommandType='Text', CommandTimeout='30'] SELECT TOP(2) [u].[id], [u].[admin], [u].[can_update], [u].[crypto_session_key], [u].[email], [u].[id_company], [u].[password], [u].[salt] FROM [User] AS [u] WHERE [u].[email] = @__email_0

fail: Microsoft.EntityFrameworkCore.Query[10100] An exception occurred while iterating over the results of a query for context type 'NetbeeMainDatabaseAPI.Models.MainDatabase_testContext'. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'User'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() ClientConnectionId:77bc7d9d-486f-44ef-a297-b29c204132e3 Error Number:208,State:1,Class:16 Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'User'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() ClientConnectionId:77bc7d9d-486f-44ef-a297-b29c204132e3 Error Number:208,State:1,Class:16

Thank you for your time reading this and possibly helping.

Solution

  • After reviewing my code everywhere the connection string was mentionned, I realised that with function Scaffold-DbContext, it creates the dbconnection in the context class itself. But I'm used to create the dbconnection in the startup class, which connection string was set there too. But the one there was outdated, and this one was used before the one generated by the function, which was correct.

    Now I put the connection string in a file and I access the same everywhere ! Thanks to @feal which help me in checking connection strings.