How to compare money data with Npgsql EF Core?

1

I am working with a database that has a table with a 'money" column. I am trying to add filter and search logic that works with that table, but I get an exception when I try to compare data with that column. Below is my function:

private IEnumerable<AccountTransaction> GetPaymentData(Guid AccountId, IFinancialCriterion Criterion)
{
    IQueryable<BusinessTransactionCredit> payments = this.context.AccountPayments
                                                         .Include(X => X.Notes)
                                                         .Where(X => X.AccountId == AccountId);
    
    if (!ReferenceEquals(Criterion.EarliestTransactionDate, null))
    {
        payments = payments.Where(X => X.CreatedOn >= Criterion.EarliestTransactionDate);
    }
    
    if (!ReferenceEquals(Criterion.LatestTransactionDate, null))
    {
        payments = payments.Where(X => X.CreatedOn <= Criterion.EarliestTransactionDate);
    }
    
    if (!ReferenceEquals(Criterion.MinTransactionAmount, null))
    {
        payments = payments.Where(X => X.Amount >= Math.Abs(Criterion.MinTransactionAmount ?? 0));
    }
    
    if (!ReferenceEquals(Criterion.MaxTransactionAmount, null))
    {
        payments = payments.Where(X => X.Amount >= Math.Abs(Criterion.MaxTransactionAmount ?? 0));
    }
    
    return payments.Select(X => new AccountTransaction()
    {
        Id = X.Id,
        Amount = X.Amount,
        TransactionDate = X.CreatedOn
    }).ToArray();
}

Any time I specify a value that evaluates the comparison with the 'Amount" property, I get the following exception:

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: money >= numeric
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
.
.
.
Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: operator does not exist: money >= numeric
    Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
    Position: 154
    File: d:\pginstaller.auto\postgres.windows-x64\src\backend\parser\parse_oper.c
    Line: 731
    Routine: op_error

UPDATED: Here are snippets of the BusinessTransactionCredit. The "Amount" property is defined as "money" using the Fluent API

public class BusinessTransactionCredit 
{
    public Guid Id
    {
        get;
        set;
    }

    public Guid AccountId
    {
        get;
        set;
    }

    public Decimal Amount
    {
        get;
        set;
    }

    [Required]
    [MaxLength(100)]
    public String ReceiptData
    {
        get;
        set;
    }
    
    public DateTime CreatedOn
    {
        get;
        set;
    }

    [Required]
    [MaxLength(50)]
    public String CreatedBy
    {
        get;
        set;
    }

    public DateTime? LastModifiedOn
    {
        get;
        set;
    }

    [MaxLength(50)]
    public String LastModifiedBy
    {
        get;
        set;
    }

    public ICollection<PaymentNote> Notes
    {
        get;
        set;
    }
}

and IFinancialCriterion

public interface IFinancialCriterion
{
    Int32 PageNumber { get; }
    
    Int32 PageSize { get; }
    
    String SearchQuery { get; }
    
    String TransactionType{ get; }
    
    DateTime? EarliestTransactionDate{ get; }

    DateTime? LatestTransactionDate{ get; }

    Decimal? MinTransactionAmount{ get; }

    Decimal? MaxTransactionAmount{ get; }
}
c#
entity-framework-core
npgsql
asked on Stack Overflow Aug 11, 2020 by BizerbaDev • edited Aug 11, 2020 by BizerbaDev

2 Answers

0

You must try 'double precision' property type because money property not matched .net core properties.

Example:

  • bigint --->Int64
  • boolean --->Boolean
  • character --->String
  • double precision --->Double
  • uuid --->Guid
answered on Stack Overflow Aug 11, 2020 by childofthealgorithm
0

This is a bug in the Npgsql EF Core provider, I've opened https://github.com/npgsql/efcore.pg/issues/1467 to track it. A fix should get merged for the next patch release.

answered on Stack Overflow Aug 13, 2020 by Shay Rojansky

User contributions licensed under CC BY-SA 3.0