最近測試組提了一個bug，說是某個報(bào)表點(diǎn)擊查詢報(bào)錯，查看錯誤log，錯誤信息如下。
類型“Ticket.Data.SqlFuns”上指定的方法“Boolean ContainsAll(System.String, System.String)”無法轉(zhuǎn)換為 LINQ to Entities 存儲表達(dá)式。
定位到錯誤行，發(fā)現(xiàn)是一個自定義的SQLCLR函數(shù)，如下代碼。
理論上CLR函數(shù)可以用在lambda表達(dá)式中，但是為什么現(xiàn)在突然就不行了呢？
依據(jù)錯誤提示，首先想到的是，函數(shù)ContainsAll已經(jīng)不在edmx文件中了，查看edmx文件的歷史記錄，對比發(fā)現(xiàn)，果然CLR函數(shù)被刪除了，如下圖。
![clipboard[1]](http://s1.VeVb.com/20151016/kukzoegxyqj49.png "clipboard[1]")
解決方案：
用新的'數(shù)據(jù)庫生成工作流'文件，重新生成，結(jié)果edmx就會包含那些CLR函數(shù)了。
![clipboard[2]](http://s1.VeVb.com/20151016/i0gujxifkrg49.png "clipboard[2]")
附數(shù)據(jù)庫生成工作流文件：
<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>
解決了CLR函數(shù)報(bào)錯的問題后，發(fā)現(xiàn)又存在性能問題，原因在于審核歷史記錄表AuditHistory中字段ItemContent存的是json字符串，而這個字符串很大很大，所以如果查詢的數(shù)據(jù)量很大的話，就會報(bào)out of memory內(nèi)存溢出的錯誤。
數(shù)據(jù)庫數(shù)據(jù)類型及數(shù)據(jù)：
![clipboard[3]](http://s1.VeVb.com/20151016/0zev1kysnl150.png "clipboard[3]")
解決方案：
1，只查詢需要的字段，并且先不要包含ItemContent，因?yàn)樵撟侄蝺?nèi)容太大。
![clipboard[4]](http://s1.VeVb.com/20151016/owxhhft0ev450.png "clipboard[4]")
2，ItemContent改為二進(jìn)制存儲
方法1只是臨時的解決方案，因?yàn)椴樵兂鼋Y(jié)果后，后面會foreach，拿到每條記錄的ItemContent并反序列化，因?yàn)槲仪懊鏇]有包括這個字段，所以每條記錄在反序列化之前還會去查一次數(shù)據(jù)庫為了拿到ItemContent，這樣如果數(shù)據(jù)量一大，就會有可能查詢超時。
要想從根本上解決這個問題，最好的方法是將ItemContent改為二進(jìn)制存儲，這樣體積就會小很多。
保存為二進(jìn)制：var localResponse = localRS.ToBinary();
/// <summary> /// 對象轉(zhuǎn)換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; }
拿到二進(jìn)制數(shù)據(jù)反序列化：
/// <summary> /// binary轉(zhuǎn)換對象 /// </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); }
新聞熱點(diǎn)
疑難解答
圖片精選
