I am trying to solve an issue I have with pulling large ints (22+ digits) into ASP.Net with Entity Framework Core from a MySQL database.
EF Core does not support BigInteger
and the suggestions I received where to use decimal
instead. However, when using decimal
types on my entities, I always receive the following exception when trying to select from the DB:
System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Decimal'
In the Database the columns are INT(25)
and in my models the type is decimal
, here is an example model:
[Table("alliance_reputation_rankings")]
public class AllianceReputationRank
{
[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
[Column("date")]
public DateTime Date { get; set; }
[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
[Column("world")]
public int World { get; set; }
[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
[Column("alliance")]
public string Alliance { get; set; }
[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
[Column("rank")]
public int Rank { get; set; }
[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
[Column("reputation")]
public decimal Reputation { get; set; }
[JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
[Key]
[Column("entry_id")]
public int EntryId { get; set; }
}
I cannot select the Reputation
property using EF Core. Even if I try and use (decimal)
before the property to cast it:
Rough example of a select:
_context.AllianceReputationRankings
.Where(p => p.Date == rank.Date && p.Alliance== rank.Alliance && p.World == rank.World)
.Select(pl => new AllianceReputationRank
{
Date = pl.Date,
World = pl.World,
Alliance = pl.Alliance,
Reputation = (decimal)pl.Reputation
}
).FirstOrDefault();
How can I use decimals in my models to bring in large ints from the database? If I cannot use decimals, how can I use large numbers with EF Core?
What I did that seemed to work is to multiply by 1m
:
context.AllianceReputationRankings
.Where(p => p.Date == rank.Date && p.Alliance== rank.Alliance && p.World == rank.World)
.Select(pl => new AllianceReputationRank
{
Date = pl.Date,
World = pl.World,
Alliance = pl.Alliance,
Reputation = pl.Reputation * 1m
}
).FirstOrDefault();
This seems to allow decimal operations while not taking performance to the floor.