最近测试组提了一个bug，说是某个报表点击查询报错，查看错误log，错误信息如下。
类型“Ticket.Data.SqlFuns”上指定的方法“Boolean ContainsAll(System.String, System.String)”无法转换为 LINQ to Entities 存储表达式。
定位到错误行，发现是一个自定义的SQLCLR函数，如下代码。
为什么使用CLR函数ContainsAll报错理论上CLR函数可以用在lambda表达式中，但是为什么现在突然就不行了呢？
依据错误提示，首先想到的是，函数ContainsAll已经不在edmx文件中了，查看edmx文件的历史记录，对比发现，果然CLR函数被删除了，如下图。
解决方案：
用新的'数据库生成工作流'文件，重新生成，结果edmx就会包含那些CLR函数了。
附数据库生成工作流文件：
<Activitymc: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:PropertyName="Csdl"Type="InArgument(sdme:EdmItemCollection)"/><x:PropertyName="ExistingSsdl"Type="InArgument(x:String)"/><x:PropertyName="ExistingMsl"Type="InArgument(x:String)"/><x:PropertyName="Ssdl"Type="OutArgument(x:String)"/><x:PropertyName="Msl"Type="OutArgument(x:String)"/><x:PropertyName="Ddl"Type="OutArgument(x:String)"/></x:Members><sap2010:WorkflowViewState.IdRef>GenerateDatabaseScriptWorkflow_1</sap2010:WorkflowViewState.IdRef><TextExpression.NamespacesForImplementation><sco:Collectionx: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:Collectionx: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><Sequencesap2010:WorkflowViewState.IdRef="Sequence_1"><mdedda:CsdlToSsdlAndMslActivityCsdlInput="[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]"/><Assignsap2010:WorkflowViewState.IdRef="Assign_2"><Assign.To><OutArgumentx:TypeArguments="x:String">[Ssdl]</OutArgument></Assign.To><Assign.Value><InArgumentx: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:SsdlToDdlActivityTemplatePath="{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:ViewStateDataId="CsdlToSsdlAndMslActivity_1"sap:VirtualizedContainerService.HintSize="243,22"/><sap2010:ViewStateDataId="Assign_2"sap:VirtualizedContainerService.HintSize="243,61"/><sap2010:ViewStateDataId="SsdlToDdlActivity_1"sap:VirtualizedContainerService.HintSize="243,22"/><sap2010:ViewStateDataId="Sequence_1"sap:VirtualizedContainerService.HintSize="265,309"><sap:WorkflowViewStateService.ViewState><scg:Dictionaryx:TypeArguments="x:String, x:Object"><x:Booleanx:Key="IsExpanded">True</x:Boolean></scg:Dictionary></sap:WorkflowViewStateService.ViewState></sap2010:ViewStateData><sap2010:ViewStateDataId="GenerateDatabaseScriptWorkflow_1"sap:VirtualizedContainerService.HintSize="305,389"/></sap2010:ViewStateManager></sap2010:WorkflowViewState.ViewStateManager></Activity>
因大字段导致的outofmemory内存溢出问题 解决了CLR函数报错的问题后，发现又存在性能问题，原因在于审核历史记录表AuditHistory中字段ItemContent存的是json字符串，而这个字符串很大很大，所以如果查询的数据量很大的话，就会报out of memory内存溢出的错误。
数据库数据类型及数据：
解决方案：
1，只查询需要的字段，并且先不要包含ItemContent，因为该字段内容太大。
2，ItemContent改为二进制存储
方法1只是临时的解决方案，因为查询出结果后，后面会foreach，拿到每条记录的ItemContent并反序列化，因为我前面没有包括这个字段，所以每条记录在反序列化之前还会去查一次数据库为了拿到ItemContent，这样如果数据量一大，就会有可能查询超时。
要想从根本上解决这个问题，最好的方法是将ItemContent改为二进制存储，这样体积就会小很多。
保存为二进制：var localResponse = localRS.ToBinary();
///<summary>///对象转换binary///</summary>///<param name="input"></param>///<returns></returns>publicstaticbyte[] ToBinary(thisobjectinput)
{varjsonStr =input.ToJson();if(!jsonStr.IsNullOrEmpty())
{returnjsonStr.GZipCompress();
}returnnull;
}
拿到二进制数据反序列化：
///<summary>///binary转换对象///</summary>///<param name="input"></param>///<returns></returns>publicstaticT FromBinary<T>(thisbyte[] input)
{varjsonStr =input.GZipDecompress();if(!jsonStr.IsNullOrEmpty())
{returnjsonStr.FromJson<T>();
}returndefault(T);
}