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

13,665

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))
Share:
13,665
Jason Foglia
Author by

Jason Foglia

Web Application Developer / Software Engineer

Updated on June 27, 2022

Comments

  • Jason Foglia
    Jason Foglia almost 2 years

    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();    
    

    .