OverflowException with dotConnection PostgreSQL

Question

I've create model from databse using dotConnect for PostgreSQL, proffesional edition. Everything works fine exception one of the entity. Some of properties were generated as decimal. Sample property in my Entity:

public global::System.Nullable<decimal> CollectionToPay
{
    get
    {
        global::System.Nullable<decimal> value = _CollectionToPay;
        OnGetCollectionToPay(ref value);
        return value;
    }
    set
    {
        if (_CollectionToPay != value)
        {
          OnCollectionToPayChanging(ref value);
          ReportPropertyChanging("CollectionToPay");
          _CollectionToPay = StructuralObject.SetValidValue(value);
          ReportPropertyChanged("CollectionToPay");
          OnCollectionToPayChanged();
      }
    }
}

private global::System.Nullable<decimal> _CollectionToPay;

When I try get collection of entities I am getting an error:

A first chance exception of type 'System.OverflowException' occurred in Devart.Data.PostgreSql.dll

Additional information: Value was either too large or too small for a Decimal.

I don't know even which row casues problem. I'm fetching about 400 rows. Is there any fast way to determine problematic row? Table has about 170 columns, about 20 are of decimal type. I've fetched rows in pgAdmin, but non of values looks like too big/small. What can I do? I suppuse I'd change decimal to double, but first I need to find problematic row(s).

Answer 1

C#'s Decimal is a bounded type: it's a 128-bit fixed size value. Presumably it's an IEEE 574:2008 decimal128 or equivalent.

This is not equivalent to PostgreSQL's numeric type, which is what I presume you are mapping it to. (It'd be nice if you showed this). numeric is arbitrary precision and scale binary-coded decimal. Its practical limit is around 1GB, but that's so huge that it's an unimaginable number.

You can't store any possibly numeric in C#'s Decimal. Presumably your application contains values that cannot be stored in Decimal.

You may need to find a binary coded decimal datatype implementation in C# or adjust your data to eliminate the out-of-range values.

It's OK to map numeric to Decimal if you add a CHECK constraint to the numeric columns to restrict their value range. Personally I recommend doing this using a DOMAIN type.