SqlQuery cause IndexOutOfRangeExeception

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
jones
Posts: 1
Joined: Fri 08 Apr 2016 11:09

SqlQuery cause IndexOutOfRangeExeception

Post by jones » Tue 10 Oct 2017 06:46

When using DbContext.DataBase.SqlQuery to get a custom object (not entity), it seems all the object's writable properties should be found in the sql query result columns or the IndexOutOfRangeException will be thrown.

For example, if the object class is defined as:

Code: Select all

class User
{
   string Name {get; private set;}
   int Age {get; private set;}
}
and I invoke the sql query api as below to get the User list:

Code: Select all

var users = MyDbContext.DataBase.SqlQuery<User>("select Name, Age form UserTable").ToList()
It works and a users list was returned. However, if the User class was defined with an extra field that doesn't queried in the sql, for example:

Code: Select all

class User
{
   string Name {get; private set;}
   int Age {get; private set;}
   string EMail {get; private set;}
}
Because the extra field, EMail, was not one of the column of the above sql, it will throws an IndexOutOfRangeException as ToList was called. I don't think this is by design because extra properties for a SQL is a common case when making this kind of object converting. Here is the stack trace for your convenience.

Code: Select all

   Devart.Data.SQLite.SQLiteDataReader.a(Int32 A_0)
   Devart.Data.SQLite.SQLiteDataReader.GetName(Int32 i)
 System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndClrType(DbDataReader reader, Type type, MetadataWorkspace workspace)
   System.Data.Entity.Core.Objects.ObjectContext.InternalTranslate[TElement](DbDataReader reader, String entitySetName, MergeOption mergeOption, Boolean streaming, EntitySet& entitySet, TypeUsage& edmType)
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64()
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63()
   System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)
   System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14`1.<ExecuteSqlQuery>b__13()
   System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   ...

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SqlQuery cause IndexOutOfRangeExeception

Post by Shalex » Thu 12 Oct 2017 11:14

jones wrote:

Code: Select all

[...]
System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndClrType(DbDataReader reader, Type type, MetadataWorkspace workspace)
[...]
This behavior is designed by EF engine: result set returned from database is mapped to CLR type.

As a workaround, implement inheritance and use the appropriate class for materialization:

Code: Select all

class BaseUser
{
    string Name {get; private set;}
    int Age {get; private set;}
}

class UserWithEmail: BaseUser
{
    string EMail {get; private set;}
}

Post Reply