最近测试组提了一个bug，说是某个报表点击查询报错，查看错误log，错误信息如下。
类型“Ticket.Data.SqlFuns”上指定的方法“Boolean ContainsAll(System.String, System.String)”无法转换为 LINQ to Entities 存储表达式。
定位到错误行，发现是一个自定义的SQLCLR函数，如下代码。
为什么使用CLR函数ContainsAll报错
理论上CLR函数可以用在lambda表达式中，但是为什么现在突然就不行了呢？
依据错误提示，首先想到的是，函数ContainsAll已经不在edmx文件中了，查看edmx文件的历史记录，对比发现，果然CLR函数被删除了，如下图。
![clipboard[1]](http://images2015.cnblogs.com/blog/311549/201510/311549-20151028172110857-1277502551.png "clipboard[1]")
解决方案：
用新的'数据库生成工作流'文件，重新生成，结果edmx就会包含那些CLR函数了。
![clipboard[2]](http://images2015.cnblogs.com/blog/311549/201510/311549-20151028172117732-1178087617.png "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("</Schema>", " <Function Name=""ContainsAll"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""search"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & " <Function Name=""ContainsAny"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""search"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & " <Function Name=""ContainsOne"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""search"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & " <Function Name=""GetDateTimeString"" ReturnType=""nvarchar"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""datetime"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""format"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & " <Function Name=""RegexIsMatch"" ReturnType=""bit"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""pattern"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""isIgnoreCase"" Type=""bit"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & " <Function Name=""RegexMatch"" ReturnType=""nvarchar"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""pattern"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""isIgnoreCase"" Type=""bit"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""groupnum"" Type=""int"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & " <Function Name=""RegexReplace"" ReturnType=""nvarchar"" Aggregate=""false"" BuiltIn=""false"" NiladicFunction=""false"" IsComposable=""true"" ParameterTypeSemantics=""AllowImplicitConversion"" Schema=""dbo"">" & vbNewLine & " <Parameter Name=""input"" Type=""nvarchar(max)"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""pattern"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""replacement"" Type=""nvarchar"" Mode=""In"" />" & vbNewLine & " <Parameter Name=""isIgnoreCase"" Type=""bit"" Mode=""In"" />" & vbNewLine & " </Function>" & vbNewLine & "</Schema>")]</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]](http://images2015.cnblogs.com/blog/311549/201510/311549-20151028172119560-994991241.png "clipboard[3]")
解决方案：
1，只查询需要的字段，并且先不要包含ItemContent，因为该字段内容太大。
![clipboard[4]](http://images2015.cnblogs.com/blog/311549/201510/311549-20151028172128435-872221398.png "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); }
