LINQ to Entities查询不支持强制转换为十进制 [英] Casting to Decimal is not supported in LINQ to Entities queries
问题描述
我有一个数据库表Transaction（事务ID，LocalAmount ...）。其中Localamount属性的数据类型为 float 。在用户界面上，我尝试在按钮单击事件中返回一行（Localamount）列的 SUM 。
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.
我使用了十进制而不是 float
但是我在强制转换为十进制
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.
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的小知识，如果它太琐碎。
Can anyone point out what other options I may try. Excuse my little knowledge about LINQ if it is too trivial.
推荐答案
Entity Framework表示它不支持您想要的转换。一种解决方法是简单地在数据库中尽可能多地执行工作，然后在内存中完成该过程。在您的情况下，您可以以其本机类型计算总和，将结果作为匿名类型存入内存，然后在构造实际需要的类型时执行转换。要进行原始查询，可以进行以下更改：
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();
这篇关于LINQ to Entities查询不支持强制转换为十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
