dotnet / dotnet/EntityFramework.Docs

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

Open
#4,558 0 comments 0 reactions 0 assignees View on GitHub
area-save-changes
Dominant language
Mermaid
Stars
1.7k
Forks
2k
Avg merge
7d 23h
Merged PRs (30d)
16

Description

I have this data model:

```csharp
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:

```tsql
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 .

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.