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

Question

Here's what I'm trying to do:

public List<int> GetRolesForAccountByEmail(string email)
{
    var account = db.Accounts.SingleOrDefault(a => a.Email == email);
    if (account == null) return new List<int>();

    return db.AccountRoles.Where(a => a.AccountId == account.AccountId).Select(a => Convert.ToInt32(a.RoleId)).ToList();
}

I had to convert to Int32 because I could not return a List<int?> when the method was to return a List<int>.

Any suggestions on how to solve this simple problem?

Answer 1

Instead of this:

Select(a => Convert.ToInt32(a.RoleId))

Do this:

Select(a => a.RoleId.Value)

The reason is in the error description; when you are doing these queries through IQueryable, the methods being used within the selector have to be something that can be translated into a SQL query or function. In this case Convert.ToInt32() is not such a method. For int fields with null allowed, using the .NET .Value property does work, though.

Note that this would not work if your RoldId is null, however. You'll get an InvalidOperationException. You might want to return a set value instead if the backing field is null:

Select(a => a.RoleId.HasValue ? a.RoleId.Value : int.MinValue)

This will return the value if there is one, and int.MinValue if not.