当前位置: 代码迷 >> 报表 >> 解决一个报表EdmFunction报错有关问题
  详细解决方案

解决一个报表EdmFunction报错有关问题

热度:446   发布时间:2016-04-29 01:52:26.0
解决一个报表EdmFunction报错问题

    最近测试组提了一个bug,说是某个报表点击查询报错,查看错误log,错误信息如下。

类型“Ticket.Data.SqlFuns”上指定的方法“Boolean ContainsAll(System.String, System.String)”无法转换为 LINQ to Entities 存储表达式。

定位到错误行,发现是一个自定义的SQLCLR函数,如下代码。

clipboard

为什么使用CLR函数ContainsAll报错

理论上CLR函数可以用在lambda表达式中,但是为什么现在突然就不行了呢?

依据错误提示,首先想到的是,函数ContainsAll已经不在edmx文件中了,查看edmx文件的历史记录,对比发现,果然CLR函数被删除了,如下图。

clipboard[1]

解决方案:

用新的'数据库生成工作流'文件,重新生成,结果edmx就会包含那些CLR函数了。

clipboard[2]

附数据库生成工作流文件:

<Activity mc:Ignorable="sap sap2010 sads" x:Class="GenerateDatabaseScriptWorkflow" xmlns="http://schemas.microsoft.com/netfx/2009/xaml/activities" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mdedda="clr-namespace:Microsoft.Data.Entity.Design.DatabaseGeneration.Activities;assembly=Microsoft.Data.Entity.Design.DatabaseGeneration" xmlns:mdedda1="clr-namespace:Microsoft.Data.Entity.Design.DatabaseGeneration.Activities;assembly=Microsoft.Data.Entity.Design" xmlns:sads="http://schemas.microsoft.com/netfx/2010/xaml/activities/debugger" xmlns:sap="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation" xmlns:sap2010="http://schemas.microsoft.com/netfx/2010/xaml/activities/presentation" xmlns:scg="clr-namespace:System.Collections.Generic;assembly=mscorlib" xmlns:sco="clr-namespace:System.Collections.ObjectModel;assembly=mscorlib" xmlns:sdme="clr-namespace:System.Data.Metadata.Edm;assembly=System.Data.Entity" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">  <x:Members>    <x:Property Name="Csdl" Type="InArgument(sdme:EdmItemCollection)" />    <x:Property Name="ExistingSsdl" Type="InArgument(x:String)" />    <x:Property Name="ExistingMsl" Type="InArgument(x:String)" />    <x:Property Name="Ssdl" Type="OutArgument(x:String)" />    <x:Property Name="Msl" Type="OutArgument(x:String)" />    <x:Property Name="Ddl" Type="OutArgument(x:String)" />  </x:Members>  <sap2010:WorkflowViewState.IdRef>GenerateDatabaseScriptWorkflow_1</sap2010:WorkflowViewState.IdRef>  <TextExpression.NamespacesForImplementation>    <sco:Collection x:TypeArguments="x:String">      <x:String>System.Activities</x:String>      <x:String>System</x:String>      <x:String>Microsoft.VisualBasic</x:String>      <x:String>System.Xml.Linq</x:String>      <x:String>System.Xml</x:String>      <x:String>System.Xml.Serialization</x:String>    </sco:Collection>  </TextExpression.NamespacesForImplementation>  <TextExpression.ReferencesForImplementation>    <sco:Collection x:TypeArguments="AssemblyReference">      <AssemblyReference>System.Activities</AssemblyReference>      <AssemblyReference>mscorlib</AssemblyReference>      <AssemblyReference>System</AssemblyReference>      <AssemblyReference>System.Core</AssemblyReference>      <AssemblyReference>System.ComponentModel.Composition</AssemblyReference>      <AssemblyReference>System.ServiceModel</AssemblyReference>      <AssemblyReference>Microsoft.VisualBasic.Editor</AssemblyReference>      <AssemblyReference>Microsoft.VisualBasic.LanguageService</AssemblyReference>      <AssemblyReference>Microsoft.VisualBasic</AssemblyReference>      <AssemblyReference>Microsoft.VisualStudio.VisualBasic.LanguageService</AssemblyReference>      <AssemblyReference>System.Xml.Linq</AssemblyReference>      <AssemblyReference>System.Xml</AssemblyReference>      <AssemblyReference>System.Data</AssemblyReference>      <AssemblyReference>System.Runtime.Serialization</AssemblyReference>    </sco:Collection>  </TextExpression.ReferencesForImplementation>  <Sequence sap2010:WorkflowViewState.IdRef="Sequence_1">    <mdedda:CsdlToSsdlAndMslActivity CsdlInput="[Csdl]" sap2010:WorkflowViewState.IdRef="CsdlToSsdlAndMslActivity_1" MslOutput="[Msl]" MslOutputGeneratorType="Microsoft.Data.Entity.Design.DatabaseGeneration.OutputGenerators.CsdlToMsl, Microsoft.Data.Entity.Design.DatabaseGeneration" OutputGeneratorType="Microsoft.Data.Entity.Design.DatabaseGeneration.OutputGenerators.CsdlToSsdl, Microsoft.Data.Entity.Design.DatabaseGeneration" SsdlOutput="[Ssdl]" />    <Assign sap2010:WorkflowViewState.IdRef="Assign_2">      <Assign.To>        <OutArgument x:TypeArguments="x:String">[Ssdl]</OutArgument>      </Assign.To>      <Assign.Value>        <InArgument x:TypeArguments="x:String" xml:space="preserve">[Ssdl.Replace("&lt;/Schema&gt;", "        &lt;Function Name=""ContainsAll"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""search"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "        &lt;Function Name=""ContainsAny"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""search"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "        &lt;Function Name=""ContainsOne"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""search"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "        &lt;Function Name=""GetDateTimeString"" ReturnType=""nvarchar"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""datetime"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""format"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "        &lt;Function Name=""RegexIsMatch"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""pattern"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""isIgnoreCase"" Type=""bit"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "        &lt;Function Name=""RegexMatch"" ReturnType=""nvarchar"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""pattern"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""isIgnoreCase"" Type=""bit"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""groupnum"" Type=""int"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "        &lt;Function Name=""RegexReplace"" ReturnType=""nvarchar"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo""&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""pattern"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""replacement"" Type=""nvarchar"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "          &lt;Parameter Name=""isIgnoreCase"" Type=""bit"" Mode=""In"" /&gt;" &amp; vbNewLine &amp; "        &lt;/Function&gt;" &amp; vbNewLine &amp; "&lt;/Schema&gt;")]</InArgument>      </Assign.Value>    </Assign>    <mdedda1:SsdlToDdlActivity TemplatePath="{x:Null}" DdlOutput="[Ddl]" ExistingSsdlInput="[ExistingSsdl]" sap2010:WorkflowViewState.IdRef="SsdlToDdlActivity_1" SsdlInput="[Ssdl]" />    <sads:DebugSymbol.Symbol>d5kBQzpcUHJvZ3JhbSBGaWxlcyAoeDg2KVxNaWNyb3NvZnQgVmlzdWFsIFN0dWRpbyAxMS4wXENvbW1vbjdcSURFXEV4dGVuc2lvbnNcTWljcm9zb2Z0XEVudGl0eSBGcmFtZXdvcmsgVG9vbHNcREJHZW5cVGFibGVQZXJUeXBlU3RyYXRlZ3kuV2l0aC5GdW5jdGlvbi54YW1sDTIDPg4CAQEzBTPGAwIBDTQFOw4CAQk8BTy4AQIBAjMwMzgCARQzuwMzwwMCARIzgAEzhwECARAznwEznAICAQ8zsQIzrwMCAQ42MTY3AgEKPFw8bAIBBzxCPEkCAQU8rQE8tQECAQM=</sads:DebugSymbol.Symbol>  </Sequence>  <sap2010:WorkflowViewState.ViewStateManager>    <sap2010:ViewStateManager>      <sap2010:ViewStateData Id="CsdlToSsdlAndMslActivity_1" sap:VirtualizedContainerService.HintSize="243,22" />      <sap2010:ViewStateData Id="Assign_2" sap:VirtualizedContainerService.HintSize="243,61" />      <sap2010:ViewStateData Id="SsdlToDdlActivity_1" sap:VirtualizedContainerService.HintSize="243,22" />      <sap2010:ViewStateData Id="Sequence_1" sap:VirtualizedContainerService.HintSize="265,309">        <sap:WorkflowViewStateService.ViewState>          <scg:Dictionary x:TypeArguments="x:String, x:Object">            <x:Boolean x:Key="IsExpanded">True</x:Boolean>          </scg:Dictionary>        </sap:WorkflowViewStateService.ViewState>      </sap2010:ViewStateData>      <sap2010:ViewStateData Id="GenerateDatabaseScriptWorkflow_1" sap:VirtualizedContainerService.HintSize="305,389" />    </sap2010:ViewStateManager>  </sap2010:WorkflowViewState.ViewStateManager></Activity>

