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; }
}
You must try 'double precision' property type because money property not matched .net core properties.
Example:
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.
User contributions licensed under CC BY-SA 3.0