14 Different database types and EF Core services

This chapter starts with the differences you might encounter in the range of relational databases that EF Core supports. To bring this to life, you’ll convert our book app from using SQL Server to the MySQL database to see what changes. I make that application available in the Git repo branch Chapter14MySql.

You’ll then look at the Command Query Responsibility Segregation (CQRS) architecture discussed at the end of the preceding chapter (see section 13.6). We’ll spend quite a bit of time on this, as it’s a useful architecture and its implementation highlights advanced features inside EF Core. You’ll also add a NoSQL database to the mix to end up with a high-performance version of our original book-selling site application. It might not challenge Amazon, but it’s still pretty fast for a single-instance ASP.NET Core application. This application is available on the book’s Git repo branch Chapter14 as well as on a live site at http://cqrsravendb.efcoreinaction.com/.

The end of this chapter goes deeper into EF Core and looks at its internal services and what you can do with them. The EF Core team has designed these services to allow you to alter the way EF Core works inside. This is advanced stuff, but it’s worth knowing about in case your project could benefit from it.

14.1 What differences do other database server types bring?

In most of this book, you’ve used an SQL Server database, but what happens if you want to use a different type of database server? EF Core has multiple database providers that access a range of database servers, and that list will grow over time. So, the question is, does anything change with different database types and providers?

You’ll tackle this wide-ranging question with a worked example: you’ll convert our book app from using an SQL Server database to a MySQL database. Typically, you don’t change databases of an application; you just have to get your application working with the database of your choice. But it’s instructive to see what changes when you swap database types, because it gives you an idea of the sorts of issues you may encounter when using different database types.

To make the example a bit harder, you’ll convert the performance-tuned version of the book app (see section 13.2), which has an SQL UDF in it. That brings in the complication of raw SQL code that you add to the database outside EF Core, and this does create some issues.

I’ve chosen a MySQL database because it’s a well-known database with a community version that’s available under the GPL license on many platforms. You can run the application locally by downloading a MySQL server; I give full details on how to do that next. Following that, this section covers the following topics:

I use the MySQL EF Core database provider Pomelo.EntityFrameworkCore.MySql in my application. There’s another MySQL database provider, MySql.Data.EntityFrameworkCore, but when I was building my application, that database provider didn’t support EF Core 2.0; it’s worth checking out, though.

14.1.1 Creating an instance of the application’s DbContext for MySQL

The first thing you need is to be able to create an instance of the application’s DbContext that accesses a MySQL database rather than an SQL Server database. Section 2.2.2 showed how to create an instance of the application’s DbContext with SQL Server, and this listing shows the same code, but with the changes needed to use a MySQL database shown in bold. In this case I show the connection string as a constant, but in the ASP.NET Core-based book app you would need to update the connection string in the appsetting.json/appsettings.Development.json file.

As you can see, there aren’t a lot of changes—just the connection string and changing the UseSqlServer method to the UseMySql method. You have to install the EF Core MySQL database provider NuGet package Pomelo.EntityFrameworkCore.MySql to get access to the UseMySql method, and a MySQL database.

14.1.2 What you have to do to convert the SQL Server application to MySQL

Although the changes to create an instance of the application’s DbContext are small, other changes are necessary to make the application work. This section lists all the changes required to make the Chapter13-Part1 branch version of the book app, which used SQL Server, now work with MySQL. I’ve split these into significant changes and housekeeping changes.

Significant changes needed to convert from Chapter13-Part1 version to use MySQL

The significant changes required in the Chapter13-Part1 application are related to migrations and the raw SQL in the application, as detailed here:

The small, housekeeping changes needed to swap to MySQL database provider

You need to make minor changes to make the Chapter13-Part1 application work with the MySQL database provider. They’re trivial, but the application isn’t going to work without them.

The first change is to the DefaultConnection string in ASP.NET Core’s appsetting.json file. When running the application locally for development, the connection string must be in the correct format to access the local MySQL database (see listing 14.1). If you deploy the application to a web host, you need to provide the correct connection string during the publish process to access the hosted MySQL database (see section 5.4.1).

You also need to alter ASP.NET Core’s ConfigureServices method in the Startup class, where the application’s DbContext is registered as a service. You replace the UseSqlServer method with the UseMySql method. That requires you to add the MySQL database provider package to your ASP.NET Core project.

