CODEWITHSUNDEEP
c#sqlentity-framework
Jason Foglia
Jason Foglia

Reputation: 2531

The specified cast from a materialized 'System.Int32' type to the 'System.String' type is not valid

I'm using Entity Framework 6.0.2; I was using a previous version before I started getting this error.

Basically, I set my models with "string" properties and execute a query or stored procedure and dump the results out as strings, this has always worked.

Any ideas how to fix? Perhaps a "Convention" I could use for the conversion?

Ex: 

SQL:
select ROW_NUMBER() OVER (ORDER BY (select Null)) AS Id, '0', 'Test0', 'some kind of data', 'Test1', 'possibly a datetime type', 'Test2'

C#:
public class SomeModel
{
    public int Id { get; set; }
    public string Test0 { get; set; }
    public string Test1 { get; set; }
    public string Test2 { get; set; }
}

Invalid Operation Exception:
var records = dbContext.Database.SqlQuery<SomeModel>("select ROW_NUMBER() OVER (ORDER BY (select Null)) AS Id, '0', 'Test0', 'some kind of data', 'Test1', 'possibly a datetime type', 'Test2'").ToList();

.

Upvotes: 3

Views: 14092

Answers (1)

Jason Foglia
Jason Foglia

Reputation: 2531

Even though this is a year old with no answer. I have my own answer to provide based on a comment @Crono said above.

Basically, The issue was datatypes. In order to bind SQL result set to a model Entity Framework has to translate the datatypes from SQL to .NET.

Well, in .NET I wanted everything back as a string, because the propose of the data being retrieved is not to manipulate it at all but display it out to a webpage or to a file.

The query needed to be updated to cast all result columns to a varchar. Which @Crono had kindly explained in is comment above. A view wouldn't work for me because the data/results are unknown.

EDIT 7/2/2016

Cast your values!

select Cast(ROW_NUMBER() OVER (ORDER BY (select Null)) AS Id as int), Cast('0' as varchar(255)), Cast('Test0' as varchar(255)), Cast('some kind of data' as varchar(255)), Cast('Test1' as varchar(255)), Cast('possibly a datetime type' as varchar(255)), Cast('Test2' as varchar(255))

Upvotes: 4

Related Questions