add document and Improve error messages of using "instead of insert trigger"

[inlineHamed]

I have this data model:

class Student { public string Name { get;set; }  }

class StudentAssignment {
   public Student Student  { get;set; }

   [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
   [Required]
   public string StudentName  { get;set; }
}

I want StudentName to be set using db triggers and as it's not nullable, I must set value of it before insert. So I ended up using "instead of insert trigger" like this:

CREATE TRIGGER StudentAssignmentTrigger on StudentAssignment
INSTEAD OF INSERT
AS
BEGIN
	select * into #tmp from inserted;
	alter table #tmp drop column Id

	UPDATE tmp set tmp.StudentName=s.Name from #tmp tmp join Student s on tmp.StudentId=s.Id
	insert into StudentAssignment select * from #tmp;
	select [Id], [StudentName] from StudentAssignment where @@ROWCOUNT=1 and id = scope_identity()
	drop table #tmp
END
GO

In this scenario I had to handle two errors:

1. ef was expecting a select statement to be right after the insert statement, otherwise an error like this occured: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded I found the solution is adding the select statement right after the insert one.

2. the next error was because of column list of the select statement. It turned out It should be the exact list of computed columns of table StudentAssignment, otherwise a strange error will be raised:

Details:
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> System.IndexOutOfRangeException: Index was outside the bounds of the array.
at Microsoft.Data.SqlClient.SqlDataReader.CheckHeaderIsReady(Int32 columnIndex, Boolean permitAsync, String methodName)
at Microsoft.Data.SqlClient.SqlDataReader.IsDBNull(Int32 i)
at lambda_method1590(Closure , DbDataReader )
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagationAsync(Int32 commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

I think this scenario can be documented and the error messages (specially the second one) can be more specific .