locked
Entity Framework Core Invalid Column after updating database table RRS feed

  • 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"
      }
    }

    Wednesday, April 18, 2018 8:08 PM

Answers

  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 19, 2018 12:19 PM

All replies

  • 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

    Thursday, April 19, 2018 2:44 AM
  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 19, 2018 12:19 PM