question

Arnab-7575 avatar image
0 Votes"
Arnab-7575 asked AgaveJoe commented

Cant Update dataBase after Adding a new Migration In EF core Code first approach

My class is
public class Ittracker
{

         [Required]
         [RegularExpression("([a-zA-Z][a-zA-Z ]+)", ErrorMessage = "Only alphabets are allowed")]
         public string Name { get; set; }
           
         public int Id { get; set; }
    
         [Required]
         [RegularExpression("(^EST\\d+$)", ErrorMessage = "Invalid EmployeCode  ")]
    
         public string EmployeCode { get; set; }
    
         [Required]
         [EmailAddress]
         public string EmailId { get; set; }
         [Required]
         public string Priority { get; set; }
    
         [Required]
         public string SupportType { get; set; }
    
         [Required]
         public string RequestDescription { get; set; }
         public string Requeststatus { get; set; }
    
         public string caseDetails { get; set; }
    
         public string OptionalText { get; set; }
    
    
         [DataType(DataType.Date)]
         [BindProperty]
         public DateTime StartDate { get; set; }
    
         [DataType(DataType.Date)]
    
    
         [BindProperty]
         public DateTime? EndDate { get; set; }
    
         [BindProperty]
    
         public DateTime? RequestDate { get; set; }
    
         [BindProperty, EmailAddress, Required]
         public string MainEmailSend { get; set; }
    
         [BindProperty, EmailAddress, Required]
         public string ccEmail { get; set; }
    
           
     }

so when I tried to add MainEmailSend,and ccEmail extra two properties and generated a migration and when I Did Update Database its giving the Error

 PM> Add-Migration AddIttrackertoDatabasePhase2
 Build started...
 Build succeeded.
 An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
 To undo this action, use Remove-Migration.
 PM> Update-Database
 Build started...
 Build succeeded.
 fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
       Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
       DECLARE @var0 sysname;
       SELECT @var0 = [d].[name]
       FROM [sys].[default_constraints] [d]
       INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
       WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Ittracker]') AND [c].[name] = N'ccEmail');
       IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Ittracker] DROP CONSTRAINT [' + @var0 + '];');
       ALTER TABLE [Ittracker] ALTER COLUMN [ccEmail] nvarchar(max) NULL;
 Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
 DECLARE @var0 sysname;
 SELECT @var0 = [d].[name]
 FROM [sys].[default_constraints] [d]
 INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
 WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Ittracker]') AND [c].[name] = N'ccEmail');
 IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Ittracker] DROP CONSTRAINT [' + @var0 + '];');
 ALTER TABLE [Ittracker] ALTER COLUMN [ccEmail] nvarchar(max) NULL;
 Microsoft.Data.SqlClient.SqlException (0x80131904): ALTER TABLE ALTER COLUMN failed because column 'ccEmail' does not exist in table 'Ittracker'.
    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 contextType)
    at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
    at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
    at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
 ClientConnectionId:a1f593cf-3ab6-4330-a056-6b8609606a6d
 Error Number:4924,State:1,Class:16
 ALTER TABLE ALTER COLUMN failed because column 'ccEmail' does not exist in table 'Ittracker'.


For trying fresh I deleted some Previous Migrations from migration folder .....So what is going wrong and what is the solution plzz suggest .

And this is my Table structure
223087-screenshot-2022-07-21-172944.jpg



Do I have to Delete The whole Ittracker Table?

dotnet-entity-framework-coredotnet-aspnet-core-razor
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The error message indicates the code is trying to modify ccEmail but the ccEmail column does not exist. Did you write a script to remove or modify the table outside core first?

Also, the model has [BindProperty] attributes which is very odd. [BindProperty] is found in a Razor Page not EF an model.

0 Votes 0 ·

The thing is I created Ittracker class and initially added some props into it then i did all the cruds and all was working fine after that I added 3 more props and generated migration and updated-database that also was going smooth but when today I added last two attribute and having this problem.

0 Votes 0 ·

I'm not sure what code first migrations were done in the past or if the ccEmail field exists in the Ittracker table. If you take a look at the SQL produced by the migration, the SQL is trying to drop then add a constraint on the ccEmail column but the columns is not found.

Does the ccEmail column currently exist in the Ittracker table?

I think part of the problem is you are mixing view models with entities. As already stated, you should not have [BindProperty] on an entity. Also, [Required] has a different meaning when used in an entity as opposed to a view model. When used in an entity, [Required] is a database constraint. When used in a view model, [Required] drives user input validation.

I recommend cleaning up your code by separating the view models from the entities.

See the official documentation for handling EF Core Entities.

Creating and configuring a model

0 Votes 0 ·
Show more comments

0 Answers