In a table, I have implemented a database trigger to populate a column with a calculated value (column is named ProjectCode). I use Web API controllers to create and retrieve entries from the database. An instance of my DbContext is injected into the controller.
When DbContext adds a new instance of the entity and then saves it, the returned data does not include the automatically created value for the column. The database does show a correct value in the column for the newly inserted row. Even on retrieving the data within the same method the property shows a value of null.
Project project = new Project();
dbContext.Projects.Add(project);
await dbContext.SaveChangesAsync();
// When placing a debugger stop here, and checking the database, I can see that the value for ProjectCode is populated correctly by the database trigger
// project.Id has valid value, however project.ProjectCode is null
Project createdProject = await dbContext.SingleOrDefaultAsync(p => p.Id == project.Id);
// createdProject is not null, however createdProject.ProjectCode is null
After the web call is completed, and the controller and DbContext are disposed, a new Web API call is made to retrieve the data. DbContext is used in identical fashion by calling
await dbContext.SingleOrDefaultAsync(p => p.Id == project.Id)
the ProjectCode then shows it is correctly populated.
Why would it not show correctly populated after adding or immediately retrieving even though the row in the database shows it’s populated?
Triggers are event based. So, those are executed asynchronously and are not blocking operation that caused its execution.
Trigger responding to insert operation is executed once the insert is completed.
I am not sure how exactly Entity Framework loads updated values, but it will be within the same transaction, thus you only read data that were provided during that transaction containing insert operation. (By default you read committed data)
You might try to reload data that DbContext hold by calling
dbContext.Entry(project).ReloadAsync();
to update values DbContext has instead of querying by its id. (It might be no different in the end, but more understandable what you are doing there).
Related info:
CREATE TRIGGER (Transact-SQL)
EntityEntry.ReloadAsync(CancellationToken) Method
1