Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
528 views
in Technique[技术] by (71.8m points)

c# - Cannot be translated into a LINQ to Entities store expression

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;
            }
        }
    }
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...