1

I'm using .net core 3.1. I have a table in MSSQL which has a nullable decimal column, and In code, I define it in its model file as (decimal?). In the repository when I want to insert my model to DB, I have this:

//financialDb is my model that I want to add in DB
context.Financial.Add(financialDb);
await context.SaveChangesAsync();

when saveChanges is called, I got this error :

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> System.Data.SqlClient.SqlException (0x80131904): Error converting data type nvarchar to numeric.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:d4ad0287-bbd8-4085-9420-292a8fa161d2
Error Number:8114,State:5,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

when I check the insertion query in the profiler I saw that EFCore change nullable decimal to nvarchar(4000) and because of that, I got this error. What should I do to fix this problem so I can insert a null value to a nullable decimal column? I remember in the EF framework I had this problem again but I fixed it by adding "IsOptional" to that property in its config file. But in EF core there is not this option that I can use again.

N.Abdolalizadeh
  • 73
  • 11
  • You are sure it is defined as a decimal in `Financial` type? Try to add a migration and see, if EF Core can detect, that you are trying to change the datatype for the column to nullable decimal. Otherwise, you could write a migration file yourself, altering the column with `AlterColumn` – thesystem Dec 08 '21 at 13:42
  • Yes, I checked the type multiple times. The problem is why EF changed the type at the last moment!? And, I wrote this mapping but it didn't work: `private static decimal? CastFinancialValues(decimal? value) { var nullableValue = (decimal?)null; if (value != null) { nullableValue = (decimal?) value; } return nullableValue; }` Instead of this when I cast the property to decimal, the model inserts to the database with zero value against null. – N.Abdolalizadeh Dec 13 '21 at 06:59
  • 1
    Now I just changed the type of the property in code from (decimal?) to (long?) and it works! (in database it's still decimal(18,6)) How is it possible? Why doesn't work with decimal? – N.Abdolalizadeh Dec 13 '21 at 10:56
  • Thanks for following up/letting me know. That is really strange, but I am glad you figured it out! Maybe someone more knowledgable than me will see this and chip in. – thesystem Dec 14 '21 at 08:25
  • @N.Abdolalizadeh you could add your resolution as an answer if you like. – derekbaker783 Dec 17 '21 at 17:07

0 Answers0