C# LINQ to Entities 查询不支持转换为十进制

Casting to Decimal is not supported in LINQ to Entities queries

提问by shaz

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

我有一个数据库表事务（事务 ID、LocalAmount...）。其中 Localamount 属性的数据类型是float。在UI上我试图返回SUM一行列（Localamount）的一个按钮单击事件。

I have used decimalinstead 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

• LINQ to SQL 奇怪的浮动行为
• 从十进制转换为浮点数

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

谁能指出我可以尝试的其他选项。如果它太琐碎，请原谅我对 LINQ 的一点了解。

采纳答案by Anthony Pegram

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

回答by lumee

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

我建议您在查询完成后进行演员表

var somevar = (decimal)transactions.YourValue

回答by Arun Prasad E S

Sometimes need casting, if more than two decimal palaces

有时需要铸造，如果超过两个小数宫

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

回答by alp ates

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

如果您碰巧没有调用 AsEnumerable 的奢侈，那么您可以int使用一些数学将其转换为十进制数。

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

从这个得到这个答案。只需看看文件的末尾。