TECHNIQUES FOR WORKING WITH DATA PART TWO

TECHNIQUES FOR WORKING WITH DATA

CREATE THE FIRST MIGRATIONS WITH EFCORE

When we are asked to implement a new feature, the need to add new columns to the database often happens. We have seen that there are two approaches, the Database-First is to connect to the database and run the scripts necessary to add a new column in a table. Using the Code-First approach first you modify the application code then with the EFCore Migrations mechanism you make the changes to the database.

Under the Migrations folder you can see the code produced by this command. It is a very normal C# class that is derived from Migrations and has two very important methods. Up and Down. Up is invoked when we need to evolve the structure of the database, and Down when we need to bring back the database before Migrations was applied.

APPLY MIGRATIONS TO THE DATABASE

The command you saw is for updating the conceptual model, when we need to apply the Migrations produced by the previous command and evolve the database structure we use this command: dotnet ef database update.

When we want the Up method to be applied to evolve the database and change its structure we use the above command. We can also have the database devolve with the same command, it all depends on the Migrations name we set. In our case we don’t have a previous Migrations to go back to, so if we want to bring the database back we have to indicate 0 as the name of the Migrations.

SECTION SUMMARY

Each DbCommand object, like the SqliteCommand, has 3 specific methods to help us get the desired result when we want to send a query or SQL command.

• ExecuteReaderAsync is mostly used when we send SQL queries to the database because it returns a data reader that we use to read information from the result sets returned by the database (rows and columns);
• ExecuteScalarAsync is also used with SQL queries but is indicated when the result set returned from the database consists of only one row and one column. Therefore, this method returns a single value that can be an integer, string, or other type. It is especially useful when we want to count the number of rows in a table or get the ID of the last inserted row;
• ExecuteNonQueryAsync is used for sending SQL commands that, typically, produce no result set. Therefore, this method returns an integer representing the number of rows affected by the command. For example, it will return 1 if we used it to send an INSERT command that ended successfully.

ADD EDIT AND DELETE ROWS

Let’s look at a brief summary of how to persist information in the database.

• With ADO.NET, it is sufficient to send the SQL commands INSERT, UPDATE and DELETE. Here are some examples showing the syntax for Sqlite but also valid for other database technologies:

1. INSERT INTO Courses (Title, Author) VALUES (‘My course’, ‘Mario Rossi’);
2. UPDATE Courses SET Title=’My new course’, Author=’Enzo Rossi’ WHERE Id=1;
3. DELETE FROM Courses WHERE Id=1;

• With Entity Framework Core we have equivalent methods such as Add and Delete that will inform the Change Tracker of our intention to want to insert or delete an entity. For change, on the other hand, no method needs to be invoked because the very moment we assign an entity property, the Change Tracker can already realize that it has undergone changes. Any changes will be persisted only when we invoke the SaveChangesAsync method of the DbContext. Here is a complete example, in which one new entity is added, one is modified, and another is deleted.

1. Course course1 = new Course(“My course,” “Mario Rossi”);
2. Course course2 = await dbContext.Courses.FindAsync(2);
3. Course course3 = await dbContext.Courses.FindAsync(3);
4. dbContext.Add(course1); //Add.
5. course2.ChangeTitle(“New Title”); //Modify
6. dbContext.Remove(course3); //Eliminate
7. await dbContext.SaveChangesAsync(); //And finally we persist the 3 entities all together

It is possible to “trick” the Change Tracker as to whether an entity should be added, changed, or deleted. We can manipulate the state of an entity at will in this way:

8. dbContext.Entry(course).State = EntityState.Deleted; //At SaveChangesAsync, the entity will be deleted

AVOID DATA LOSS WITH OPTIMISTIC COMPETITION

If several users are working on the same edit form at the same time, it may happen that one inadvertently overwrites changes made by the other, without anyone being immediately aware of it.

This can happen because after the edit form has been loaded by the browser, it will continue to display the same information even if other users have made changes in the meantime. When submitting the form, some old information may then have persisted, overwriting the more current information.

To avoid this problem:

• we add a new column called, for example, RowVersion to the database table;
• we make sure that the contents of the column are automatically regenerated each time the row is updated. Depending on the database technology used, this can be achieved with columns of a special type for this purpose or with a trigger that will be activated immediately after the row is inserted or updated;
• we introduce the RowVersion value into the form, such as in a hidden field since the value should not be shown to the user;
• When the form is submitted, we are only going to persist the data if the RowVersion value that comes to us from the form is still identical to what is on the row in the database. Otherwise, if the value is different, it means that the row has since been updated and therefore we return an error to the user asking him to reload the form and repeat the changes he had made.

TO IMPLEMENT THIS POINT:

• With ADO.NET, we simply need to involve the value of RowVersion in the WHERE clause of the UPDATE command. For example:

1. FormattableString command = $”UPDATE Courses SET Title={inputModel.Title} WHERE Id={inputModel.Id} AND RowVersion={inputModel.RowVersion}”

• With Entity Framework Core, in the OnModelCreating method of the DbContext we specify IsRowVersion() for the new property.

