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

This post was most recently updated on March 1st, 2023.

3 min read.

This article explains how to fix a weird situation where your .NET application utilizing EF (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! No way to recover, no fallbacks, unusable callstacks.

Oh, speaking of which – what was the error, exactly?

Problem

Table of Contents

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.

And what IS sufficient and proper precision & scale, then?

Apparently, 30 for precision and 15 for scale. Precision means the number of digits in your decimal altogether, and scale means the number of digits on the right hand side of the decimal separator. I can’t remember where I dug these from – the link is probably in the References-section – but it worked for me. It’s probably an overkill for a lot of use cases, so feel free to scale down.

An example of setting the precision and scale in your annotations for your property 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!

Antti K. Koskela

Developer / Speaker / Consultant at Koskila / Precio Fishbone / Norppandalotti Software Co / Alter - Experience Ideas Ltd

Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as Solutions Architect for Precio Fishbone, building delightful Digital Workplaces.

He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.

This is his personal professional (e.g. professional, but definitely personal) blog.

Latest posts by Antti K. Koskela (see all)