Entity Framework Core Invalid Column after updating database table

Question

User-1188570427 posted

I am using code first from the database using Asp.net core.

I have a table: UserRoles where I need to remove a CategoryId column and add in the OrganizationId reference.

Everything works, but when I go to delete (in order) my organizations, I try to delete from UserRoles first.  Well for some reason I am seeing the error below.

Any ideas?

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'CategoriesCategoryId'.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<BufferlessMoveNext>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<MoveNext>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.<MoveNextCore>d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Linq.AsyncEnumerable.AsyncIterator`1.<MoveNext>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.<MoveNext>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

Here is my code to set my connection string. Now in my local SQL db, I do have multiple databases for initial categlog. Other tables DO have that column still because I haven't updated those databses. Could the appsetting json file be pulling another database for some reason?

        /// <summary>
        /// <para>
        /// Override this method to configure the database (and other options) to be used for this context.
        /// This method is called for each instance of the context that is created.
        /// 
        /// Note: All consuming applications must declare an appsettings.json file with connection strings - they must also add a dependency to
        /// Microsoft.Extensions.Configuration.Json
        /// 
        /// </para>
        /// <para>
        /// In situations where an instance of <see cref="T:Microsoft.EntityFrameworkCore.DbContextOptions" /> may or may not have been passed
        /// to the constructor, you can use <see cref="P:Microsoft.EntityFrameworkCore.DbContextOptionsBuilder.IsConfigured" /> to determine if
        /// the options have already been set, and skip some or all of the logic in
        /// <see cref="M:Microsoft.EntityFrameworkCore.DbContext.OnConfiguring(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder)" />.
        /// </para>
        /// </summary>
        /// <param name="optionsBuilder">A builder used to create or modify options for this context. Databases (and other extensions)
        /// typically define extension methods on this object that allow you to configure the context.</param>
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if(!optionsBuilder.IsConfigured)
            {
                // Try to get the connection string from the hosted environment variables
                var connectionString = Environment.GetEnvironmentVariable("DefaultConnection");

                if (string.IsNullOrWhiteSpace(connectionString))
                {
                    // Fallback to appsettings.json in the host applications base directory
                    connectionString = ConnectionUtilities.GetConnectionString("DefaultConnection");
                }

                optionsBuilder.UseSqlServer(connectionString);
            }
        }

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=mycomputername;Initial Catalog=MYTable;Integrated Security=True"
  }
}

Answer 1

User283571144 posted

Hi tvb2727,

According to the error message and description, I couldn't directly find the reason why show this error.

Could you please post the details codes about your poco model?

 Entity Framework will assume that any class that inherits from a POCO class that is mapped to a table on the database requires a CategoriesCategoryId column.

So I guess your poco class is not map the table in the database.

I suggest you firstly use migration command to change the database table as the poco class. 

Best Regards,

Brando

Answer 2

User-1188570427 posted

Hi tvb2727,

According to the error message and description, I couldn't directly find the reason why show this error.

Could you please post the details codes about your poco model?

 Entity Framework will assume that any class that inherits from a POCO class that is mapped to a table on the database requires a CategoriesCategoryId column.

So I guess your poco class is not map the table in the database.

I suggest you firstly use migration command to change the database table as the poco class. 

Best Regards,

Brando

So I figured out the issue.

So I had to remove a reference to CategoryId from My UserRole entity. Well when I did that, the Category entity had a reference of an ICollection to the UserRole Entity. Well I never removed that from the Category Entity and I guess that is what it was having the errors on, BUT it was giving me the error when I was trying to delete from the UserRole entity. I still don't know why it did not tell me that the issue was actually on the entity that I was removing the reference to, but I guess it back tracks and binds to validate everything is good.

Is this correct or can someone tell me why it had that error as it did?