Entity Framework - am I right?

“Conversion overflow” when reading numeric data from MS SQL database using Entity Framework

Reading Time: 3 minutes.

This article explains how to fix a weird situation where your .NET application utilizing Entity Framework or Entity Framework Core simply crashes without any way to recover or catch the Exception. You only get errors somewhere along the lines of “conversion overflow”.

Ah – what a fun little issue!

Problem

So when you’re trying to instantiate an entity with a property of type decimal, you’ll get one of the 2 following exceptions with varying Call Stacks:

Conversion overflow

or:

Arithmetic overflow error converting numeric to data type numeric in SQL Server.

And there’s no way to recover from it, and no way to catch it – what do?

Reason

This seems to be caused by a precision mismatch between the datatypes Decimal in Microsoft SQL Server decimal in Entity Framework (or .NET in general). Essentially, your numeric data types stored in the SQL Server won’t “fit” in your property in .NET.

Oh – and this is such a low-level exception that you can’t catch it. ¯\_(ツ)_/¯

Solution

Essentially, you need to verify that whatever data you store in MS SQL Server also fits in the property in your entity in your .NET code. Probably the easiest way to do this is by using the built-in validation in EF from the System.ComponentModel.DataAnnotations namespace.

You should be fine after making sure the precision & scale for the datatype are sufficient, and additionally adding validation to make sure you can’t end up pushing some incompatible data there anyway.

This would look somewhat like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Data.Entities
{
    public class Price : BaseEntity
    {
        [Required]
        [Column(TypeName = "decimal(30,15)")]
        [Range(0, 999999999999999.999999999999999,
            ErrorMessage = "Value for {0} must be between {1} and {2}.")]
        public decimal Price { get; set; }
    }
}

If, for whatever reason, the above does NOT work, you can also validate on submit (or on ValueChanged), running something like this:

private void ValidateValue(object value)
{
	// Validate also for going over the precision and scale (30 & 15) of our decimal SQL type
	if (Decimal.TryParse(value.ToString(), out var newPrice))
	{
		uint[] bits = (uint[])(object)decimal.GetBits(newPrice);

		decimal mantissa =
			(bits[2] * 4294967296m * 4294967296m) +
			(bits[1] * 4294967296m) +
			bits[0];

		uint scale = (bits[3] >> 16) & 31;

		// Precision: number of times we can divide
		// by 10 before we get to 0
		uint precision = 0;
		if (newPrice != 0m)
		{
			for (decimal tmp = mantissa; tmp >= 1; tmp /= 10)
			{
				precision++;
			}
		}
		else
		{
			// Handle zero differently. It's odd.
			precision = scale + 1;
		}

		// Verify prevision and scale here:
		if (precision >= 30 || scale > 15)
		{
			// Do something
		}
	}
	else
	{
		// Handle this case as well
	}
}

The code above was originally produced by Jon Skeet on Stack Overflow.


Now, if you’re adding a migration and running it against the database, you should get rid of offending data (mind the data loss!), but in case you STILL get the same exception afterwards, you’ll need to query the database for any offending data (usually a value too big for .NET’s decimal – so just start by querying for large values) and modify it.

But finally, with that, you should be good!

References

mm
0 0 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments