I am relatively new to LINQ-to-Entities, but often use LINQ-to-SQL.
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 within the SELECT query itself, while LINQ2Entities is not so simple and must have the correct conversion in place before executing the LINQ query. So I get the error message:
The specified method 'System.Decimal ConvertToDecimal (Byte)' for type 'BillYeagerDB.EdmxExtensionMethods' cannot be converted to a LINQ to Entities repository expression.
After a thorough study of this issue, especially in Qaru, I found a link ( LINQ to Entities does not recognize the & # 39; Double Parse method (System.String) & # 39; and this method cannot be translated into a store expression ), but it worked with ObjectContext , and I work with DbContext .
I am also sure that this will work for me, but I think that I am simply designing the extension method incorrectly (which gives me the above error). Note that this particular problem is related to the AvgRating variable in the LINQ query. As soon as I can get this to work, I can make the same fix for any other conversions. Note that AvgRating is defined as a Decimal type, and a.Rating.RatingValue is defined as a Byte type.
If anyone can help me, I would really appreciate it.
Here is my code I am trying to use the following query, which, as I know, will not work (as mentioned earlier) due to a conversion problem.
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; } } } }
The update I needed to do for 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 at 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 that there are no compilation errors during development, and the 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; } } } }