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.

Solution 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();

Solution 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.

Solution 3

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

var somevar = (decimal)transactions.YourValue

Solution 4

Sometimes need casting, if more than two decimal palaces

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

View more solutions

Comments

[PineCone]

but I am just using two columns from the transaction table, which are LocalAmount and AmountInEUR and they are Float in the database but decimal in the ViewModel class. And the transactions need to be converted using .ToList() since the method returns List<>. In this case can I actually apply your suggestion? I didn't quite get how would I do it.

[PineCone]

This seems feasible but I am getting an error {"The specified cast from a materialized 'System.Decimal' type to the 'System.Double' type is not valid."}. This is because the Model class for Transaction contains double for LocalAmount and AmountInEuro properties that I have manually changed but then it gives another error of The type 'Edm.Double' of the member 'LocalAmount' in the conceptual side type 'CostReportModel.Transaction' does not match with the type 'System.Decimal' of the member 'LocalAmount' on the object side type 'CostReportModel.Transaction'.

[Anthony Pegram]

Sadly, those are separate issues from this query. It seems you (or somebody on your team) have been toying with your entities, which can be perilous. You might need a separate, new question on safely changing types in your entity data model.

[PineCone]

The problem is I made the datatype in DB at first float (lack of knowledge), then changed them to decimal (because I was getting formatting issue). However, the Model got auto generated by EF from float to double. Would that be good idea to generate the Entity Classes again to try this solution?

[Anthony Pegram]

Yes, you would ideally want your entity classes to agree with your database on the type. You seem to have done the sensible* thing in changing the database type to decimal since you are dealing with financial amounts (it appears), and it would be good for your classes to treat them the same way. That may make the query as written above a moot point.

[Anthony Pegram]

(*In other instances, this "sensible" thing is often hard to do, because you might have already built up legacy data, and have mounds of legacy code, and with that, even sensible changes become risky.)

[PineCone]

I have regenerated the Models and changing the datatype from double to decimal along with your solution worked perfectly. Thanks a lot.

[Philip Vaughn]

NOT an answer. If someone doesn't want to pull the DB 80,000,000 times per query.

[Anthony Pegram]

@PhilipVaughn, you are incorrect. Working with AsEnumerable() on an EF query, all results that match the previous portion of the query will be fetched into memory, and and subsequent will be performed in memory. It is not going back to the database for every iteration. (Had the query been accessing unloaded navigation properties, yes, that would be a problem.)

[Philip Vaughn]

@AnthonyPegram Not really what I meant. I work with very complicated queries on a regular basis. If you're working with any kind IQueryable that is combining many other iqueryables into it pulling one into an enumerable negates basically everything. I hate these kinds of answers as they DO NOT solve the question that was asked. The answer by alp ates below is a correct response.

[Philip Vaughn]

@AnthonyPegram Basically the POINT of an iquery is so the DB is NOT pulled. You are BUILDING an sql query. Then after the query is completely built you go to the db. So say I have a query that needs a decimal conversion and I'm using that query in another query I CANNOT send it to an enumerable otherwise EVER iteration of the parent query will need to go to the db EVER TIME. That's UNACCEPTABLE.

[Philip Vaughn]

This is a correct response to the OP's question as it keeps everything in query form.