你的位置:首页 > ASP.net教程

[ASP.net教程]解决一个报表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" ="http://schemas.microsoft.com/netfx/2009/xaml/activities" ="http://schemas.open ="clr-namespace:Microsoft.Data.Entity.Design.DatabaseGeneration.Activities;assembly=Microsoft.Data.Entity.Design.DatabaseGeneration" ="clr-namespace:Microsoft.Data.Entity.Design.DatabaseGeneration.Activities;assembly=Microsoft.Data.Entity.Design" ="http://schemas.microsoft.com/netfx/2010/xaml/activities/debugger" ="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation" ="http://schemas.microsoft.com/netfx/2010/xaml/activities/presentation" ="clr-namespace:System.Collections.Generic;assembly=mscorlib" ="clr-namespace:System.Collections.ObjectModel;assembly=mscorlib" ="clr-namespace:System.Data.Metadata.Edm;assembly=System.Data.Entity" ="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.</x:String>   <x:String>System.</x:String>   <x:String>System.</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.</AssemblyReference>   <AssemblyReference>System.</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" ="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);    }