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)

Most upvoted comments

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: binary numeric existing After modifying the value (and actually setting it back to the same number) the binary representation has changed and no exception occurs: binary numeric updated

According to the PostgreSQL source code, the bits different is the display scale value (numeric.c, line 245ff):

dscale, or display scale, is the nominal precision expressed as number of digits after the decimal point (it must always be >= 0 at present). dscale may be more than the number of physically stored fractional digits, implying that we have suppressed storage of significant trailing zeroes. It should never be less than the number of stored digits, since that would imply hiding digits that are present. NOTE that dscale is always expressed in decimal digits, and so it may correspond to a fractional number of base-NBASE digits — divide by DEC_DIGITS to convert to NBASE digits.

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 decimal with some loss of precision? I am sure there are installations with columns that were created as numeric that 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 option IgnoreDecimalLossOfPrecision or similar).