locked
casting to decimal is not supported in LINQ to entities queries RRS feed

  • Question

  • User521171331 posted

    I have this LINQ Query to calculate the revenue sum of each employee

    (from E in db.Employees
                           orderby E.EmployeeID
                           select new
                           {
                               E.EmployeeID,
                               E.FirstName,
                               E.LastName,
                               TotalSales =
                           (from O in E.Orders
                            from D in O.Order_Details
                            let LineTotal = (D.UnitPrice * D.Quantity)
                            select LineTotal).Sum()
                           }).ToList()
    

    But, once I factor in the "discount" to become 

    D.UnitPrice * D.Quantity * (1-D.Discount)

    it will hit error "linq to entities does not recognize the method
    system.decimal to decimal method" or "casting to decimal is not supported in LINQ to entities queries" even when I tried to do some conversion and casting.

    This is the data type for these properties:

    • decimal UnitPrice
    • short Quantity
    • float Discount

    Any work around for this?

    Thursday, March 14, 2019 10:31 AM

Answers

  • User1120430333 posted

    ngaisteve1

    I tried

    let LineTotal = ((decimal)D.UnitPrice * (decimal)D.Quantity * (decimal)(1f-D.Discount))
                            

    but it hit error: 

    casting to decimal is not supported in LINQ to entities queries



    So,  I guess you are still using Linq-2-Entity involving EF instead of trying to find a way of using Linq-2-Object. 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/linq-to-entities

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/linq-to-objects

    Until you recognize why you are having the problem is due to you involving EF in the Linq query and doing this decimal thing, then you are not coming around the problem IMO.

    var employeelist = new List<Employee>();  

    employees = (from E in db.Employees select E).ToList();

    //do whatever you have to do, like use Dispose() to close the EF connection.

    1) employeelist is a disconnected collection of objects

    2) It is not a  a list of EF Entities that are connected to EF and still connected to the database.


    var employeelist = new List<Employee>(); // it's whatever the class name for Employees is on the EF virtual object model

    employeelists = (from E in db.Employees select E).ToList(); // get all the objects from the EF query.

    // CLOSE the connection to EF

    //employeelist is no longer connected to EF and now the Linq query is using Linq-2-Object and not Linq-2-Entities. If you need to do the cast to Decimalthen use it.

    var results = (from E in employeelist orderby E.EmployeeID select new { E.EmployeeID, E.FirstName, E.LastName, TotalSales = (from O in E.Orders from D in O.Order_Details let LineTotal = (D.UnitPrice * D.Quantity) select LineTotal).Sum() }).ToList()



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 15, 2019 4:14 AM

All replies

  • User1120430333 posted

    What's really happening is that the EF engine cannot make the T-SQL to submit to the Database engine for execution based on the formulated Linq query.

    You can query for the data, not do this calculation, disconnect from the database so you have a disconnected result.

    You can then use a Linq query that is using Linq-2-Object, becuase you are querying a collection in memory that is disconnected from EF that is using Linq-2-Entities causing T-SQL to be generated and submitted to the database engine for execution.

    Thursday, March 14, 2019 11:22 AM
  • User753101303 posted

    Hi,

    Try maybe 1f-D.Discount ? (note the f suffix after 1 to tell it is a literal float value).

    I would use a decimal as well for the discount (float is when you want to favor range - ie being able to use very high or very small values - over precision) which might explain why I never noticed that.

    Thursday, March 14, 2019 12:31 PM
  • User521171331 posted

    I tried

    let LineTotal = ((decimal)D.UnitPrice * (decimal)D.Quantity * (decimal)(1f-D.Discount))
                            

    but it hit error: 

    casting to decimal is not supported in LINQ to entities queries



    Friday, March 15, 2019 1:55 AM
  • User1120430333 posted

    ngaisteve1

    I tried

    let LineTotal = ((decimal)D.UnitPrice * (decimal)D.Quantity * (decimal)(1f-D.Discount))
                            

    but it hit error: 

    casting to decimal is not supported in LINQ to entities queries



    So,  I guess you are still using Linq-2-Entity involving EF instead of trying to find a way of using Linq-2-Object. 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/linq-to-entities

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/linq-to-objects

    Until you recognize why you are having the problem is due to you involving EF in the Linq query and doing this decimal thing, then you are not coming around the problem IMO.

    var employeelist = new List<Employee>();  

    employees = (from E in db.Employees select E).ToList();

    //do whatever you have to do, like use Dispose() to close the EF connection.

    1) employeelist is a disconnected collection of objects

    2) It is not a  a list of EF Entities that are connected to EF and still connected to the database.


    var employeelist = new List<Employee>(); // it's whatever the class name for Employees is on the EF virtual object model

    employeelists = (from E in db.Employees select E).ToList(); // get all the objects from the EF query.

    // CLOSE the connection to EF

    //employeelist is no longer connected to EF and now the Linq query is using Linq-2-Object and not Linq-2-Entities. If you need to do the cast to Decimalthen use it.

    var results = (from E in employeelist orderby E.EmployeeID select new { E.EmployeeID, E.FirstName, E.LastName, TotalSales = (from O in E.Orders from D in O.Order_Details let LineTotal = (D.UnitPrice * D.Quantity) select LineTotal).Sum() }).ToList()



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 15, 2019 4:14 AM
  • User521171331 posted

    Thanks DA924. It works.:)

    Sunday, March 17, 2019 8:05 PM