The specified cast from a materialized 'System.Int32' type to the 'System.Double' type is not valid
C#asp.net MvcLinqC# Problem Overview
When executing the following query, I get the error:
> The specified cast from a materialized 'System.Int32' type to the > 'System.Double' type is not valid.
var data = ctx.tblTO
.Where(m => m.Id == Id)
.GroupBy(m => m.EmployeeId)
.Select(m => new
{
workDay = m.Sum(k => k.WorkDay),
onDutyDay = m.Sum(k => k.OnDutyDay),
holiDay = m.Sum(k => k.Holiday)
})
.FirstOrDefault();
The datatype of WorkDay
, OnDutyDay
and Holiday
is double
. There is no Int32
here, so why do I get this error?
How can I solve this error?
public class TO
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public int EmployeeId { get; set; }
public double WorkDay { get; set; }
public double OnDutyDay { get; set; }
public double Holiday { get; set; }
}
C# Solutions
Solution 1 - C#
I think the data type of the columns inside your database table is Int32
but your data model has double
. You need to change the data types of your data models to int
. By materialized
it means the type it got when it ran the query on the database.
Solution 2 - C#
First make sure your model and table column data type are same.
Try to change your query to this.
var data = ctx.tblTO
.Where(m => m.Id == Id)
.GroupBy(m => m.EmployeeId)
.Select(m => new
{
workDay = m.Select(k => k.WorkDay).DefaultIfEmpty(0).Sum(),
onDutyDay = m.Select(k => k.OnDutyDay).DefaultIfEmpty(0).Sum(),
holiDay = m.Select(k => k.Holiday).DefaultIfEmpty(0).Sum()
})
.FirstOrDefault();
If the collection is empty, it will return one element with the value of 0 and then the sum will be applied.
Solution 3 - C#
For me I was calling a stored procedure
from EF 6 of which I wrote a poco, but I didn't write the sproc. I needed to look at all the tables that had field returning data types. and then change my Poco from a int to boolean.
public Boolean Active { get; set; }
Solution 4 - C#
Apart from the other answers, I got another scenario where I faced the same error. Which is, In our stored procedure a bigint
was being casted to a decimal and on C#
the same thing was being casted to int64. See:
SELECT Cast(@Request_Id as decimal) AS RetValue
What I did was changed the query to this:
SELECT @Request_Id AS RetValue
So, I removed the extra casting and the issue was gone. Somehow, it was maybe raising some sort of arithmetic exceptions but not quite sure yet. Will Update soon if I get to know the exact reason.
PS: My @Request_Id
variable is of type bigint
.
DECLARE @Request_Id bigint = 0