none
Cannot be translated into a LINQ to Entities store expression RRS feed

  • Question

  • I am relatively new to LINQ to Entities, but use LINQ to Sql a lot.

    I am using Visual Studio 2013 with EntityFramework 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 stackoveflow and here with this question, I discovered a link (http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-met) that hopefully would work. I'm sure the example the author gives works, 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**

        using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        using System.Threading.Tasks;
        using System.Data.Entity;
        
        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;
                    }
                }
            }
        }

    Bill Yeager

    Monday, November 25, 2013 1:46 AM

Answers

  • Hi Bill_Yeager,

    >>Fred, The issue was I needed to change my namespace that is referenced in the edmx file from "BillYeagerDB" to "BillYeagerModel".

    Yes, I tested this with a different namespace and it throwed the "... cannot be translated to...". It seems that

    EdmFunction specify the namesapmce and they should be same.

    >>However, even after making that change, I got another error which is a bug in EF 6.01 at this link

    Since I do not use the MappingViewCacheFactory, I do not get error. And hope it will be fixed as soon as possible.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 26, 2013 9:01 AM
    Moderator

All replies

  • Hello,

    For the error “Method cannot be translated into a store expression ... LINQ to Entities”, it is by design,LINQ to Entities requires the whole LINQ query expression to be translated to a server query. Only a few uncorrelated subexpressions (expressions in the query that do not depend on the results from the server) are evaluated on the client before the query is translated. Arbitrary method invocations that do not have a known translation, like GetHomeFeatures() in this case, are not supported. To be more specific, LINQ to Entities only support Parameterless constructors and Initializers.

    One solution is to use the AsEnumerable() like below:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    var result = db.Orders.AsEnumerable().Select(o => new OrderDTO() { OrderID = Convert.ToInt32("1"), OrderCode = o.OrderCode, OrderName = o.OrderName }).ToList();
    
                }
    

    It will be ok for us to use the conversion then.

    Another way is that you have mentioned: use the “DefiningExpression”.

    I made test with the codes provided by you, however, it worked fine.

    Main method:

     using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
                {
                    var result = db.Orders.Select(o => new OrderDTO() { OrderID = EdmxExtensionMethods.ParseInt("1"), OrderCode = o.OrderCode, OrderName = o.OrderName }).ToList();
                }

    Partial class:

    public partial class EdmxExtensionMethods : DbContext
    
        {
    
            /// <summary>
    
            ///     This method exists for use in LINQ queries,
    
            ///     as a stub that will be converted to a SQL CAST statement.
    
            /// </summary>
    
            [EdmFunction("DataBaseFirstDBModel", "ParseInt")]
    
            public static int ParseInt(string stringvalue)
    
            {
    
                return Int32.Parse(stringvalue);
    
            }
    
        }
    The Cast in edmx:
    
    <Function Name="ParseInt" ReturnType="Edm.Int32">
    
              <Parameter Name="stringvalue" Type="Edm.String" />
    
              <DefiningExpression>
    
                cast(stringvalue as Edm.Int32)
    
              </DefiningExpression>
    
            </Function>

    The result:

    I notice that you call the method “ConvertToDecimal”, have a try to change it to:

    AvgRating = s.Average(a => EdmxExtensionMethods. ParseDecimal(a.Rating.RatingValue)),

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 25, 2013 6:09 AM
    Moderator
  • Fred, thanks so much for the input. Seems like you're the only person that can help me on this issue.

    What you have listed as far as the extension method and the function in the edmx file is casting a value from a string to what we want.

    That's fine as long as I would know what the a.Rating.RatingValue and c.RatingId values are ahead of time. You see in your example, you're casting it and setting the value "1" (which is a string).

    UNfortunately, the first value is a Byte and the second value is an Int32.

    So, in my extension methods, I have to set them up as follows. Note that Object.Parse(value) only works if the value is a string, so I can't do it like that.

    With the below code, I'm still getting the same error message and is very frustrating. I don't know what else I need to do in order for it to convert successfully.

    You're my only hope left as I have a requirement to get this in by tomorrow. If you could help out any more, I would appreciate it.

    Here are my query:

    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.ParseDecimal(a.Rating.RatingValue)),
    							NbrOfPeopleRating = s.Distinct().Count(c => EdmxExtensionMethods.ParseBoolean(c.RatingId)),
    							Id = s.Key.Id
    						}).ToListAsync();
    
    					return restaurants;
    				}

    Here are my extesnion methods. Again this class is outside the EDMX file on the root of the project:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Entity;
    
    namespace BillYeagerDB
    {
    	public partial class EdmxExtensionMethods : DbContext
    	{
    		[DbFunctionAttribute("BillYeagerDB", "ParseDecimal")]
    		public static Decimal ParseDecimal(byte bytevalue)
    		{
    			return Convert.ToDecimal(bytevalue);
    		}
    
    		[DbFunctionAttribute("BillYeagerDB", "ParseBoolean")]
    		public static bool ParseBoolean(Int32 intvalue)
    		{
    			return Convert.ToBoolean(intvalue);
    		}
    	}
    }
    

    These are my functions inside the 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>
    		<Function Name="ParseBoolean" ReturnType="Edm.Boolean">
              <Parameter Name="intvalue" Type="Edm.Int32" />
              <DefiningExpression>
                cast(intvalue as Edm.Boolean)
              </DefiningExpression>
            </Function>


    Bill Yeager

    Monday, November 25, 2013 7:38 PM
  • Fred, The issue was I needed to change my namespace that is referenced in the edmx file from "BillYeagerDB" to "BillYeagerModel". However, even after making that change, I got another error which is a bug in EF 6.01 at this link, so I can't do anything about it now. entityframework.codeplex.com/discussions/470366 

    Bill Yeager

    Monday, November 25, 2013 9:51 PM
  • Hi Bill_Yeager,

    >>Fred, The issue was I needed to change my namespace that is referenced in the edmx file from "BillYeagerDB" to "BillYeagerModel".

    Yes, I tested this with a different namespace and it throwed the "... cannot be translated to...". It seems that

    EdmFunction specify the namesapmce and they should be same.

    >>However, even after making that change, I got another error which is a bug in EF 6.01 at this link

    Since I do not use the MappingViewCacheFactory, I do not get error. And hope it will be fixed as soon as possible.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 26, 2013 9:01 AM
    Moderator
  • thank's , but AsEnumerable() make the response very slow , is there any another solution that keep the performance .
    Thursday, April 10, 2014 9:11 AM