我有这个简单的LINQ查询,执行8395毫秒：
var _context = new SurveyContext();
_context.Database.Log = Console.WriteLine;
(from p in _context.Participants
join row in _context.ListAnswerSelections
on new {p.Id,QuestionId = 434} equals
new {Id = row.RelatedParticipantId,QuestionId = row.RelatedQuestionId}
select new { V = row.NumericValue.ToString() })
.ToList()
.Select(x => new {R = x.V})
.Count() //This is just to see one number instead of whole result
.Dump("Results");
它的IL：
IL_0000: nop IL_0001: newobj Survey.Model.SurveyContext..ctor IL_0006: stloc.0 // _context IL_0007: ldloc.0 // _context IL_0008: callvirt System.Data.Entity.DbContext.get_Database IL_000D: ldnull IL_000E: ldftn System.Console.WriteLine IL_0014: newobj System.Action<System.String>..ctor IL_0019: callvirt System.Data.Entity.Database.set_Log IL_001E: nop IL_001F: ldloc.0 // _context IL_0020: callvirt Survey.Model.SurveyContext.get_Participants IL_0025: ldloc.0 // _context IL_0026: callvirt Survey.Model.SurveyContext.get_ListAnswerSelections IL_002B: ldtoken Survey.Model.Participant IL_0030: call System.Type.GetTypeFromHandle IL_0035: ldstr "p" IL_003A: call System.Linq.Expressions.Expression.Parameter IL_003F: stloc.1 // CS$0$0000 IL_0040: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>..ctor IL_0045: ldtoken <>f__AnonymousType0<System.Int32,System.Int32> IL_004A: call System.Reflection.MethodBase.GetMethodFromHandle IL_004F: castclass System.Reflection.ConstructorInfo IL_0054: ldc.i4.2 IL_0055: newarr System.Linq.Expressions.Expression IL_005A: stloc.2 // CS$0$0001 IL_005B: ldloc.2 // CS$0$0001 IL_005C: ldc.i4.0 IL_005D: ldloc.1 // CS$0$0000 IL_005E: ldtoken Survey.Model.ModelBase.get_Id IL_0063: call System.Reflection.MethodBase.GetMethodFromHandle IL_0068: castclass System.Reflection.MethodInfo IL_006D: call System.Linq.Expressions.Expression.Property IL_0072: stelem.ref IL_0073: ldloc.2 // CS$0$0001 IL_0074: ldc.i4.1 IL_0075: ldc.i4 B2 01 00 00 IL_007A: Box System.Int32 IL_007F: ldtoken System.Int32 IL_0084: call System.Type.GetTypeFromHandle IL_0089: call System.Linq.Expressions.Expression.Constant IL_008E: stelem.ref IL_008F: ldloc.2 // CS$0$0001 IL_0090: ldc.i4.2 IL_0091: newarr System.Reflection.MethodInfo IL_0096: stloc.3 // CS$0$0002 IL_0097: ldloc.3 // CS$0$0002 IL_0098: ldc.i4.0 IL_0099: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_Id IL_009E: ldtoken <>f__AnonymousType0<System.Int32,System.Int32> IL_00A3: call System.Reflection.MethodBase.GetMethodFromHandle IL_00A8: castclass System.Reflection.MethodInfo IL_00AD: stelem.ref IL_00AE: ldloc.3 // CS$0$0002 IL_00AF: ldc.i4.1 IL_00B0: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_QuestionId IL_00B5: ldtoken <>f__AnonymousType0<System.Int32,System.Int32> IL_00BA: call System.Reflection.MethodBase.GetMethodFromHandle IL_00BF: castclass System.Reflection.MethodInfo IL_00C4: stelem.ref IL_00C5: ldloc.3 // CS$0$0002 IL_00C6: call System.Linq.Expressions.Expression.New IL_00CB: ldc.i4.1 IL_00CC: newarr System.Linq.Expressions.ParameterExpression IL_00D1: stloc.s 04 // CS$0$0003 IL_00D3: ldloc.s 04 // CS$0$0003 IL_00D5: ldc.i4.0 IL_00D6: ldloc.1 // CS$0$0000 IL_00D7: stelem.ref IL_00D8: ldloc.s 04 // CS$0$0003 IL_00DA: call System.Linq.Expressions.Expression.Lambda IL_00DF: ldtoken Survey.Model.ListAnswerSelection IL_00E4: call System.Type.GetTypeFromHandle IL_00E9: ldstr "row" IL_00EE: call System.Linq.Expressions.Expression.Parameter IL_00F3: stloc.1 // CS$0$0000 IL_00F4: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>..ctor IL_00F9: ldtoken <>f__AnonymousType0<System.Int32,System.Int32> IL_00FE: call System.Reflection.MethodBase.GetMethodFromHandle IL_0103: castclass System.Reflection.ConstructorInfo IL_0108: ldc.i4.2 IL_0109: newarr System.Linq.Expressions.Expression IL_010E: stloc.2 // CS$0$0001 IL_010F: ldloc.2 // CS$0$0001 IL_0110: ldc.i4.0 IL_0111: ldloc.1 // CS$0$0000 IL_0112: ldtoken Survey.Model.ListAnswerSelection.get_RelatedParticipantId IL_0117: call System.Reflection.MethodBase.GetMethodFromHandle IL_011C: castclass System.Reflection.MethodInfo IL_0121: call System.Linq.Expressions.Expression.Property IL_0126: stelem.ref IL_0127: ldloc.2 // CS$0$0001 IL_0128: ldc.i4.1 IL_0129: ldloc.1 // CS$0$0000 IL_012A: ldtoken Survey.Model.ListAnswerSelection.get_RelatedQuestionId IL_012F: call System.Reflection.MethodBase.GetMethodFromHandle IL_0134: castclass System.Reflection.MethodInfo IL_0139: call System.Linq.Expressions.Expression.Property IL_013E: stelem.ref IL_013F: ldloc.2 // CS$0$0001 IL_0140: ldc.i4.2 IL_0141: newarr System.Reflection.MethodInfo IL_0146: stloc.3 // CS$0$0002 IL_0147: ldloc.3 // CS$0$0002 IL_0148: ldc.i4.0 IL_0149: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_Id IL_014E: ldtoken <>f__AnonymousType0<System.Int32,System.Int32> IL_0153: call System.Reflection.MethodBase.GetMethodFromHandle IL_0158: castclass System.Reflection.MethodInfo IL_015D: stelem.ref IL_015E: ldloc.3 // CS$0$0002 IL_015F: ldc.i4.1 IL_0160: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_QuestionId IL_0165: ldtoken <>f__AnonymousType0<System.Int32,System.Int32> IL_016A: call System.Reflection.MethodBase.GetMethodFromHandle IL_016F: castclass System.Reflection.MethodInfo IL_0174: stelem.ref IL_0175: ldloc.3 // CS$0$0002 IL_0176: call System.Linq.Expressions.Expression.New IL_017B: ldc.i4.1 IL_017C: newarr System.Linq.Expressions.ParameterExpression IL_0181: stloc.s 04 // CS$0$0003 IL_0183: ldloc.s 04 // CS$0$0003 IL_0185: ldc.i4.0 IL_0186: ldloc.1 // CS$0$0000 IL_0187: stelem.ref IL_0188: ldloc.s 04 // CS$0$0003 IL_018A: call System.Linq.Expressions.Expression.Lambda IL_018F: ldtoken Survey.Model.Participant IL_0194: call System.Type.GetTypeFromHandle IL_0199: ldstr "p" IL_019E: call System.Linq.Expressions.Expression.Parameter IL_01A3: stloc.1 // CS$0$0000 IL_01A4: ldtoken Survey.Model.ListAnswerSelection IL_01A9: call System.Type.GetTypeFromHandle IL_01AE: ldstr "row" IL_01B3: call System.Linq.Expressions.Expression.Parameter IL_01B8: stloc.s 05 // CS$0$0004 IL_01BA: ldtoken <>f__AnonymousType1<System.String>..ctor IL_01BF: ldtoken <>f__AnonymousType1<System.String> IL_01C4: call System.Reflection.MethodBase.GetMethodFromHandle IL_01C9: castclass System.Reflection.ConstructorInfo IL_01CE: ldc.i4.1 IL_01CF: newarr System.Linq.Expressions.Expression IL_01D4: stloc.2 // CS$0$0001 IL_01D5: ldloc.2 // CS$0$0001 IL_01D6: ldc.i4.0 IL_01D7: ldloc.s 05 // CS$0$0004 IL_01D9: ldtoken Survey.Model.ListAnswerSelection.get_NumericValue IL_01DE: call System.Reflection.MethodBase.GetMethodFromHandle IL_01E3: castclass System.Reflection.MethodInfo IL_01E8: call System.Linq.Expressions.Expression.Property IL_01ED: ldtoken System.Int32.ToString IL_01F2: call System.Reflection.MethodBase.GetMethodFromHandle IL_01F7: castclass System.Reflection.MethodInfo IL_01FC: ldc.i4.0 IL_01FD: newarr System.Linq.Expressions.Expression IL_0202: call System.Linq.Expressions.Expression.Call IL_0207: stelem.ref IL_0208: ldloc.2 // CS$0$0001 IL_0209: ldc.i4.1 IL_020A: newarr System.Reflection.MethodInfo IL_020F: stloc.3 // CS$0$0002 IL_0210: ldloc.3 // CS$0$0002 IL_0211: ldc.i4.0 IL_0212: ldtoken <>f__AnonymousType1<System.String>.get_V IL_0217: ldtoken <>f__AnonymousType1<System.String> IL_021C: call System.Reflection.MethodBase.GetMethodFromHandle IL_0221: castclass System.Reflection.MethodInfo IL_0226: stelem.ref IL_0227: ldloc.3 // CS$0$0002 IL_0228: call System.Linq.Expressions.Expression.New IL_022D: ldc.i4.2 IL_022E: newarr System.Linq.Expressions.ParameterExpression IL_0233: stloc.s 04 // CS$0$0003 IL_0235: ldloc.s 04 // CS$0$0003 IL_0237: ldc.i4.0 IL_0238: ldloc.1 // CS$0$0000 IL_0239: stelem.ref IL_023A: ldloc.s 04 // CS$0$0003 IL_023C: ldc.i4.1 IL_023D: ldloc.s 05 // CS$0$0004 IL_023F: stelem.ref IL_0240: ldloc.s 04 // CS$0$0003 IL_0242: call System.Linq.Expressions.Expression.Lambda IL_0247: call System.Linq.Queryable.Join IL_024C: call System.Linq.Enumerable.ToList IL_0251: ldsfld UserQuery.CS$<>9__CachedAnonymousMethodDelegate2 IL_0256: brtrue.s IL_026B IL_0258: ldnull IL_0259: ldftn UserQuery.<Main>b__1 IL_025F: newobj System.Func<<>f__AnonymousType1<System.String>,<>f__AnonymousType2<System.String>>..ctor IL_0264: stsfld UserQuery.CS$<>9__CachedAnonymousMethodDelegate2 IL_0269: br.s IL_026B IL_026B: ldsfld UserQuery.CS$<>9__CachedAnonymousMethodDelegate2 IL_0270: call System.Linq.Enumerable.Select IL_0275: call System.Linq.Enumerable.Count IL_027A: ldstr "Results" IL_027F: call LINQPad.Extensions.Dump IL_0284: pop IL_0285: ret <Main>b__1: IL_0000: ldarg.0 IL_0001: callvirt <>f__AnonymousType1<System.String>.get_V IL_0006: newobj <>f__AnonymousType2<System.String>..ctor IL_000B: stloc.0 // CS$1$0000 IL_000C: br.s IL_000E IL_000E: ldloc.0 // CS$1$0000 IL_000F: ret <>f__AnonymousType1`1.get_V: IL_0000: ldarg.0 IL_0001: ldfld 16 00 00 0A IL_0006: stloc.0 IL_0007: br.s IL_0009 IL_0009: ldloc.0 IL_000A: ret <>f__AnonymousType1`1.ToString: IL_0000: newobj System.Text.StringBuilder..ctor IL_0005: stloc.0 IL_0006: ldloc.0 IL_0007: ldstr "{ V = " IL_000C: callvirt System.Text.StringBuilder.Append IL_0011: pop IL_0012: ldloc.0 IL_0013: ldarg.0 IL_0014: ldfld 16 00 00 0A IL_0019: Box 02 00 00 1B IL_001E: callvirt System.Text.StringBuilder.Append IL_0023: pop IL_0024: ldloc.0 IL_0025: ldstr " }" IL_002A: callvirt System.Text.StringBuilder.Append IL_002F: pop IL_0030: ldloc.0 IL_0031: callvirt System.Object.ToString IL_0036: stloc.1 IL_0037: br.s IL_0039 IL_0039: ldloc.1 IL_003A: ret <>f__AnonymousType1`1.Equals: IL_0000: ldarg.1 IL_0001: isinst 06 00 00 1B IL_0006: stloc.0 IL_0007: ldloc.0 IL_0008: brfalse.s IL_0022 IL_000A: call 10 00 00 0A IL_000F: ldarg.0 IL_0010: ldfld 16 00 00 0A IL_0015: ldloc.0 IL_0016: ldfld 16 00 00 0A IL_001B: callvirt 11 00 00 0A IL_0020: br.s IL_0023 IL_0022: ldc.i4.0 IL_0023: nop IL_0024: stloc.1 IL_0025: br.s IL_0027 IL_0027: ldloc.1 IL_0028: ret <>f__AnonymousType1`1.GetHashCode: IL_0000: ldc.i4 8B 15 0F EC IL_0005: stloc.0 IL_0006: ldc.i4 29 55 55 A5 IL_000B: ldloc.0 IL_000C: mul IL_000D: call 10 00 00 0A IL_0012: ldarg.0 IL_0013: ldfld 16 00 00 0A IL_0018: callvirt 14 00 00 0A IL_001D: add IL_001E: stloc.0 IL_001F: ldloc.0 IL_0020: stloc.1 IL_0021: br.s IL_0023 IL_0023: ldloc.1 IL_0024: ret <>f__AnonymousType1`1..ctor: IL_0000: ldarg.0 IL_0001: call System.Object..ctor IL_0006: ldarg.0 IL_0007: ldarg.1 IL_0008: stfld 16 00 00 0A IL_000D: ret <>f__AnonymousType2`1.get_R: IL_0000: ldarg.0 IL_0001: ldfld 17 00 00 0A IL_0006: stloc.0 IL_0007: br.s IL_0009 IL_0009: ldloc.0 IL_000A: ret <>f__AnonymousType2`1.ToString: IL_0000: newobj System.Text.StringBuilder..ctor IL_0005: stloc.0 IL_0006: ldloc.0 IL_0007: ldstr "{ R = " IL_000C: callvirt System.Text.StringBuilder.Append IL_0011: pop IL_0012: ldloc.0 IL_0013: ldarg.0 IL_0014: ldfld 17 00 00 0A IL_0019: Box 02 00 00 1B IL_001E: callvirt System.Text.StringBuilder.Append IL_0023: pop IL_0024: ldloc.0 IL_0025: ldstr " }" IL_002A: callvirt System.Text.StringBuilder.Append IL_002F: pop IL_0030: ldloc.0 IL_0031: callvirt System.Object.ToString IL_0036: stloc.1 IL_0037: br.s IL_0039 IL_0039: ldloc.1 IL_003A: ret <>f__AnonymousType2`1.Equals: IL_0000: ldarg.1 IL_0001: isinst 07 00 00 1B IL_0006: stloc.0 IL_0007: ldloc.0 IL_0008: brfalse.s IL_0022 IL_000A: call 10 00 00 0A IL_000F: ldarg.0 IL_0010: ldfld 17 00 00 0A IL_0015: ldloc.0 IL_0016: ldfld 17 00 00 0A IL_001B: callvirt 11 00 00 0A IL_0020: br.s IL_0023 IL_0022: ldc.i4.0 IL_0023: nop IL_0024: stloc.1 IL_0025: br.s IL_0027 IL_0027: ldloc.1 IL_0028: ret <>f__AnonymousType2`1.GetHashCode: IL_0000: ldc.i4 21 74 32 1C IL_0005: stloc.0 IL_0006: ldc.i4 29 55 55 A5 IL_000B: ldloc.0 IL_000C: mul IL_000D: call 10 00 00 0A IL_0012: ldarg.0 IL_0013: ldfld 17 00 00 0A IL_0018: callvirt 14 00 00 0A IL_001D: add IL_001E: stloc.0 IL_001F: ldloc.0 IL_0020: stloc.1 IL_0021: br.s IL_0023 IL_0023: ldloc.1 IL_0024: ret <>f__AnonymousType2`1..ctor: IL_0000: ldarg.0 IL_0001: call System.Object..ctor IL_0006: ldarg.0 IL_0007: ldarg.1 IL_0008: stfld 17 00 00 0A IL_000D: ret
SELECT
[Extent1].[Id] AS [Id],CAST( [Extent2].[NumericValue] AS nvarchar(max)) AS [C1]
FROM [dbo].[Participants] AS [Extent1]
INNER JOIN [dbo].[ListAnswerSelections] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[ListAnswer_RelatedParticipantId]) AND (434 = [Extent2].[ListAnswer_RelatedQuestionId])
-- Executing at 08-Oct-15 3:26:59 PM +03:00
-- Completed in 661 ms with result: sqlDataReader
什么是实体框架(版本=“6.1.0.133”)一直在做什么？
对LINQ查询的一点改动,即在select部分中删除ToString()调用,使其执行749 ms：
var _context = new SurveyContext();
_context.Database.Log = Console.WriteLine;
(from p in _context.Participants
join row in _context.ListAnswerSelections
on new {p.Id,QuestionId = row.RelatedQuestionId}
select new { V = row.NumericValue })
.ToList()
.Select(x => new { R = x.V.ToString() })
.Count()
.Dump("Results");
SELECT [Extent1].[Id] AS [Id],[Extent2].[NumericValue] AS [NumericValue] FROM [dbo].[Participants] AS [Extent1] INNER JOIN [dbo].[ListAnswerSelections] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[ListAnswer_RelatedParticipantId]) AND (434 = [Extent2].[ListAnswer_RelatedQuestionId]) -- Executing at 08-Oct-15 3:33:33 PM +03:00 -- Completed in 367 ms with result: sqlDataReader
我可以看到,转换为字符串会降低sql执行性能,降低1.8倍.但EF的性能下降了11.2倍.为什么会这样？如果我真的需要在查询的选择部分中使用ToString(),我该如何避免这种性能损失？
解决方法
所以我通过AdventureWorks上的LINQPad运行以下两个表达式来测试一个理论：
from p in Persons
join row in BusinessEntityAddresses
on new {p.BusinessEntityID} equals new {row.BusinessEntityID}
select new { V = row.ModifiedDate }
和
from p in Persons
join row in BusinessEntityAddresses
on new {p.BusinessEntityID} equals new {row.BusinessEntityID}
select new { V = row.ModifiedDate.ToString() }
两者都有一些反思：
IL_017D: ldtoken <>f__AnonymousType1<System.String>.get_V IL_0182: ldtoken <>f__AnonymousType1<System.String> IL_0187: call System.Reflection.MethodBase.GetMethodFromHandle IL_018C: castclass System.Reflection.MethodInfo
但是选择new … .ToString()的版本生成了几个额外的反射调用：
IL_014E: call System.Reflection.FieldInfo.GetFieldFromHandle IL_0153: call System.Linq.Expressions.Expression.Field IL_0158: ldtoken System.Object.ToString IL_015D: call System.Reflection.MethodBase.GetMethodFromHandle IL_0162: castclass System.Reflection.MethodInfo IL_0167: ldc.i4.0 IL_0168: newarr System.Linq.Expressions.Expression IL_016D: call System.Linq.Expressions.Expression.Call IL_0172: stelem.ref IL_0173: ldloc.1 // CS$0$0001 IL_0174: ldc.i4.1 IL_0175: newarr System.Reflection.MethodInfo IL_017A: stloc.2 // CS$0$0002 IL_017B: ldloc.2 // CS$0$0002 IL_017C: ldc.i4.0
似乎在LINQ查询中调用.ToString会导致每个条目的额外(重复)反射开销.之后调用它时,编译器知道如何内联反射以更有效地获取.ToString.
看起来最重要的是你应该避免在LINQ查询中调用方法,因为额外的反射开销(GetMethodFromHandle,MethodInfo)会以你不期望的方式进入查询.
