search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.53k stars 3.13k forks source link

Stored procs with TPH doesn't work with multiple subtypes #28805

Closed ajcvickers closed 1 year ago

ajcvickers commented 1 year ago

Start with sample here: https://github.com/dotnet/EntityFramework.Docs/pull/3989

Change Program.cs to run StoredProcedureMappingSample.Inset_Update_and_Delete_using_stored_procedures_with_TPH();

fail: 8/20/2022 21:47:02.279 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)
      Failed executing DbCommand (24ms) [Parameters=[@p0='Book' (Nullable = false) (Size = 4000), @p1='Extreme Programming Explained' (Nullable = false) (Size = 4000), @p2='190', @p3='2000-01-01T00:00:00.0000000', @p4=NULL (Size = 8000) (DbType = Binary), @p5='201-61641-6' (Size = 4000), @p6='Book' (Nullable =
false) (Size = 4000), @p7='Java Puzzlers' (Nullable = false) (Size = 4000), @p8='283', @p9='2005-01-01T00:00:00.0000000', @p10=NULL (Size = 8000) (DbType = Binary), @p11='0-321-33678-X' (Size = 4000), @p12='Book' (Nullable = false) (Size = 4000), @p13='Effective Java' (Nullable = false) (Size = 4000), @p14='252
', @p15='2001-01-01T00:00:00.0000000', @p16='0x010203040506070809' (Size = 8000), @p17='0-201-31005-8' (Size = 4000), @p18='Book' (Nullable = false) (Size = 4000), @p19='Test-Driven Development By Example' (Nullable = false) (Size = 4000), @p20='220', @p21='2003-01-01T00:00:00.0000000', @p22=NULL (Size = 8000)
(DbType = Binary), @p23='0-321-14653-0' (Size = 4000), @p24='Kent Beck' (Nullable = false) (Size = 4000), @p25='Joshua Bloch' (Nullable = false) (Size = 4000), @p26='Neal Gafter' (Nullable = false) (Size = 4000), @p27='Simon Rockman' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      EXEC [Document_Insert] @p0, @p1, @p2, @p3, @p4, @p5;
      EXEC [Document_Insert] @p6, @p7, @p8, @p9, @p10, @p11;
      EXEC [Document_Insert] @p12, @p13, @p14, @p15, @p16, @p17;
      EXEC [Document_Insert] @p18, @p19, @p20, @p21, @p22, @p23;
      EXEC [Person_Insert] @p24;
      EXEC [Person_Insert] @p25;
      EXEC [Person_Insert] @p26;
      EXEC [Person_Insert] @p27;
fail: 8/20/2022 21:47:02.294 CoreEventId.SaveChangesFailed[10000] (Microsoft.EntityFrameworkCore.Update)
      An exception occurred in the database while saving changes for context type 'NewInEfCore7.TphDocumentsContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
      Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
      Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
      Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
      ClientConnectionId:c8c014cf-98e9-4691-91c7-4ed9b89ef0ae
      Error Number:201,State:4,Class:16
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.Execute(IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__107_0(DbContext _, ValueTuple`2 t)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
Procedure or function 'Document_Insert' expects parameter '@CoverPrice', which was not supplied.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
ClientConnectionId:c8c014cf-98e9-4691-91c7-4ed9b89ef0ae
Error Number:201,State:4,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__107_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at NewInEfCore7.DocumentsContext.Seed() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\DocumentsContext.cs:line 564
   at NewInEfCore7.StoredProcedureMappingSample.SprocMappingTest[TContext]() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\StoredProcedureMappingSample.cs:line 40
   at NewInEfCore7.StoredProcedureMappingSample.Inset_Update_and_Delete_using_stored_procedures_with_TPH() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\StoredProcedureMappingSample.cs:line 10
   at Program.Main() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\Program.cs:line 25
ajcvickers commented 1 year ago

@roji Can we get this fixed ASAP to unblock the stored-procs sample? Fix should target rc2.