运行SqlQuery EF时FieldNameLookup.GetOrdinal中的索引超出范围异常 [英] Index out of range exception in FieldNameLookup.GetOrdinal when running SqlQuery EF

问题描述

在运行如下代码时，我们最终会得到一个 IndexOutOfRange 异常:

We are getting eventually an IndexOutOfRange exception when running code like this:

result.Data = dbOptima.Database.ExecuteStoredProcedure(
    task,
    StoredProcedureValues.PROC_GET_TASKS).ToList();

，其中 ExecuteStoredProcedure 执行以下操作:

public static IEnumerable<TResult> ExecuteStoredProcedure<TResult>(this Database database, IStoredProcedure<TResult> procedure, string procedureName)
{
    var parameters = CreateSqlParametersFromProperties(procedure);
    var format = CreateSPCommand<TResult>(parameters, procedureName);

    return database.SqlQuery<TResult>(format, parameters.Cast<object>).ToArray());
}

我们无法在本地重现该问题，但是使用 Application Insights 可以非常频繁地注册该异常.以下是调用堆栈摘录:

We cannot reproduce the problem locally, but using Application Insights the exception is registered quite often. The following is a call stack extract:

System.IndexOutOfRangeException:
   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal (System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.TryGetColumnOrdinalFromReader (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndClrType (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext.InternalTranslate (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext+<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64 (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext+<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63 (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute (EntityFramework.SqlServer, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext (EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Collections.Generic.List`1..ctor (mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
   at System.Linq.Enumerable.ToList (System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)

我们尝试通过以下方式在本地重现该问题:

We've tried to reproduce the problem locally by:

• 使存储过程失败.
• 使存储过程返回0个结果.
• 通过重命名某些模型属性来导致映射问题.

没有成功.

通常，此错误是由于使用 ADO.NET 时数据读取器中的映射错误而发生的，但并非如此，因为我们使用 EF6 ，而且这种情况并非总是会发生，因此我们无法真正找到问题所在.

Typically, this error happens due to a mapping error in the datareader when you use ADO.NET, but this is not the case since we use EF 6 and it does not happen always, so we cannot really find where the problem is.

推荐答案

问题与将存储过程的结果与声明为要绑定的模型绑定在一起有关存储过程的输出.

The problem is related to binding the result of the stored procedure with the model declared to be bound with the output of the stored procedure.

如果存储过程检索的列多于模型所具有的属性，即使满足模型要求(因为所有属性都可以与存储过程输出的列进行匹配)，内部也会引发异常，原因是模型没有没有存储某些存储过程输出值的属性.

If the stored procedure retrieves more columns than the properties that the model has, even though the model is satisfied (because all the properties can be matched with columns of the stored procedure output), internally an exception is raised cause the model doesn't have properties to store some of the store procedure output values.

因此，可以通过两种方式解决此问题:

So, this can be fixed in two ways: either

• 在模型中添加属性以包含存储的列程序，或者
• 删除存储过程输出中不必要的列.

第二种方法通常更好，因为此问题表明不再需要存储过程返回的某些数据，因此，删除存储过程中不需要的代码会更干净，性能更高.

The second approach is normally better, since this issue is making evident that some data returned by the stored procedure is no longer required, so it would be cleaner and more performant to remove the unneeded code in the stored procedure.

这篇关于运行SqlQuery EF时FieldNameLookup.GetOrdinal中的索引超出范围异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！