14.1.3 Looking at other database server types and differences

The two key issues when looking at a database to use with EF Core are as follows:

Looking at the database features first, mostly minor differences exist in SQL syntax or features. The SQLite database has the biggest number of feature limitations (it does have the suffix lite), but most other database servers provide good coverage of all SQL features that EF Core uses.

Typical of a minor database difference is MySQL’s requirement that the EF Core’s concurrency timestamp (see section 8.7.2) must be of the .NET type DateTime, rather than the byte[] in SQL Server, whereas a PostgreSQL database uses a column called xmin (see http://mng.bz/5zB9). I’m sure that lots of subtle EF Core issues exist in various databases, because each database server works in a slightly different way.

The quality of the EF Core database provider and the level of support it provides is also another part of the equation. Writing a database provider for EF Core is a nontrivial task, and the SQL Server database provider written by the EF Core team is the gold standard. You should test any database provider to ensure that it works for you. When I started using the Pomelo.EntityFrameworkCore.MySql database provider, I found a problem, and when I raised an issue on the Pomelo Foundation EF Core GitHub issue page, I got a workaround in 24 hours—which I thought was a good result.

14.1.4 Summarizing EF Core’s ability to work with multiple database types

Doing this database swap, plus a bit of work with PostgreSQL, shows me that EF Core and its database providers do an excellent job of handling various database types. The only problems I had during the conversion from SQL Server to MySQL were the differences in how each database server worked. EF Core can also produce database migrations specifically for each database type (see section 11.2.1, subsection “Migrations are database-provider specific”), which is another help for developers who don’t know the SQL language well.

14.2 Developing a CQRS architecture application with EF Core

Having talked about various databases, I now want to talk about a solution that combines a relational database handled by EF Core with a NoSQL database. This comes about from my suggestion in section 13.6 that a CQRS architecture using a polyglot database structure would provide better scalability performance.

The CQRS architecture acknowledges that the read side of an application is different from the write side. Reads are often complicated, drawing in data from multiple places, whereas the write side is often much simpler. You can see in the example application that listing the books is complex, but adding a review is fairly trivial. Separating the code for each part can help you focus on the specific features of each part; this is another application of the SoC software principle.

In chapter 13, you produced the performance version, in which you cached values (see section 13.4). It struck me then that the final query didn’t access any relationships and could be stored in a simpler database, such as a NoSQL database. In this example, you’ll use a polyglot database structure, with a mixture of SQL and NoSQL databases, for the following reasons:

The result of these design inputs means you’ll develop what I refer to as a two-database CQRS architecture, as shown in figure 14.1.

Using two databases is a logical step with the CQRS architecture. It brings potential performance gains for reads, but a performance cost on writes. This makes the two-database CQRS architecture appropriate when your business application has more reads of the data than writes. Many business applications have more reads than writes (e-commerce applications are a good example), so this architecture fits our book app well.

14.2.1 Implementation of a two-database CQRS architecture application

You want to move only the book list view data to the read-side database, and not do this for the order-processing part, because only the book list view has a performance issue. It turns out that although adding CQRS does require a fair amount of work, it’s simple to apply the CQRS architecture to only part of our application. Figure 14.2 shows the design of our changed book application, with the book list implemented as a two-database CQRS part.

The other part to change is the BookListService class in the ServiceLayer. This class handles the book list, and you change it to access the NoSQL database instead of the SQL database. I selected the RavenDB NoSQL database, which has a community version of its database server that you can run locally. There’s also a .NET package that supports LINQ commands, so the LINQ built for EF Core works directly with RavenDB.

I don’t cover the RavenDB database access code because it’s outside the scope of this book. Visit https://ravendb.net/ for documentation, or the GitHub documentation site at https://github.com/ravendb/docs/, which includes sample code.

In addition to being a high-performance combination, the implementation of this architecture reveals advanced aspects of the way EF Core works. The following are the points covered in the next few subsections:

14.2.2 How the parts of the CQRS solution interact with each other

When updating an existing application for performance reasons, you need to be careful not to break the application in the process. The book-selling site isn’t that complicated, but you still need to be careful when you modify the application over to a CQRS architecture. You therefore want a design that minimizes the changes and isolates the new parts.

I came up with a design that keeps all the NoSQL/RavenDB parts separate. In this final design, the EF Core doesn’t know, or care, what database is being used for the read-side part of the CQRS system. This makes the update simpler, plus offers the possibility of changing the NoSQL database used. I like RavenDB, with its support of LINQ, but EF Core version 2.1 previews Azure’s NoSQL database, Cosmos, which might be an interesting alternative.

Keeping as much of the new database code in the NoSqlDataLayer, and using interfaces, keeps the impact of the changes to a minimum. Figure 14.3 shows how to hide the NoSQL code behind interfaces to keep that code isolated. You use dependency injection to provide both the DataLayer and the ServiceLayer with methods that allow access to the database.

The changes from the existing, SQL-based book app are as follows:

The core of this NoSQL implementation is a class I call RavenStore (see listing 14.2). RavenDB requires a single instance of the RavenDB’s IDocumentStore, which is set up on the application’s start. This RavenStore class provides two methods: one for the DataLayer to get a class for writing to the read-side database, and one for the ServiceLayer to get a class to allow reading of the read-side database.

The INoSqlCreators interface is used by the DataLayer to get the method to update the read-side database, and by the ServiceLayer to gain access to the read-side for querying. You need to register a single RavenStore instance with ASP.NET Core’s dependency injection service as the service to be accessed via the INoSqlCreators interface. The following listing shows the section of code in the ConfigureServices method in the Startup class that registers the RavenStore as a singleton, which provides the service INoSqlCreators.

The listing shows part of the Raven implementation of the INoSqlUpdater interface, which the DataLayer would use to update the read-side database. This gives you some idea of how this works.

Now that you’ve seen the code that’ll update the read-side database, the other major part of the CQRS implementation is in how the DataLayer detects changes to the SQL database, which will alter the NoSQL book list view. I describe this next.

14.2.3 Finding book view changes—Part 1, finding the correct state and key

As explained in section 14.2.2, you override the SaveChange methods (sync and async) in the application’s DbContext and add code to find changes that will affect the book list view. This turns out to be quite complex; I solved it only by understanding how EF Core works underneath. I think this learning is useful outside the CQRS situation, so in this section I explain how EF Core handles the setting of the foreign keys by looking at the navigational properties.

For this example, you’ll add a new Book entity instance, with one new Review entity instance attached to it via the Book’s Reviews navigational property. This is the simplest example that shows all the stages that EF Core goes through. Table 14.1 shows the value of the State of the Book entity in the Book’s State column after the code in column 1 has run. The other two columns, Book’s BookId and Review’s BookId, show the value of BookId property of the Book entity, and BookId of the Review entity, respectively, after the code in column 1 has run.

Now, you might be wondering about the large negative value that appears after stage 2, the Add stage in table 14.1. What has happened here is that the Add method has looked at the Book entity’s navigational properties to see whether there are any changes in its relationships. EF Core finds that a new Review entity is assigned to the Book entity, so it wants to set the foreign key. In this case, the Book entity hasn’t yet been written to the database, so it uses a negative key to represent that relationship. The negative key is unique within the current tracked entities and tells the SaveChanges method which new entities are linked.

In stage 3, in which the SaveChanges method is called, these negative keys link the Book entity and the Review entity. This causes EF Core to output SQL code that first INSERTs the Book entity into the Books table, returning its primary key as normal, followed by an INSERT of the Review entity, including a BookId value taken from the Book entity.

Table 14.1 How EF Core tracks relationships when adding new entities to the database. EF Core’s Add method uses negative key values to define the relationships. These negative keys are replaced with the real key value after the entities have been written to the database.

The problem is, if you wait until after the call to the SaveChanges method to get the correct key values, the State of the entities will have been cleared. You need a two-stage process, as shown in this listing. In the first part of the process, you capture the State and the relationships; and in the second part, you capture the primary key of any Book entities.

Now, let’s look inside the BookChangeInfo class and the FindChangedBooks method, as it’s interesting to see the steps required to get the State and the BookId in the correct form.

14.2.4 Finding the book view changes—Part 2, building the correct State

The preceding section showed you how the State property was correct before the call to the SaveChanges method, but the BookId wouldn’t be correct for a new Book until after that method call. Obviously, you need to do something before and after the SaveChanges method call. This section shows those steps.

Listing 14.5 showed the overridden SaveChanges method, with the extra code before and after the call to the base SaveChanges method. Now you’ll look at what’s happening before and after the base SaveChanges method call.

Before the base SaveChanges method call—get the State and relationships

Any change to a relationship in the Book entity class could affect the book list view. You therefore mark the Book entity and all its relationship entities with an interface, as shown in this code snippet:

You apply the IBookId interface Book entity, any entity class that has a foreign-key relationship with the Book entity (the Review, PriceOffer, and BookAuthor entities). This allows you to detect when a command changes any of these entities, which in turn will affect the book list view. After you find any change, you decode that change into a series of BookChangeInfo instances. The BookChangeInfo class holds the State before the SaveChanges method is called, and the BookId that refers to the Book entity it changes. This may be a negative value, as shown in table 14.1, or the real BookId for an update, but either way you can use it to find all the entities that are linked to a single Book entity.

Listing 14.6 shows how the BookChangeInfo class works out the correct State for the book list view. Working out the right State from the Book entity’s perspective is complex—for instance, a new Book entity should set the State to Added—but a new Review should only set the State to Modified, because the new Review only modifies the book list view.

That might seem like a lot of work to decide on the final State, but because you’re using the SoftDeleted property to hide a Book entity (see section 3.5.1), you need to honor that in the NoSQL database. If a Book entity’s SoftDeleted property is set to true, you must delete it from book list NoSQL database. Listing 14.6 must correctly handle all the combinations to ensure that it doesn’t try to delete an already soft-deleted book from the NoSQL database.

After the base SaveChanges method call—build a list of books that need updating

Now, let’s look at how to use this information after the SaveChanges method has been called. You take the BookChangeInfo information, which may include multiple updates to the same Book entity, and coalesce them down to a one-change-per-book list. The trick is to make sure the type of change is correct for the read-side database. This listing shows the BookChange class, with its static method that produces the final update information.

The result of this is a list of BookChange classes, which conveys the BookId of the book list view to change, and the State it should be changed to. You make this class as small as possible, because in a real system, you might want to save the information in the database, or send it to a background job to process. That would allow you to improve on the performance of the write, but more important, to provide retry and error-checking facilities in case the NoSQL database access fails.

14.2.5 Why the CQRS solution is less likely to have out-of-date cached values

When you create any system in which you cache values, the key issue is to make sure that the cached values stay in step with the calculated values. Applications that handle lots of simultaneous updates can produce situations in which a cached value gets out of step. This is one form of a concurrency issue (see section 8.7).

In section 13.4, you built a version of our application that stored various values, such as the average book’s review votes (what I refer to as cached-values SQL from now on). In that version, you use EF Core’s concurrency detection to find and fix a possible concurrency issue around simultaneous Reviews being added to a Book. That works, but you need to correctly identify that this is a potential problem and then write code to handle it. But it’s better if the design avoids potential concurrency issues, as you did with the ActualPrice in the cached-values SQL solution (section 13.4.1). The CQRS solution does that, by removing any concurrency issues right from the start.

Unlike the cached-values SQL solution, in which you had to consider each cached value separately and devise a different solution for each, the CQRS design handles all potential problems in one go; it effectively designs them out. In addition, the CQRS architecture helps with the overall design of the system, which is why I think CQRS architecture is worthy of consideration for systems that have more reads of data than writes.

14.2.6 Is the two-database CQRS architecture worth the effort?

Implementing this two-database CQRS architecture isn’t simple and took me over a week to develop, which is long for me. Admittedly, the main part is learning a new database approach, but there are also some complex EF Core parts to write to. So, is it worth the effort? I’ll answer that question in terms of three distinct aspects:

The differences in read-side performance between the non-CQRS and CQRS solutions

First, let’s compare the performance of the CQRS solution against the “best-SQL” solution—the part 2 version (see section 13.3) in which SQL had to calculate the average vote every time the book list was displayed. Figure 14.5 shows the performance of the CQRS solution against the part 2 version for the following database content:

Clearly, the performance of this two-database CQRS solution is much better than the “best-SQL” solution from section 13.3. No user wants to wait two seconds for the books to be sorted. The SQL version is slow because it must dynamically calculate the average votes every time. The CQRS solution, in which the book list view contains a precalculated average votes value with an index, is obviously much faster.

But to provide a fair comparison, you need to compare the CQRS solution against the part 3 solution (see section 13.4), in which you add cached values to your SQL database (the cached-values SQL solution). In this case, the difference is much smaller; see figure 14.6.

Looking beyond the precalculated view issue, figure 14.6 also shows that the RavenDB database, with its simpler database structure, has better performance as the number of books in the database increases. This brings us to another side of performance: scalability.

Scalability determines how many simultaneous users an application can handle while still providing good performance (see chapter 12, especially section 12.7). Because NoSQL databases such as RavenDB are dealing with a single entry containing all the information, a read or write is simpler than in the SQL case. In general, this makes NoSQL databases easier to duplicate (to have multiple databases all containing the same data).

The effect of having multiple databases on scalability can be significant. Not only can you spread database access across multiple databases, but you can locate databases geographically around the world to provide shorter access times.

To summarize on performance:

Is the drop in the performance of any book-related database write acceptable?

I said previously that a two-database CQRS architecture is going to be slower on writes, because it must write to two databases. I’ve measured this in my solution and there’s an effect, but it’s pretty small. Table 14.2 shows one common case, which is a user adding a review to a book, and the difference in write time.

In my mind, a function that takes less than 50 ms to return to the user isn’t worth performance tuning. But in applications with more-complex updates, this time might get too long. But there are plenty of ways to handle this; for instance, you could pass the update a background task to be executed so that the application returns immediately to the user. The only downside of that approach is the user may be shown out-of-date data, which could be confusing. These are the design trade-offs you must think through.

The differences in software implementation between non-CQRS and CQRS solutions

This section compares the cached-values SQL solution in section 13.4 and the two-database CQRS solution in this chapter. Both solutions take extra work to build, and make the software structure more complex. Table 14.3 compares the development effort, complexity, and robustness of the two designs.

In terms of development effort, the CQRS implementation takes longer to create than the cached-values SQL solution. Part of that is learning about RavenDB’s philosophy and software package, and part of it is coming up with a clever design to separate the NoSQL code from the rest of the application. But I think this is still within a sensible development cost, considering you’re performance-tuning an existing, SQL-only, EF Core application (but some managers may not agree with me!).

The CQRS design is more complex than the original EF Core design in chapter 5, but I accept some additional complexity whenever I apply performance tuning. But the cached-values SQL solution also added complexity, just in other places. Their complexity is different: the CQRS design has complex interfaces to hide things, the cached-values SQL solution has complex concurrency handling code. Overall, I think the CQRS design and the cached-values SQL solution are comparable in the extra complexity they add to the book app.

The big, positive difference is in the robustness of the CQRS design. I rate it above the cached-values SQL solution because it designs out the concurrency issues that the cached-values SQL solution has. You don’t need any explicit code to handle concurrency in my CQRS solution. That’s a big plus.

To summarize the differences in implementation:

14.3 Accessing and changing EF Core services

Now let’s look at a completely different area of EF Core: its internal services. EF Core is built in a modular way, with most of the key parts of its code linked by dependency injection (see section 5.3). The EF Core team has made these services available for developers to use for their own uses; you can override some of the services if you want to change the way EF Core works.

Using or overriding the EF Core services is an advanced feature, but it can be useful when you need to customize EF Core behavior, or to save you from writing code that EF Core has already implemented. This section covers the following:

14.3.1 Accessing an EF Core service to help in your own application

EF Core has more than 50 services that you could gain access to. Most aren’t that useful, but a few might help with a project you’re working on. One part of EF Core I’m interested in is its mapping of entity classes to SQL tables. In previous applications I wrote with EF6.x, I had to have a table of how EF mapped .NET types to database types. With EF Core, you can tap into its relational mapping service and obtain that information directly from the EF Core code.

To do this, you need to access the database provider mapping service via the IRelationalTypeMapper interface. This service provides methods that can map a .NET type, with any EF Core configurations or attributes, to an SQL type, and from an SQL type to a .NET type. Listing 14.8 shows how to obtain an instance of the SQL-type-to-.NET-type mapper that EF Core uses for an SQL Server database. In this case, you give it the SQL type, and it tells you the information about the .NET type, including information you’d need to configure EF Core to match that SQL Server type.

There are other services, but many are even more specific to EF Core and therefore not that useful outside EF Core itself. But the next section shows how you can replace an EF Core service with your own custom variant, which opens interesting possibilities.

14.3.2 Replacing an EF Core service with your own modified service

Wouldn’t it be great if you could change how the internals of EF Core work? For instance, you could modify the IModelValidator service to check that the database table names adhere to your specific project rules. Or you could apply a new property-naming convention to set the correct SQL varchar/nvarchar type by overriding the IRelationalTypeMapper service.

Even if you could replace them, some of these services are complicated; for instance, the RelationalModelValidator class has 11 methods. So it would be a nightmare if you had to re-create all that code, and you might have to change your code when a new EF Core version comes out. Thankfully the EF Core team has thought about developers wanting to alter or extend EF Core internal services.

The EF Core development team has built all the EF Core internal services with overridable methods. You can inherit the appropriate class and then just override the specific method you want to change, with the option of calling the base method if you need to. This makes it much easier to build a customer service, although you still need to understand what you’re doing.

For this example, you’re going to override part of the EF Core SqlServerTypeMapper class, which has 20 parts that can be overridden. Writing all those parts would be an impossible job, but you can override just the one you want to change and leave the rest alone, as shown in figure 14.7.

You’re going to override the FindMapping(IProperty property) method to add your own By Convention rule to EF Core’s configuration stage. The new rule will allow you to configure the SQL storage of certain string properties as a non-Unicode (8-bit) string to save space (normally, string properties are held in 16-bit Unicode characters in SQL Server). The new rule is as follows: if a string property name ends with Ascii, it should be stored using SQL Server’s varchar type (8-bit chars) rather than the normal string mapping to SQL Server’s nvarchar type (16-bit chars).

The first step is to create a custom type mapper, which is shown in the following listing. You override the .NET-type-to-SQL-type mapping method, in which you add the new code.

The second step is to alter the configuration options sent to the application’s DbContext when you create a new instance. Listing 14.10 shows the alteration of the ASP.NET Core’s ConfigureServices method in the Startup class, which registers the application’s DbContext, plus its options, with ASP.NET Core’s dependency injection module. The new line of code is shown in bold.

14.4 Accessing command-line tools from software

EF Core provides a series of command-line tools to allow you to migrate your database or reverse-engineer a database (see chapter 11 for more details). These are known as design-time services, because these services are normally run by typing a command into the PMC in Visual Studio or the command prompt on your system. But you can access them via software, which can be useful if you want to automate something or exploit a tool for your own use.

As an example, you’ll tap into the EF Core’s reverse-engineering tool design-time service and use it to get data that allows you to list the schema of a database referred to by a connection string.

14.4.1 How to access EF Core design-time services

To access EF Core design-time services, you need to re-create the setup that EF Core uses when you call commands such as Add-Migration or dotnet ef dbcontext scaffold. This is a bit complicated, and thanks to Erik Ejlskov Jensen (http://erikej.blogspot.co.uk/) for helping me with this.

Listing 14.11 shows the code to create the scaffolding (also known as reverse-engineering) service that’s used to produce the entity classes and application’s DbContext from an existing database (see section 11.3). For this to compile, you need to include the NuGet packages for the database providers that you want to access the design-time services; for instance, Microsoft.EntityFrameworkCore.SqlServer to access the SQL Server services.

You can use other design-time services, such as the migration tools, but those services will need a different setup. The best way to find out what’s required is to look at the EF Core source at https://github.com/aspnet/EntityFrameworkCore.

14.4.2 How to use design-time services to build the EfSchemaCompare tool

Section 11.4.2 introduced the EfSchemaCompare tool I created to help with database migrations. This uses the design-time scaffolding service to read in the schema of the database you want to inspect. Using the scaffolding service replaces a large amount of ADO.NET code I had to write when I built the EF6.x version of the EfSchemaCompare tool. And because the scaffolding service is provided by the database provider, my new EfSchemaCompare tool can work with any relational database that EF Core supports.

This listing shows how to use one of the available scaffolding services to get information on the schema of the database.

From this code, EfSchemaCompare can use the data to compare with EF Core’s Model property, which contains a model of what the database should look like based on the entity classes and EF Core configuration.

Using this design-time service provides three things to help build the EfSchemaCompare tool:

Summary