因大字段导致的outofmemory内存溢出问题

    解决了CLR函数报错的问题后,发现又存在性能问题,原因在于审核历史记录表AuditHistory中字段ItemContent存的是json字符串,而这个字符串很大很大,所以如果查询的数据量很大的话,就会报out of memory内存溢出的错误。

    数据库数据类型及数据:

clipboard[3]

解决方案:

1,只查询需要的字段,并且先不要包含ItemContent,因为该字段内容太大。

clipboard[4]

2,ItemContent改为二进制存储

    方法1只是临时的解决方案,因为查询出结果后,后面会foreach,拿到每条记录的ItemContent并反序列化,因为我前面没有包括这个字段,所以每条记录在反序列化之前还会去查一次数据库为了拿到ItemContent,这样如果数据量一大,就会有可能查询超时。

    要想从根本上解决这个问题,最好的方法是将ItemContent改为二进制存储,这样体积就会小很多。

    保存为二进制:var localResponse = localRS.ToBinary();

/// <summary>        /// 对象转换binary        /// </summary>        /// <param name="input"></param>        /// <returns></returns>        public static byte[] ToBinary(this object input)        {            var jsonStr = input.ToJson();            if (!jsonStr.IsNullOrEmpty())            {                return jsonStr.GZipCompress();            }            return null;        }

    拿到二进制数据反序列化:

/// <summary>        /// binary转换对象        /// </summary>        /// <param name="input"></param>        /// <returns></returns>        public static T FromBinary<T>(this byte[] input)        {            var jsonStr = input.GZipDecompress();            if (!jsonStr.IsNullOrEmpty())            {                return jsonStr.FromJson<T>();            }            return default(T);        }
  相关解决方案