1. modelBuilder.Entity<Course>(entity =>
2. {
3. entity.Property(course => course.RowVersion).IsRowVersion();
4. //Here more mapping
5. }

Now, in the application service where we persist the entity, we should use the following syntax.

6. //Recovery entity
7. Course course = await dbContext.Courses.FindAsync(inputModel.Id);
8. //Add values (e.g., title).
9. course.ChangeTitle(inputModel.Title);
10. //We set the RowVersion that we received from the form
11. dbContext.Entry(course).Property(course => course.RowVersion).OriginalValue = inputModel.RowVersion;
13. //Try to persist
14. try
15. {
16. await dbContext.SaveChangesAsync();
17. }
18. catch (DbUpdateConcurrencyException)
19. {
20. //If a DbUpdateConcurrencyException occurs, we will know that the RowVersion has changed
21. //so we raise a more significant exception, which we will catch from the Controller
22. throw new OptimisticConcurrencyException();
23. }

LOGICALLY DELETE A LINE WITH SOFT-DELETE

Instead of physically deleting a row from the database, if the situation calls for it, we can instead mark the row as logically deleted. In this way, the row will continue to be present in the database and maintain all its relationships with any other dependent rows in other tables.

Our application, in extracting data from the database, will need to exclude rows marked as deleted so that they do not appear in the lists and detail pages. This strategy, which does not require the physical deletion of data, is precisely called soft-delete.

To implement it, we first need to add a new column to the database table that helps us distinguish rows marked as deleted from those that are still active. Here are ideas on how to create the column:

• It can, for example, be called Deleted and admit the values 0 or 1, where 1 indicates precisely that it has been deleted;
• Or, it could be called Status and admit various string values such as Draft, Published, and Deleted, where only Deleted indicates successful deletion while the other values still serve to control the visibility of the row in the application. For example, Draft might indicate that the row should be shown only to the user who created it, while Published would make it visible to the entire audience of users.

So when the user wants to delete a row, the application will simply have to update the value of the new column we have added to the table. For example, the Deleted column, if any, should be set to 1 or the Status column set to Deleted.

The trickiest part is then to make sure that the application excludes all rows marked as deleted.

• With ADO.NET, we will necessarily have to add WHERE Status<>’Deleted’ to each query or SQL command sent by our application. We have to be very careful because it is easy to forget, especially when we write a new query or command. To limit this risk, we could
  create a view
  in the database, so that the filter is applied centrally. Not all database technologies allow the creation of updatable views, so we will have to continue to integrate the WHERE clause at least for UPDATE and DELETE commands;
• With Entity Framework Core it is much easier because we can set up a Global Query Filter that will be automatically applied to every LINQ query we address to the EntitySet. The filter should be specified in the OnModelCreating method of the DbContext, using the HasQueryFilter method, as seen in the following example.

1. modelBuilder.Entity<Course>(entity =>
2. {
3. entity.HasQueryFilter(course => course.Status != CourseStatus.Deleted);
4. //Here more mapping
5. }

UPDATE THE DATABASE STRUCTURE WITH MIGRATIONS

When we want to evolve the database structure, such as to add or change a table or column, we can do so by taking advantage of migration, a feature offered by Entity Framework Core.

Using migration leads us to follow the code-first approach. This is the sequence of activities that we will be engaged in:

1. First we modify the conceptual model code, such as adding a new property to the Course class or changing its mapping in the DbContext;
2. Then we add a migration to the project by running the dotnet command ef migrations add NameMigration. A new migration will be automatically generated describing the changes made in step 1;
3. Finally, we run the dotnet ef database update command to ask Entity Framework Core to apply the migration i.e., modify the database structure in a manner consistent with what the migration describes.

A migration is a C# class that contains Up and Down methods. Both of these methods define a parameter of type MigrationBuilder that is used to describe the operations to be performed against the database, such as adding a column or creating a table. It can also be used to send arbitrary SQL commands.

• The Up method is used to evolve the database structure and is invoked by Entity Framework Core when we apply migration. Tables and columns are usually created here;
• The Down method is used to devolve the database structure, that is, it is invoked when we want to undo the migration after it has been applied. It is usually used to delete tables and columns, so it can result in loss of data. Useful when we discover that there is a problem in one of the last applied migration and want to revert to a previous structure, then reapply it after correcting it.

Entity Framework Core very strictly respects the order in which we added the migrations to the project. One always follows the other, like train or subway stations.

For this we can evolve or devolve the database structure back and forth along this one direction using the dotnet ef database update command, which also allows us to name a target migration. Assuming that no migration has yet been applied to the database, here are the effects produced by the following commands:

• dotnet ef database update all migration is applied, so the Up methods of each migration will be invoked, in the strict order shown in the image, from left to right. Thus, the last migration applied is LessonVersion;
• dotnet ef database update LessonOrder the database structure devolves to return to the LessonOrder migration, so LessonVersion‘s Down method will be invoked;
• dotnet ef database update 0 the Down methods of each applied migration (the first four) are invoked from right to left, so that the database returns to its original state, i.e., devoid of any tables;
• dotnet ef database update UniqueCourseTitle the Up method of the InitialMigration is invoked and then the Up method of the UniqueCourseTitle is invoked.

If we want to remove the last migration created from the project, we run the dotnet ef migrations remove command. Only the last migration can be deleted, as long as it has not been applied to the database. It is important NOT to delete migration .cs files from the project by hand, but always use the commands provided.

LINK TO CODE ON GITHUB

GITHUB

Download the section16 code or the master branch or clone the GITHUB repository to have all sections available in your favorite editor.