Arithmetic overflow error converting numeric to data type numeric. EF Core

entity-framework-core sql-server

Question

I am trying to figure out which column, or columns, is tripping the error below. Something changed about the incoming data, fed by a 3rd party service and it is now causing failures when I try to save it to SQL.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

A very simple flow:

  • Read 3rd party API as JSON
  • Use newtonsoft to convert directly to EF data model class
  • Add record(s) to DB then save.

Data structure is defined as:

[JsonObject(MemberSerialization.OptIn)]
public class RatDbAttributes
{
    [JsonProperty]
    [StringLength(50)]
    public string block_chain { get; set; } // varchar(50)
    [JsonProperty]
    [StringLength(50)]
    public string block_reduction { get; set; } // varchar(50)
    [JsonProperty]
    [StringLength(50)]
    public string block_reward { get; set; } // varchar(50)
    [JsonProperty]
    public double block_time { get; set; } // decimal(28,6)
    [JsonProperty]
    [StringLength(100)]
    public string consensus_method { get; set; } // varchar(100)
    [JsonProperty]
    public decimal decimals { get; set; } // decimal(28,6)
    [JsonProperty]
    [StringLength(50)]
    public string difficulty_retarget { get; set; } // varchar(50)
    [JsonProperty]
    [StringLength(200)]
    public string genesis_address { get; set; } // varchar(200)
    [JsonProperty]
    [StringLength(100)]
    public string hash_algorithm { get; set; } // varchar(100)
    [JsonProperty]
    [StringLength(50)]
    public string mineable { get; set; } // varchar(50)
    [JsonProperty]
    public long p2p_port { get; set; } // bigint
    [JsonProperty]
    public long rpc_port { get; set; } // bigint
    [JsonProperty]
    [StringLength(200)]
    public string token_role { get; set; } // varchar(200)
    [JsonProperty]
    public decimal @float { get; set; } // decimal(28,6)
    [JsonProperty]
    public decimal minted { get; set; } // decimal(28,6)
    [JsonProperty]
    public decimal total_supply { get; set; } // decimal(28,6)
    [JsonProperty]
    public decimal max_supply { get; set; } // decimal(28,6)
    [JsonProperty]
    [StringLength(133)]
    public string wallet { get; set; } // varchar(133)
    [JsonProperty]
    [NotMapped]
    public double genesis_timestamp { get; set; } // see below

    [JsonIgnore]
    public DateTime Genesis_TimeStamp { get { return genesis_timestamp.ToDateTime(); } set { genesis_timestamp = value.ToEpoch(); } }


    // Foregin Key Relationship (1-to-1) and Primary Key
    [JsonIgnore]
    public long TokenMasterId { get; set; }
    [JsonIgnore]
    [ForeignKey("TokenMasterId")]
    public RatDbTokenMaster TokenMaster { get; set; } //foreign key to Parent
}

I've double checked the genesis_timestamp and that is not the problem (converting double to datetime).

Sample incoming failing JSON:

{"block_chain":""
,"block_reduction":""
,"block_reward":"0"
,"block_time":0.0
,"consensus_method":""
,"decimals":0.0
,"difficulty_retarget":""
,"genesis_address":""
,"hash_algorithm":""
,"mineable":"False"
,"p2p_port":0
,"rpc_port":0
,"token_role":""
,"float":0.0
,"minted":0.0
,"total_supply":0.0
,"max_supply":0.0
,"wallet":""
,"genesis_timestamp":0.0
}
1
2
5/6/2019 5:48:02 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

I created a fail-over iterative save for when a batch fails. Since I made my database connection "global" for this class I was inadvertantly running into a problem. I would add the recordset and try to save it (error pops), then try to iterate smaller and smaller batches to discover the offending record(s). The problem was I did not remove the offending recordset before iterating. Therefore, every iteration carried the error condition in the DB connection!

    internal void IterateSave<TModel>(List<TModel> items) where TModel : class
    {
        using (LogContext.PushProperty("Data: Class", nameof(RatBaseCommandHandler)))
        using (LogContext.PushProperty("Data: Method", nameof(IterateSave)))
        using (LogContext.PushProperty("Data: Model", nameof(items)))
        {
            int max = items.Count;
            int skip = 0;
            int take = (max > 20) ? (max / 5) : 1;
            int lastTake = take;
            List<TModel> subItems = new List<TModel>();

            while (skip <= max)
            {
                try
                {
                    subItems = items.Skip(skip).Take(take).ToList();
                    Log.Verbose("Working {Max} | {Take} | {Skip}", max, take, skip);

                    skip += take;
                    _db.Set<TModel>().AddRange(subItems);
                    _db.SaveChanges();
                }
                catch (Exception ex)
                {
/***** Was not removing the faulty record/recordset! *****/
                    _db.Set<TModel>().RemoveRange(subItems);
/***** Was not removing the faulty record/recordset! *****/

                    if (take == 1 && skip < max)
                    {
                        Log.Error(ex, "Error saving specific record in this data batch! {GuiltyRecord}", JsonConvert.SerializeObject(subItems));
                        if (skip >= max - 1)
                        {
                            depth--;
                            return;
                        }
                    }
                    else if (take > 1)
                    {
                        Log.Warning("Something is wrong saving this data batch! {RecordCount}  Running a smaller batch to isolate.", take);
                        IterateSave(subItems);
                    }
                }
            }
        }
    }

