LINQ to Entities 无法识别方法 'Double Parse(System.String)' 方法，并且此方法无法转换为存储表达式 [英] LINQ to Entities does not recognize the method 'Double Parse(System.String)' method, and this method cannot be translated into a store expression
问题描述
当我尝试运行报告时出现错误.问题在这里: model.Referring = Math.Round(_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question1) ? 0 : Double.Parse(m.Question1)).Average());
I get the error when i try to run report. The problem is here: model.Referring = Math.Round(_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question1) ? 0 : Double.Parse(m.Question1)).Average());
public class SummaryDetails
{
public int ChannelId { get; set; }
public int ChannelGroupId { get; set; }
public string Question1 { get; set; }
public string Question2 { get; set; }
public string Question3 { get; set; }
public string Question4 { get; set; }
public int OrganizationId { get; set; }
}
public ActionResult AreaManager(AreaManagerModel model)
{
model.ShowCustomerReport = false;
model.ShowSurveyReport = true;
LoadModelVariablesonPostBack(model, 8);
var _newSurveyResult = (
from ls in SessionHandler.CurrentContext.LennoxSurveyResponses
join ml in SessionHandler.CurrentContext.MailingListEntries on ls.SurveyCode equals ml.SurveyCode
join m in SessionHandler.CurrentContext.MailingLists on ml.MailingListId equals m.MailingListId
join ch in SessionHandler.CurrentContext.Channels on m.ChannelId equals ch.ChannelId
join cg in SessionHandler.CurrentContext.ChannelGroups on ch.ChannelGroupId equals cg.ChannelGroupId
join dcg in SessionHandler.CurrentContext.ChannelGroups on cg.ParentChannelGroupId equals dcg.ChannelGroupId
join ncg in SessionHandler.CurrentContext.ChannelGroups on dcg.ParentChannelGroupId equals ncg.ChannelGroupId
join pcg in SessionHandler.CurrentContext.ChannelGroups on ncg.ParentChannelGroupId equals pcg.ChannelGroupId
select new SummaryDetails {
OrganizationId = ch.OrganizationId,
Question1 = ls.Question1Answer,
Question2 = ls.Question2Answer,
Question3 = ls.Question3Answer,
Question4 = ls.Question4Answer,
ChannelId = ch.ChannelId,
ChannelGroupId = model.TMId != 0 ? cg.ChannelGroupId : model.DistrictId != 0 ? dcg.ChannelGroupId : model.AreaId != 0 ? ncg.ChannelGroupId : model.NationId != 0 ? pcg.ChannelGroupId : model.AreaId == 0 ? ncg.ChannelGroupId : model.DistrictId == 0 ? dcg.ChannelGroupId : cg.ChannelGroupId
}
);
var _newSentSurveys = (
from ml in SessionHandler.CurrentContext.MailingListEntries
join m in SessionHandler.CurrentContext.MailingLists on ml.MailingListId equals m.MailingListId
join ch in SessionHandler.CurrentContext.Channels on m.ChannelId equals ch.ChannelId
join cg in SessionHandler.CurrentContext.ChannelGroups on ch.ChannelGroupId equals cg.ChannelGroupId
join dcg in SessionHandler.CurrentContext.ChannelGroups on cg.ParentChannelGroupId equals dcg.ChannelGroupId
join ncg in SessionHandler.CurrentContext.ChannelGroups on dcg.ParentChannelGroupId equals ncg.ChannelGroupId
join pcg in SessionHandler.CurrentContext.ChannelGroups on ncg.ParentChannelGroupId equals pcg.ChannelGroupId
where (ml.EmailDate != null || ml.LetterDate != null || ml.EmailBounce == null)
select new SummaryDetails
{
OrganizationId = ch.OrganizationId,
ChannelId = ch.ChannelId,
ChannelGroupId = model.TMId != 0 ? cg.ChannelGroupId : model.DistrictId != 0 ? dcg.ChannelGroupId : model.AreaId != 0 ? ncg.ChannelGroupId : model.NationId != 0 ? pcg.ChannelGroupId : model.AreaId == 0 ? ncg.ChannelGroupId : model.DistrictId == 0 ? dcg.ChannelGroupId : cg.ChannelGroupId
}
);
if (model.ChannelId != 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelId == model.ChannelId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelId == model.ChannelId);
}
else if (model.TMId != 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.TMId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.TMId);
}
else if (model.DistrictId != 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.DistrictId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.DistrictId);
}
else if (model.AreaId != 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.AreaId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.AreaId);
}
else if (model.NationId != 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.NationId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.NationId);
}
else if (model.NationId == 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.OrganizationId == 8);
_newSentSurveys = _newSentSurveys.Where(p => p.OrganizationId == 8);
}
else if (model.AreaId == 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.LoggedChannelGroupId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.LoggedChannelGroupId);
}
else if (model.DistrictId == 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.LoggedChannelGroupId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.LoggedChannelGroupId);
}
else if (model.TMId == 0)
{
_newSurveyResult = _newSurveyResult.Where(p => p.ChannelGroupId == model.LoggedChannelGroupId);
_newSentSurveys = _newSentSurveys.Where(p => p.ChannelGroupId == model.LoggedChannelGroupId);
}
model.SentSurveys = _newSentSurveys.Count() > 0 ? _newSentSurveys.Count() : 0;
model.CompletedSurveys = _newSurveyResult.Count() > 0 ? _newSurveyResult.Count() : 0;
model.PercentageComplete = model.SentSurveys != 0 ? (Convert.ToDouble(model.CompletedSurveys) / Convert.ToDouble(model.SentSurveys)) : 0;
if (_newSurveyResult.Count() > 0)
{
model.Referring = Math.Round(_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question1) ? 0 : Double.Parse(m.Question1)).Average());
model.ServicePerformance = Math.Round(_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question2) ? 0 : Double.Parse(m.Question2)).Average());
model.InstallPerformance = Math.Round(_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question3) ? 0 : Double.Parse(m.Question3)).Average());
model.ReferringLennox = Math.Round(_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question4) ? 0 : Double.Parse(m.Question4)).Average());
double overAllScore = CalculateOverallScore(
_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question1) ? 0 : Double.Parse(m.Question1)).Sum(),
_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question2) ? 0 : Double.Parse(m.Question2)).Sum(),
_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question3) ? 0 : Double.Parse(m.Question3)).Sum(),
_newSurveyResult.Select(m => string.IsNullOrEmpty(m.Question4) ? 0 : Double.Parse(m.Question4)).Sum(),
_newSurveyResult.Count());
model.OverallScore = Math.Round(overAllScore);
}
}
推荐答案
这里的问题是你的查询正在被翻译成 SQL 并在数据库上运行，而实体框架不知道如何翻译 Double.解析 为有效的 SQL 代码.但是，您可以定义一个自定义方法来进行解析，并告诉实体框架如何将该方法转换为 SQL.过程如下:
The problem here is that your query is being translated into SQL and run on the database, and Entity Framework doesn't know how to translate Double.Parse into valid SQL code. However, you can define a custom method to do the parsing, and tell Entity Framework how to translate that method to SQL. Here's how it goes:
定义翻译
在文本编辑器中打开您的 *.edmx 文件，并查找 <Schema Namespace="YourModel" ...> 标签.在 Schema 标签内，添加以下内容:
Open up your *.edmx file in a text editor, and look for the <edmx:ConceptualModels> tag. Under that you should see a <Schema Namespace="YourModel" ...> tag. Inside the Schema tag, add the following:
<Function Name="ParseDouble" ReturnType="Edm.Double">
<Parameter Name="stringvalue" Type="Edm.String" />
<DefiningExpression>
cast(stringvalue as Edm.Double)
</DefiningExpression>
</Function>
这定义了您的自定义 ParseDouble 函数将被翻译成的 Enity-SQL 代码.
This defines the Enity-SQL code that your custom ParseDouble function is going to be translated into.
创建要翻译的方法
现在我们需要在代码中定义一个匹配函数，您可以将其放入 LINQ 语句中.您的 EDMX 文件用于生成继承自 ObjectContext 的分部类.由于它是一个分部类，您可以向其中添加自己的方法，而无需修改生成的代码 - 只需确保类名匹配即可.
Now we need to define a matching function in code that you can put in your LINQ statement. Your EDMX file is used to generate a partial class that inherits from ObjectContext. Since it's a partial class, you can add your own methods to it without touching the generated code - just make sure the class names match.
using System.Data.Objects.DataClasses;
public partial class YourObjectContext
{
/// <summary>
/// This method exists for use in LINQ queries,
/// as a stub that will be converted to a SQL CAST statement.
/// </summary>
[EdmFunction("YourModel", "ParseDouble")]
public static double ParseDouble(string stringvalue)
{
return Double.Parse(stringvalue);
}
}
现在您可以返回到您的 LINQ 语句，并将 Double.Parse 的任何实例替换为 YourObjectContext.ParseDouble.由于这是一个实际调用Double.Parse的实际方法，它将在LINQ to Objects调用上起作用，并且由于它也在EDMX文件中定义，因此可以通过LINQ to Entities翻译成SQL
Now you can go back to your LINQ statement, and replace any instances of Double.Parse with YourObjectContext.ParseDouble. Since this is an actual method that actually calls Double.Parse, it will work on LINQ to Objects calls, and since it is also defined in the EDMX file, it can be translated into SQL by LINQ to Entities as well.
但是等等，您还没有完成！
我注意到您的 LINQ 语句还包括对 Math.Round 的调用.我不知道实体框架是否包含该方法的翻译，但如果没有，则在为 Double.Parse 修复该方法后，您将收到该方法的相同错误.幸运的是，这种情况的解决方案几乎完全相同，只是 EDMX 文件中定义的函数看起来像这样:
I noticed that your LINQ statement also includes a call to Math.Round. I don't know off the top of my head if Entity Framework includes a translation for that method, but if it doesn't, you'll get the same error for that method after you fix the one for Double.Parse. Fortunately, the solution for that case is almost exactly the same, except the function defined in the EDMX file would look something like this:
<Function Name="Round" ReturnType="Edm.Double">
<Parameter Name="input" Type="Edm.Double" />
<DefiningExpression>
Round(input)
</DefiningExpression>
</Function>
您可以使用这个 EDM 规范函数列表来查看放在
You can use this list of EDM Canonical Functions to see what's valid to put inside the <DefiningExpression> tags.
这篇关于LINQ to Entities 无法识别方法 'Double Parse(System.String)' 方法，并且此方法无法转换为存储表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
