efcore.pg: Numeric value does not fit in a System.Decimal
Since build 2.1.0-rtm-ci.305 lazy loading a one-to-many entity collection fails with the following exception:
InvalidOperationException: No row is available
Npgsql.NpgsqlDefaultDataReader.GetFieldValue<T>(int column) in C:\projects\npgsql\src\Npgsql\NpgsqlDefaultDataReader.cs, line 136
Stack
InvalidOperationException: No row is available
Npgsql.NpgsqlDefaultDataReader.GetFieldValue<T>(int column) in C:\projects\npgsql\src\Npgsql\NpgsqlDefaultDataReader.cs
lambda_method(Closure , DbDataReader )
Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+Enumerator.BufferlessMoveNext(DbContext _, bool buffer)
Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation<TState, TResult>(Func<DbContext, TState, TResult> operation, Func<DbContext, TState, ExecutionResult<TResult>> verifySucceeded, TState state)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+Enumerator.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities<TOut, TIn>(IEnumerable<TOut> results, QueryContext queryContext, IList<EntityTrackingInfo> entityTrackingInfos, IList<Func<TIn, object>> entityAccessors)+MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor<T>+EnumeratorExceptionInterceptor.MoveNext()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.Load<TSource>(IQueryable<TSource> source)
Microsoft.EntityFrameworkCore.Internal.EntityFinder<TEntity>.Load(INavigation navigation, InternalEntityEntry entry)
Microsoft.EntityFrameworkCore.Internal.LazyLoader.Load(object entity, string navigationName)
Microsoft.EntityFrameworkCore.Infrastructure.LazyLoaderExtensions.Load<TRelated>(ILazyLoader loader, object entity, ref TRelated navigationField, string navigationName)
[...].Article.get_Statistics() in Article.cs
get => _statistics ?? LazyLoader?.Load(this, ref _statistics);
Build 2.1.0-rtm-ci.304 works fine without any issues. Since dependencies on Npgsql and EFCore are unchanged between the two builds, this seems to have been introduced with changes between the two builds.
I’m using a simple lazy loader configuration in Article.cs:
public virtual ICollection<ArticleStatistic> Statistics
{
get => _statistics ?? LazyLoader?.Load(this, ref _statistics);
set => _statistics = value;
}
private ICollection<ArticleStatistic> _statistics;
#region Lazy Loader
private ILazyLoader LazyLoader { get; set; }
public Article()
{
}
[UsedImplicitly]
public Article(ILazyLoader lazyLoader)
{
LazyLoader = lazyLoader;
}
#endregion
The relationship is not further described with the fluent API, using a standard one-to-many relationship as can be seen in the model snapshot:
modelBuilder.Entity(
"[...].ArticleStatistic", b => {
b.HasOne("[...].Article", "Entity")
.WithMany("Statistics")
.HasForeignKey("EntityId")
.OnDelete(DeleteBehavior.Cascade);
});
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 67 (42 by maintainers)
Yes, the numeric handler was changed (npgsql/npgsql#1959). I’ll take a look on the problem. Thanks for reporting!
I looked at the binary representation and discovered the following; originally values (5, 0 and 0) inserted via Npgsql (roughly two months ago) that lead to the exception:
After modifying the value (and actually setting it back to the same number) the binary representation has changed and no exception occurs:

According to the PostgreSQL source code, the bits different is the display scale value (
numeric.c, line 245ff):So far it seems handling of dscale changed and values written with an earlier version of the Npgsql EF Core provider now lead to the exception present here.
I’ve added a big warning in the 4.0 release notes with links back to the appropriate comments here. Unless I’m mistaken there’s nothing more for us to do on this, so I’m closing the issue.
@roji Yes, because of these lines of code. It uses the string representation of a number to get the group count. I think that 5 with 30 zeroes was inserted as text via Npgsql or psql.
@guylando I opened npgsql/npgsql#1977 for your issue.
@roji Previous versions of Npgsql write only the integer part of a value if the fractional one equals to zero. Therefore, it’s impossible to write 5 with 30 trailing zeroes.
@YohDeadfall @roji Would it also be possible to quietly return a .NET
decimalwith some loss of precision? I am sure there are installations with columns that were created asnumericthat would break after a provider upgrade without either adding a migration or manually modifying the database column to reduce the scale. This would ensure compatibility with existing applications (or alternatively as a configuration optionIgnoreDecimalLossOfPrecisionor similar).