LINQ to Entities does not recognize the method 'Int32 Int32(System.String)' method, and this method cannot be translated into a store expression

Question

I am trying to Query Database Context using Linq to Entities and I am getting this error:

LINQ to Entities does not recognize the method 'Int32 Int32(System.String)' method, and this method cannot be translated into a store expression.`

Code:

public IEnumerable<CourseNames> GetCourseName()
{
   var course = from o in entities.UniversityCourses
                select new CourseNames
                {
                    CourseID = Convert.ToInt32(o.CourseID),
                    CourseName = o.CourseName,
                 };
   return course.ToList();
}

I tried like this after seeing this

public IEnumerable<CourseNames> GetCourseName()
{
    var temp = Convert.ToInt32(o.CourseID);
    var course = from o in entities.UniversityCourses
                 select new CourseNames
                 {
                     CourseID = temp,
                     CourseName = o.CourseName,
                 };
    return course.ToList();
}

But it throws an error:

"The name 'o' does not exist in the current context"

This is my code for the class GetCourseName

namespace IronwoodWeb
{
    public class CourseNames
    {
        public int CourseID { get; set; }
        public string CourseName { get; set; }
    }
}

Ibis

Try int.Parse(o.CourseID); the error is because EF didn't know how to write Convert.ToInt32 in terms of SQL, but I think it should understand int.Parse .. (best would be to use the appropriate column type and avoid having to deal with this conversion :) – Orlina

@pst using int.parse throws the same error "The name 'o' does not exist in the current context" and I can't use the same column type, it also shows error " Cannot implicitly convert type....." Anyway thanks for trying to help me! – Ibis

Another way to get around this issue (where EF cannot translate some expression into SQL) is to run a simpler query first and at the end of it force it to populate via .ToList(). Once you have it in memory, you are no longer bound by having to convert your expressions to SQL. – Kugler

@Ibis No, that's a different issue (o is out of scope). Go back the first query, but replace Convert.ToInt32 with int.Parse, hmm nevermind, it is the other way. Seems to be IEnumerable vs. IQueryable difference? – Orlina

@pst No worries, the answer lazyberezovsky posted works, anyways thanks for trying to help me! – Ibis

@Kugler solved the problem, anyways thanks for trying to help me! – Ibis

Answer 1

public IEnumerable<CourseNames> GetCourseName()
{
    var courses = from o in entities.UniversityCourses
                  select new { o.CourseID, o.CourseName };

    return courses.ToList() // now we have in-memory query
                  .Select(c => new CourseNames()
                  {
                     CourseID = Convert.ToInt32(c.CourseID), // OK
                     CourseName = c.CourseName
                  });
}

Sterculiaceous

Is there a way to get str->int conversion without needing to force in-memory? – Orlina

@lazyberezovsky Thank you so much! It worked, I was trying to figure this out for more than two hours! Thanks again! :) – Ibis

@pst as far as I know only there is no built-in possibility to do that conversion on server side – Sterculiaceous

There is, by using an explicit conversion (see answers below) – Latton

Should be noted that it's better to use AsEnumerable instead to avoid creating an unneeded list in memory. – Ewold

Answer 2

If you dont want to materialize the query (retrieve the data) you can use cast (i.e. (int) o.CourseId). Is converted to SQL CAST AS statement.

Wrongful

Answer 3

You could also bring back the value as a string (as it is apparently stored) and then Convert it after.

The error on 'o' being out of the context is that you are only declaring o in the Linq query and it can only be referenced in that scope.

Loupe

the answer lazyberezovsky posted works, anyways thanks for trying to help me! – Ibis

Answer 4

Explicit conversion is simple and works: (int)o.CourseID

var course = from o in entities.UniversityCourses
                select new CourseNames
                {
                    CourseID = (int)o.CourseID,
                    CourseName = o.CourseName,
                 };

Pfister

That won't even compile: CS0030: Cannot convert type 'string' to 'int' – Stylography

Cast to object first, then int, then it will compile. However, then Linq to Entities still will not recognize it. Linq to SQL will, however. – Outspoken