Casting to Decimal is not supported in LINQ to Entities queries

Question

I have a database table Transaction (transactionID, LocalAmount...). where datatype for Localamount property is float. ON the UI I am trying to return a SUM of column (Localamount) in one row on a button click event.

I have used decimal instead of float

However I am getting an error on the code where I am casting to decimal

System.NotSupportedException was unhandled by user code
Message=Casting to Decimal is not supported in LINQ to Entities queries, because the required precision and scale information cannot be inferred.

The

 public static IEnumerable<TransactionTotalForProfitcenter> GetTotalTransactionsForProfitcenter(int profitcenterID)
    {
        List<TransactionTotalForProfitcenter> transactions = new List<TransactionTotalForProfitcenter>();
        using (var context = new CostReportEntities())
        {
          transactions = (from t in context.Transactions
                            join comp in context.Companies on t.CompanyID equals comp.CompanyID
                            join c in context.Countries on comp.CountryID equals c.CountryID
                            where c.CountryID.Equals(comp.CountryID) && t.CompanyID == comp.CompanyID 
                             
                            join acc in context.Accounts
                                 on t.AccountID equals acc.AccountID
                            join pc in context.Profitcenters
                                on t.ProfitcenterID equals pc.ProfitcenterID
                            group t by pc.ProfitcenterCode into tProfitcenter
                            
                            select new TransactionTotalForProfitcenter
                            {
                                ProfitcenterCode = tProfitcenter.Key,
                    //the error is occurring on the following line           
                                TotalTransactionAmount = (decimal)tProfitcenter.Sum(t => t.LocalAmount),  
                   //the error is occurring on the following line       
                                TotalTransactionAmountInEUR = (decimal)tProfitcenter.Sum(t => t.AmountInEUR) //the error is occurring on this line 
                            }
                            ).ToList();

        }
        return transactions;

    }

I have tried few options from the following posts but with no luck.

• LINQ to SQL strange float behavirou
• Casting from Decimal to Float

Can anyone point out what other options I may try. Excuse my little knowledge about LINQ if it is too trivial.

Answer 1

Entity Framework is indicating it does not support the conversion you desire. One workaround is to simply execute as much of the work in the database as you can, and then complete the process in memory. In your case, you can calculate the sum in its native type, pull the result into memory as an anonymous type, then perform your conversion as you construct the type you actually need. To take your original query, you can make the following change:

select new // anonymous type from DB
{
    ProfitcenterCode = tProfitcenter.Key,
    // notice there are no conversions for these sums
    TotalTransactionAmount = tProfitcenter.Sum(t => t.LocalAmount),       
    TotalTransactionAmountInEUR = tProfitcenter.Sum(t => t.AmountInEUR)
})
.AsEnumerable() // perform rest of work in memory
.Select(item =>
     // construct your proper type outside of DB
    new TransactionTotalForProfitcenter
    {
        ProfitcenterCode = item.ProfitcenterCode,
        TotalTransactionAmount = (decimal)item.TotalTransactionAmount
        TotalTransactionAmountInEUR = (decimal)item.TotalTransactionAmountInEUR
    }
).ToList();

Answer 2

If you don't happen to have the luxury of calling AsEnumerable than you can convert it to int and than to decimal with some math.

 (((decimal)((int)(x.Discount * 10000))) / 10000)

Each zero actually represents the precision that the conversion is going to have.

Got this answer from this. Just take a look at the end of the file.

Answer 3

I would suggest you make the cast after your query has finished

var somevar = (decimal)transactions.YourValue

Answer 4

Sometimes need casting, if more than two decimal palaces

     double TotalQty;
     double.TryParse(sequence.Sum(x => x.Field<decimal>("itemQty")).ToString(),out TotalQty);