LINQ to Entities does not recognize the method 'Int32 ToInt32 Converting String to Int

Question

I have a LINQ statement that i want to do a sum of some a single column that is of type String.

I am trying to do the following statement where i am Converting.ToInt32. When i run this i am getting the following error.

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

LINQ statement

    var CoreData = new
    {
    Shoots =
   (from item in coresdb.Productions
    where item.Date >= StartShift && item.Date <= EndDate
   select item).Sum(x => Convert.ToInt32(x.ShootCount)
   )
};

I have tried a number of different data types to convert too and get a similar error.

Answer 1

You can't translate ToInt32 to T-SQL. One way to make it work is to run it in memory based on the list retrieved from the database like this

var CoreData = coresdb.Productions
    .Where(item => item.Date >= StartShift && item.Date <= EndDate)
    .ToList()  // get the data into memory first.
    .Sum(x => Convert.ToInt32(x.ShootCount));

Updated: Moved the ToList() after the where clause.

Answer 2

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