With those 2 lines added (commented section in catch) the error literally popped right out!

Error saving specific record in this data batch! "[{\"block_chain\":\"Ethereum\",\"block_reduction\":\"\",\"block_reward\":\"0\",\"block_time\":0.0,\"consensus_method\":\"\",\"decimals\":18.0,\"difficulty_retarget\":\"\",\"genesis_address\":\"0x3520ba6a529b2504a28eebda47d255db73966694\",\"hash_algorithm\":\"\",\"mineable\":\"False\",\"p2p_port\":0,\"rpc_port\":0,\"token_role\":\"\",\"float\":0.0,\"minted\":60000000000000000000000000.0,\"total_supply\":60000000000000000000000000.0,\"max_supply\":60000000000000000000000000.0,\"wallet\":\"\",\"genesis_timestamp\":0.0}]" Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

While C# can handle 60000000000000000000000000.0 in a DECIMAL data type, our SQL is defined at DECIMAL(28,6). Because of the 6 digit precision that only leaves space for a 10^22 value.

(It appears that SQL can now handle DECIMAL(38,6). Time to play with column definitions without losing production data.)

0
5/7/2019 2:14:38 PM

Related Questions

Entity Framework .Net core FindAsync Throw System.OverflowException: Arithmetic operation resulted in an overflow

I fixed it. I used wrong connection string. I have to put the port number of SQL database in the connection string....the wrong one:...Server=serverAddress;Database=dbName;User Id=dbUser;Password=dbPassword; ...the right one:...Server=serverAddres,portNumber;Database=dbName;User Id=dbUser;Password=dbPassword; ...Default MSSQL port number is 1433...
.net-core asp.net-core-webapi entity-framework-core
asked by Andrianto Dwi Laksono

Defining precision for numeric data type of Postgres DB in Entity Framework Core

Decimal not support to high precision so you have Used Double Other Wise Price Value Convert to Decimal Value ...Example:...1.stored value price=1234.12345678901234567890...tabledetails.Select(c =>(decimal) c.value).ToList(); ...Get Vlaue is 1234.12345678901...2.Other wise The before stored the Price Value using decimal then using below format...
c# entity-framework-core linq postgresql
asked by kari kalan

Eager Loading "No coercion operator is defined between types" after .NET Core/EF Core 2.0 Upgrade

Include... isn't useful in this situation as you are returning a projection and the ...Include...s are ignored (read about ignored includes ...here...)....If by "intended results" you mean you want ...record.PaperRecord.Paper... to have a value, simply include it in your projection; EF will wire up the relationship....new { PaperRecord = _.Pape...
.net-core c# eager-loading entity-framework-core linq
asked by Moho

EF Core SingleOrDefaultAsync compile time return type error

How to solve your compilation error :...I change it to the following code (i.e. no OrDefault) I get no issues. ...What am I doing wrong?...You should try the following to force the compiler to convert the return Type values ...and... the possible default (null) type to ...Administrator... ctx.Users.SingleOrDefaultAsync(u => u.Guid == guid) as Admi...
async-await c# entity-framework-core linq
asked by Pac0

EF Core Migration error: "Unable to create an object of type 'ApplicationContext'"

This will also happen if you have multiple start-up projects - when migrating, just select one (in VS right click Solution and Set StartUp Projects...)
asp.net-core entity-framework-core
asked by john hunter

Change data type in Model while preserving data (Entity Framework Core)

When the problem is complex, I prefer divide that....For this migration, I will make 2 update :...Create and populate Translations table...Populate PageDataSections table...1) Create and populate Translations table...See the temporary column Translations.PageDataSectionId...public partial class Update1 : Migration { protected override void Up(M...
c# entity-framework-core sql
asked by Orwel

Add-Migration error with EF Core Owned type

In the mean time, you can use the fluent method to configure this....Remove the ...[Owned]... attributes from the data model...Configure the relationship by overriding the ...OnModelCreating... method on your ...ApplicationDbContext...protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); m...
c# entity-framework-core
asked by Todd Skelton

EF Core - error with duplicated key while adding data with unique index

Thanks to @Zer0's and @Steve Py's suggestions I came up with the following solution:...void Insert(IEnumerable<Transaction> data) { using(var context = dbContextFactory.CreateDbContext(new string[0])) { List<Stock> stocks = data.Select(s => s.RelatedStock).Distinct(new StockComparer()).ToList(); ...
c# ef-code-first ef-code-first-mapping entity-framework entity-framework-core
asked by l.sypniewski

Why there is no EF Core OfType(Type type) method?

The generic method ...OfType<T>... is a standard LINQ method which EF Core supports. ...There is no standard ...OfType(Type)... method and EF Core designers didn't find a reason of implementing such custom EF Core specific extension....But it's not hard to be implemented. TPH (and other future database inheritance strategies) are supported in LINQ...
.net-core entity-framework-core
asked by Ivan Stoev

Entity Framework Core traverse big blob data without memory overflow, best practice

In general when you create a LINQ filter on an Entity it is like writing a SQL statement in code form. It returns an ...IQueryable... that has not actually executed against the database. When you iterate over the ...IQueryable... with a ...foreach... or call ...ToList()... then the sql is executed and all the results are returned, and stored in mem...
blob c# entity-framework-core out-of-memory traversal
asked by ste-fu

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow