Cannot be translated into a LINQ to Entities store expression

Question

I am relatively new to LINQ-to-Entities, but use LINQ-to-SQL a lot.

I am using Visual Studio 2013 with Entity Framework 6 and MVC 5.

The biggest difference between the two is that Linq2SQL has the ability to perform conversions inside the SELECT query itself whereas LINQ2Entities is not as forgiving and must have the right conversion in place before executing the LINQ query. Therefore, I am getting the error:

The specified method 'System.Decimal ConvertToDecimal(Byte)' on the type 'BillYeagerDB.EdmxExtensionMethods' cannot be translated into a LINQ to Entities store expression.

After doing much research, especially on Stack Overflow with this question, I discovered a link (LINQ to Entities does not recognize the method 'Double Parse(System.String)' method, and this method cannot be translated into a store expression), but he was working with the ObjectContext and I am working with the DbContext.

I'm also sure it will work for me, but I think I'm just designing the extension method incorrectly (which gives me the above error). Note that this specific issue is with the AvgRating variable in the LINQ query. Once I can get this to work, I can do the same type of fixing for any other conversions. Note that AvgRating is defined as type Decimal and a.Rating.RatingValue is defined as type Byte.

If somebody can straighten me out on this, I would greatly appreciate it.

Here is my code. I'm trying to use the following query, which I know won't work (as mentioned before) because of the conversion issue.

Original LINQ Query:

namespace BillYeagerDB
{
    public class BillYeagerDB
    {
        public async Task<List<RestaurantList>> GetRestaurantListAsync()
        {
            try
            {
                using (BillYeagerEntities DbContext = new BillYeagerEntities())
                {
                    DbContext.Database.Connection.Open();

                    var restaurants = await DbContext.Restaurants.GroupBy(g => g).Select(s =>
                        new RestaurantList()
                        {
                            Name = s.Key.Name,
                            City = s.Key.City,
                            Phone = s.Key.Phone,
                            AvgRating = s.Average(a => Convert.ToDecimal(a.Rating.RatingValue)),
                            NbrOfPeopleRating = s.Distinct().Count(c => Convert.ToBoolean(c.RatingId)),
                            Id = s.Key.Id
                        }).ToListAsync();

                    return restaurants;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

Update I needed to do to my EDMX file

<edmx:ConceptualModels>
      <Schema Namespace="BillYeagerModel" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
        <Function Name="ParseDecimal" ReturnType="Edm.Decimal"> 
            <Parameter Name="bytevalue" Type="Edm.Byte" /> 
            <DefiningExpression> 
                cast(bytevalue as Edm.Decimal)
            </DefiningExpression> 
        </Function>

C# extension method which is a class on the root of my project - not inside my EDMX

namespace BillYeagerDB
{
    public partial class EdmxExtensionMethods : DbContext
    {
        [DbFunctionAttribute("BillYeagerDB", "ParseDecimal")]
        public static Decimal ParseDecimal(byte bytevalue)
        {
            return Convert.ToDecimal(bytevalue);
        }
    }
}

Updated Linq query - note no design time compile errors and project compiles successfully

namespace BillYeagerDB
{
    public class BillYeagerDB
    {
        public async Task<List<RestaurantList>> GetRestaurantListAsync()
        {
            try
            {
                using (BillYeagerEntities DbContext = new BillYeagerEntities())
                {
                    DbContext.Database.Connection.Open();

                    var restaurants = await DbContext.Restaurants.GroupBy(g => g).Select(s =>
                        new RestaurantList()
                        {
                            Name = s.Key.Name,
                            City = s.Key.City,
                            Phone = s.Key.Phone,
                            AvgRating = s.Average(a => EdmxExtensionMethods.ConvertToDecimal(a.Rating.RatingValue)),
                            NbrOfPeopleRating = s.Distinct().Count(c => Convert.ToBoolean(c.RatingId)),
                            Id = s.Key.Id
                        }).ToListAsync();

                    return restaurants;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

Answer 1

I know I'm a little late to the party but for anyone doing a Google search:

I had this problem and it turns out the class the DbFunctionAttribute is on has to have the same namespace as the edmx schema.

So in this case either change the edmx schema namespace to BillYeagerDB,
or change the EdmxExtensionMethods namespace to BillYeagerModel