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