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））。 ））;

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并在数据库上运行，Entity Framework不知道如何将 Double.Parse 转换为有效的SQL代码。但是，您可以定义一个自定义方法来进行解析，并告诉Entity Framework如何将该方法转换为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文件，并查找 && edmx：ConceptualModels> 标签。在那里你应该看到一个< 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.

创建一种要翻译的方法

Create a method to be translated

现在我们需要定义一个匹配函数在你可以放入你的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语句，用 YourObjectContext.ParseDouble 替换 Double.Parse 的任何实例。由于这是实际调用 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 。如果Entity Framework包含该方法的翻译，我不知道顶部，但如果没有，您将在修复之后为该方法获得相同的错误。 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规范函数，查看< DefiningExpression> 标签中有效的内容。

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屋！