Multiple calls to FromSql with stored procedure results in errors

[mikary]

Composing on a FromSql call to a stored procedure with another FromSql call results in errors. Calls to Include have already been disabled by throwing in RelationalEntityQueryableExpressionVisitor and it is probably appropriate to throw for additional FromSql query annotations in the same place.

[Fact]
public virtual void From_sql_queryable_with_multiple_stored_procedures()
{
    using (var context = CreateContext())
    {
        var actual
                = (from a in context.Set<MostExpensiveProduct>().FromSql(TenMostExpensiveProductsSproc)
                   from b in context.Set<MostExpensiveProduct>().FromSql(TenMostExpensiveProductsSproc)
                   where a.TenMostExpensiveProducts == b.TenMostExpensiveProducts
                   select new { a, b })
                .ToArray();
            }
}

Multiple stored produce calls produce the following:

---------------------------
Assertion Failed: Abort=Quit, Retry=Debug, Ignore=Continue
---------------------------

   at Microsoft.Data.Entity.Relational.RemappingUntypedValueBufferFactory.CreateValueBuffer(DbDataReader dataReader) in C:\src\aspnet\EntityFramework\src\EntityFramework.Relational\RemappingUntypedValueBufferFactory.cs:line 28

   at Microsoft.Data.Entity.Relational.Query.QueryingEnumerable.Enumerator.MoveNext() in C:\src\aspnet\EntityFramework\src\EntityFramework.Relational\Query\QueryingEnumerable.cs:line 87

   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()

   at System.Linq.Enumerable.<SelectManyIterator>d__1`2.MoveNext()

   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()

   at Microsoft.Data.Entity.Query.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() in C:\src\aspnet\EntityFramework\src\EntityFramework.Core\Query\LinqOperatorProvider.cs:line 134

   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)

   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

   at Microsoft.Data.Entity.Relational.Functi......

<truncated>
---------------------------
Abort   Retry   Ignore   
---------------------------

[Fact]
public virtual void From_sql_queryable_stored_procedure_and_select()
{
    using (var context = CreateContext())
    {
        var actual
            = (from mep in context.Set<MostExpensiveProduct>().FromSql(TenMostExpensiveProductsSproc)
                from p in context.Set<Product>().FromSql("SELECT * FROM Products")
                where mep.TenMostExpensiveProducts == p.ProductName
                select new { mep, p })
                .ToArray();
    }
}

Stored procedure followed by a select produces the following:

Test 'Microsoft.Data.Entity.SqlServer.FunctionalTests.FromSqlSprocQuerySqlServerTest.From_sql_queryable_select_and_stored_procedure' failed:
    System.InvalidOperationException : The required column 'ProductID' was not present in the results of a 'FromSql' operation.
    Query\Sql\RawSqlQueryGenerator.cs(104,0): at Microsoft.Data.Entity.Relational.Query.Sql.RawSqlQueryGenerator.CreateValueBufferFactory(IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader)
    Query\CommandBuilder.cs(77,0): at Microsoft.Data.Entity.Relational.Query.CommandBuilder.<>c__DisplayClass7_0.<NotifyReaderCreated>b__0()
    at System.Threading.LazyInitializer.EnsureInitializedCore[T](T& target, Func`1 valueFactory)
    at System.Threading.LazyInitializer.EnsureInitialized[T](T& target, Func`1 valueFactory)
    Query\CommandBuilder.cs(74,0): at Microsoft.Data.Entity.Relational.Query.CommandBuilder.NotifyReaderCreated(DbDataReader dataReader)
    Query\QueryingEnumerable.cs(81,0): at Microsoft.Data.Entity.Relational.Query.QueryingEnumerable.Enumerator.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Enumerable.<SelectManyIterator>d__1`2.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    Query\LinqOperatorProvider.cs(144,0): at Microsoft.Data.Entity.Query.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    FromSqlSprocQueryTestBase.cs(22,0): at Microsoft.Data.Entity.Relational.FunctionalTests.FromSqlSprocQueryTestBase`1.From_sql_queryable_stored_procedure_and_select()
    FromSqlSprocQuerySqlServerTest.cs(22,0): at Microsoft.Data.Entity.SqlServer.FunctionalTests.FromSqlSprocQuerySqlServerTest.From_sql_queryable_select_and_stored_procedure()

[Fact]
public virtual void From_sql_queryable_select_and_stored_procedure()
{
    using (var context = CreateContext())
    {
        var actual
            = (from p in context.Set<Product>().FromSql("SELECT * FROM Products")
                from mep in context.Set<MostExpensiveProduct>().FromSql(TenMostExpensiveProductsSproc)                       
                where mep.TenMostExpensiveProducts == p.ProductName
                select new { mep, p })
                .ToArray();
    }
}

Select followed by a stored procedure produces the following:

Test 'Microsoft.Data.Entity.SqlServer.FunctionalTests.FromSqlSprocQuerySqlServerTest.From_sql_queryable_select_and_stored_procedure' failed:
    System.Data.SqlClient.SqlException : Incorrect syntax near ')'.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.ExecuteReader()
    Query\QueryingEnumerable.cs(79,0): at Microsoft.Data.Entity.Relational.Query.QueryingEnumerable.Enumerator.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Enumerable.<SelectManyIterator>d__1`2.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    Query\LinqOperatorProvider.cs(144,0): at Microsoft.Data.Entity.Query.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    FromSqlSprocQueryTestBase.cs(47,0): at Microsoft.Data.Entity.Relational.FunctionalTests.FromSqlSprocQueryTestBase`1.From_sql_queryable_select_and_stored_procedure()
    FromSqlSprocQuerySqlServerTest.cs(22,0): at Microsoft.Data.Entity.SqlServer.FunctionalTests.FromSqlSprocQuerySqlServerTest.From_sql_queryable_select_and_stored_procedure()

[mikary]

Appears to be fixed by other changes in Query, added tests to catch any regressions in this area.

[SpilledMilkCOM]

I don't now if anybody will see this, but I'm seeing this error and I'm not exactly sure why this is happening. I'm sending in a few User Defined Table Types (with the help of NReco.Data) and as far as I can tell that is working, but when the data is returned I see this error: "Exception thrown: 'System.InvalidOperationException' in Microsoft.EntityFrameworkCore.dll

Additional information: The required column 'InventoryCapacityGroupSecondaryId' was not present in the results of a 'FromSql' operation."

That column is the first column in my resulting SELECT statement at the end of the stored procedure. That is also the first property in my data entity class. I wasn't sure how to contact anybody or post something new? Maybe the UDTT are affecting the results.

[rowanmiller]

@SpilledMilkCOM this issue is closed, please open a new issue with details of the problem you are encountering.

[HydTechie]

I don't now if anybody will see this, but I'm seeing this error and I'm not exactly sure why this is happening. I'm sending in a few User Defined Table Types (with the help of NReco.Data) and as far as I can tell that is working, but when the data is returned I see this error: "Exception thrown: 'System.InvalidOperationException' in Microsoft.EntityFrameworkCore.dll

Additional information: The required column 'InventoryCapacityGroupSecondaryId' was not present in the results of a 'FromSql' operation."

That column is the first column in my resulting SELECT statement at the end of the stored procedure. That is also the first property in my data entity class. I wasn't sure how to contact anybody or post something new? Maybe the UDTT are affecting the results.

Its saying the said primary key is not present in the list of select column, if you could add it for the sake of it...I had a weird situation, the count by group, so I could not add Pkey, stuck at that.:(