Step 1: Steps

To begin, grab a copy of the Activity1401_AsynchronousDatabaseOperations_Starter.zip files, extract them, build the project, double-check your connection string, and make sure that you have the InventoryManager database set up. Run an update-database command to make sure the migrations are up to date on your machine. Additionally, you could run an add-migration command to ensure that you don’t have any pending changes. Assuming there are none, you could then just run the remove-migration command to clean up the empty migration. If you have pending changes, consider just updating the database to match the current solution using the update-database command.

Alternatively, you could just continue using your InventoryManager solution that you’ve been building through the previous chapters in this book, with your code in the same state as it was at the end of Chapter 13.

Step 2: Begin at the database level

To make the changes work, we’re going to have to touch most of the layers in some way or another, including the tests. We’ll start by reworking all of the database calls and move up the layers from there. Along the way, we’ll see how to make calls with async/await, as well as see the ability to run from a synchronous method when we get to the program.

Build the project. There will be a number of errors, as should be expected (review Figure 14-1).

We can use the errors to work out the problems going forward as a road map. We already know that we changed the interface that is implemented by two classes. The next step is to rework the two implementations. Do not select “implement interface,” or you’ll get a number of duplicated methods. Instead, let’s fix the methods and the code that goes with them.

In the InventoryDatabaseRepo, begin by fixing the method signatures to match the methods in the interface. This is done by once again wrapping the return types with Task<T> or setting the return type to Task when the method is void. Additionally, each method needs to be declared as an async method. For example, the public List<GetItemsForListingDto> GetItemsForListingFromProcedure(...) method becomes public async Task<List<GetItemsForListingDto>> GetItemsForListingFromProcedure(...).

Make sure to add the using statement for System.Threading.Tasks.

After fixing all the signatures, a number of errors will be created. We’ll now walk through each method and fix the internal code.

Make note that some methods don’t have an error, but only have a green squiggly line under the method name. Hovering on the name of the method reveals the issue (see Figure 14-2).

While you can have an async method that does not have an await operation in it and still have valid execution, the warning here is to remind you that you made an asynchronous method without an await operation. We want to await the database call. To do this, we need to make a couple of changes.

Next, let’s fix the GetItemsForListingLinq method . This fix will be more involved. Because of the way this query is built and because of the database encryption we have implemented, we have to get the list back sooner than would be ideal. Making this method asynchronous will force us to rework the ordering a bit as well.

This change will allow us to get the list in an asynchronous manner and then we have to do the ordering. The IOrderedEnumerable will not work asynchronously as the code is written, so, while not ideal, at least we got the limited results with the query. If we wanted, we could get the query as an AsyncEnumerable, which would allow for async enumeration of the results, but here we’ll just fetch the results with the await operation and then order the resulting list in memory.

For clarity, the completed GetItemsForListingLinq method is illustrated in Figure 14-3.

The ListInventory() method is next, and it will require a few changes. For this method, we’ll again start by getting results with await and ToListAsync and then change to include the OrderBy. To see the error we would get without proper refactoring, just add await and change the return of ToList to ToListAsync. We’ll see the error is regarding the fact that the IOrderedEnumerable does not allow a ToListAsync call (review Figure 14-4).

For the UpdateItem method , we need to make a couple of similar changes. Add await to the call to get Items, and also change the FirstOrDefault call to SingleOrDefaultAsync.

To complete this portion of the activity, we need to also refactor the Dapper implementation.

To do this, once again change all the method signatures on each method to match the asynchronous changes as defined in the interface. Most of these methods are unimplemented, so we can just leave them. They will have a green squiggly line indicating that they do not implement an await operator, but that will not break the compiler or the program execution, even if the method had other synchronous code in it.

For clarity, I’ve collapsed all of the methods and am including a screenshot (see Figure 14-5) so that you can see what the method signatures should look like in the InventoryDatabaseDapperRepo class .

To complete the code changes in the InventoryDatabaseDapperRepo, we need to fix the two methods that are implemented. Beginning with ListCategoriesAndColors, change the code for the first var result = _connection.Query... to var result = await _connection.QueryAsync<dynamic>(sql);.

This will complete our refactor operations on the InventoryDatabaseLayer project. Build the project to get the next set of errors (see Figure 14-6).

Step 3: Update the integration tests to use asynchronous database operations

Now that our database layer is fully updated, we need to begin the next layer of fixes by first fixing the integration tests and making sure they still work as expected.

Open the InventoryManagerIntegrationTests class file in the IntegrationManagerTests project.

Begin by changing the four methods that have Fact or Theory attributes to be asynchronous. This is easily accomplished by simply changing the keyword void to be async Task. Yes, it is really that easy (see Figure 14-7 for reference).

Make sure to add the using statement for System.Threading.Tasks so that the code will compile.

In the each of the four methods identified in Figure 14-7, simply find any calls to the _dbRepo objects and add the await operator to allow for the methods to operate asynchronously. For example, in TestListInventory, change var items = _dbRepo.ListInventory(); to var items = await _dbRepo.ListInventory();

There should be four calls to await added to the test project when this is completed:

Rebuild the project. There are still errors for the InventoryBusinessLayer that need to be fixed. However, we can run the integration tests. Run them now to validate that our asynchronous code is working as expected (see Figure 14-8 for sample test output).

Step 4: Update the business layer

The next step in refactoring our code is to refactor the business layer. This will be a fairly easy and quick operation, now that the deeper database layer is already refactored and validated to be working via the integration tests.

Make sure to also update any calls in other methods to the ListInventory method to include an await operator, i.e., var items = await ListInventory(); such as in the GetItemsPipeDelimitedString method.

Now that our ItemsService is updated, we also need to update the ItemsServiceReadOnly to use asynchronous operations.

The remaining methods aren’t implemented, so just make sure they have the correct signatures as shown in Figure 14-9.

This completes our refactoring of the business layer. Rebuild the project to see the next set of errors, which will be in the InventoryManagerUnitTests and the overall program itself.

Step 5: Update the unit tests

Now that the business layer is in place, we need to update the unit tests. Open the InventoryManagerUnitTests file in the InventoryManagerUnitTests project.

There won’t be a lot to change in this project since we just have the one test. For the setup however, we need to do something a bit different. Since we are going to be mocking an asynchronous return, we need to get the _allItems object as the result of a Task. To do this, we can simply use the call Task.FromResult(_allItems).

This completes our refactor for the unit tests. Build the project and see the next set of errors which should all relate to the program itself. Run the unit tests to make sure that it passes as expected. Figure 14-10 shows the output of the unit tests running successfully.

All that remains is to fix the main program.

Step 6: Update the Program

To complete the system and allow it to work, we need to update the Program. In this part of the activity, we have all the code turned on and just need to get the calls to work with the new asynchronous code.

One thing that will happen is that there are a number of calls to ListInventory. For this reason, the first thing we should do is create a helper method to get the Inventory List. This will also give us a chance to see how to run an asynchronous piece of code from within a synchronous context.

Make sure to also add the using statement for.

In this code, we see the call to Task.Run(() => ...).Result; That line of code allows us to tell the system to run a command and then use a lambda to inject the asynchronous code to run. Since our method is not asynchronous, we cannot await the result. Therefore, we add the call to .Result at the end of the statement, which tells the system to wait until a result is returned.

Now that we have our common code for getting inventory, let’s fix the rest of the code.

To begin, replace any calls in the code to svc.ListInventory with the new method call to GetInventoryList(svc). There should be six places to replace and the one result that we just created with our new method. For example, the line var inventory = svc.ListInventory(); becomes var inventory = GetInventoryList(svc);.

Using the find and replace tool of ctrl + H, enter the svc.ListInventory() search term and replace all but the new method with GetInventoryList(svc).

After completion, run another find operation to validate you have six method calls to GetInventoryList(svc) as illustrated in Figure 14-11.

Rebuild the solution to see further errors. Each issue revolves around the fact that the method went from being synchronous to asynchronous. To fix the rest of the calls, look for a call to the business layer and then initiate the call using the Task.Run syntax learned previously.

In the update multiple items method, set the svc call for two lines of code to use the Task.Run(...) operation. First, find and set svc.InsertOrUpdateItem(updItem) to Task.Run(() => svc.InsertOrUpdateItem(updItem));. Then find and set the svc.InsertOrUpdateItems(allItems) method to Task.Run(() => svc.InsertOrUpdateItems(allItems));.

Note that a void method does not need to await the result. For clarity and brevity, review Figure 14-12 to see these two lines of code that need to be altered in the UpdateMultipleItems method.

Use a find and replace operation to implement the code changes from the previous discussion. When completed, your code should look similar to what is shown in Figure 14-13.

Build and run the program. Everything should work as expected now that we have fixed up the code. Figure 14-14 shows the program in action with asynchronous operations.

Remember, if you are having problems getting the program to run, don’t hesitate to just leverage the code in the final version of the files to see the completed version of these reworked method calls.

This concludes the activity on asynchronous database operations.

Final thoughts on activity 1401

In this first activity for our chapter, we were able to work through getting the database operations into an asynchronous pattern. We started by changing out the lower-level database layer calls to leverage the context with async and await calls.

After working through each layer, we saw how easy it was to refactor the solution for asynchronous operations. In the end, our program remained as a synchronous method, and therefore we used the Task.Run(() => somecode).Result call to get the results of an asynchronous operation from a synchronous context.

In the next activity, we’ll see what it takes to work with multiple contexts and how having the power to work with asynchronous commands can really help a solution be more responsive.

Step 1: Get the files we created in Chapter 6

We created a new web application all the way back in Chapter 6. To complete this activity, we’ll be starting where we left off in that activity. In the event you didn’t complete those activities, you can just get the Activity1402_Multiple_Database_Contexts_Starter.zip files and extract them to use on your local development machine.

You may need to update the connection string in the appSettings.json file , as the default connection for the web app is just going to leverage the localdb. This simulates a situation where you have an application that connects to two different databases on two different servers.

Once up and running, make sure you can register users and log in to validate the fact that we have a prebuilt identity schema in place for managing user authentication and authorization.

Additionally, you’ll need to put a few categories into the category table. This will not be the same as our inventory system, so feel free to enter anything you want.

Since I’m creating the starter pack and activity 0601 was not a long activity, I’m just running with new files. You could do the same if you so desired.

A copy of this script is also available in the Resources folder in the starter pack files.

Step 2: Bring the inventory libraries into the project

For clarity, review Figure 14-15 to see the projects in the solution.

Once the projects are in place, add a reference to the InventoryBusinessLayer in the main project. All other references will be added via the dependency chain in the projects (see Figure 14-16 for clarity).

The important thing to note is to not forget that you may need to update that connection string to point to the correct local database instance, whether it’s in SQLExpress or SQLDeveloper edition.

Build the solution and run it. There shouldn’t be any issues. If for some reason you get an issue, you may just need to make sure all of your NuGet packages are updated to the latest versions.

Step 3: Add the context to the injection for the web application

With the InventoryManager libraries ready to go, it’s time to inject the context into the project so that we can use it in our web solution.

Locate the Startup.cs file in the web application. Notice the ConfigureServices method. This is where the context injection will be added. Note that there is already a statement to add the DBContext for the ApplicationDbContext – the default context that contains identity.

We need to add another AddDbContext statement, and we need to leverage the connection string that we copied in the previous step.

Copy the lines for services.AddDbContext<....."DefaultConnection")));

For clarity, the code from the ConfigureServices method is shown in Figure 14-17.

Note that there is no reason we must use a different database.  As long as the two contexts do not conflict with one another, you can put them both into the same database.

Run the solution to make sure there are no issues. Everything should still work as before.

Step 4: Generate Inventory controllers and views for Items

Now that we’ve brought our InventoryDBContext into the solution, let’s generate some CRUD operations around the Inventory items in the web solution.

Right-click the Controllers folder and select Add ➤ Controller (see Figure 14-18).

Select MVC Controller with views, using Entity Framework, and then hit Add (review Figure 14-19).

Next, change the context to the InventoryDbContext and select the Item model from InventoryModels. Note that the controller will be Items1Controller. Change that default name to InventoryItemsController. Figure 14-20 shows what the form should look like before hitting “Add” to scaffold the views.

Add the controller and let the scaffolded views be created by default.

Our inventory context is leveraged, but not our service and database layer that we’ve tested. By default, the solution is putting direction operations against the DBContext into the controller.

Let’s run it to validate that things are working the way we would expect. Run the project and navigate to https://localhost:<yourport>/InventoryItems.

You should see whatever was in your inventory listed in the table (output should be similar to Figure 14-21).

Make sure you can also view, create, edit, and delete items.

This is not a book on web development.  Therefore, we aren’t going to spend any time making the web page nicer at this point.  Clearly, this page is not production-ready.

A couple of final thoughts. We directly leveraged the context and injected it. To make this solution work more like a production system, it would be a good idea to set up AutoMapper and instantiate it at the services level (like we did for the context, similar to how we set everything up in the console app). Then, in the InventoryItems Controller, instantiate an ItemsService object, and only use that object to get data.

After refactoring to set the ItemsService in the controller, also refactor the views to use the DTO objects instead of the full-blown models.

Finally, you’d want to make sure that your drop-down list shows the CategoryName, not the Id so that the user could know which category they are selecting.

The amount of work it would take to do this is outside of the scope of this activity, but is a worthy endeavor to continue your learning.

Step 5: Add a new model to the web application context, add the migration, and update the database

With the new State model added to the solution, add the State model to the ApplicationDBContext so it can be leveraged in code. Add the line public DbSet<State> States { get; set; } to the ApplicationDbContext. You can add the code anywhere in the class, but I generally stack my properties and then do the constructors and then other methods. For this instance, I put the States DbSet property after the Categories property and before the Constructor.

Even though the contexts are in different projects, EF still needs us to explicitly name the context to run the migration against. The error we get in this situation is shown in Figure 14-22.

Trying to run the migration in this situation generates an error due to having multiple contexts in the solution. To remedy that, the suggestion is to use the -Context flag . In EF6, we leveraged the -ConfigurationTypeName flag.

One other important note here.  When using multiple database contexts, you should make sure they are in different namespaces, and then when you reference them, use the fully qualified name, including the namespace.  This will ensure the correct migrations are associated with the correct contexts.

When the command add-migration <name> -Context <NameSpace>.<ClassName> is used, the migration generates as expected.

Now we just need to run update-database, right? You probably guessed by now that just running update-database will have the same problem as add-migration. Instead, run the command update-database -Context Activity1402_MultipleDatabaseContexts.Data.ApplicationDbContext.

If we wanted, we could generate the controller and views for the State model to see that it works as expected in the database. Additionally, we could go on to add more entities to the InventoryManager system . In that case, the database for the InventoryManager would reflect the migrations.

When running the update-database command, it is not as important to fully qualify with the namespace, because the program is not generating anything but is executing.  In the preceding command, we could have simply run the command update-database -context ApplicationDbContext.

In the rare instance that you are using multiple contexts against the same database, just remember that you can’t have conflicts in naming between the two contexts. Once one of them has a model that is leveraged, named States, for example, the other one would not be able to add it since they both exist in the same database. However, when multiple databases are used in conjunction with contexts, then the model names do not need to be unique and can be reused, such as Item was reused in both of our contexts for this activity.

Final thoughts on activity 1402

In this second activity for our chapter, we were able to see what it would take to leverage multiple database contexts in the same solution.

The main takeaway is that it is possible, and this opens the door for sharing data across solutions, such as sharing the user identity management portion of a suite of solutions within one database context that can be shared.

Additionally, using multiple contexts opens the door to both sharing the same database and having separate implementations, perhaps even across vendors. There is nothing stopping us from having one context connecting to SQL Server and another to Oracle.

The real difference in the way we have to work when using multiple contexts is that we have to remember to explicitly name the context in the PMC as we run commands. The command in .Net Core is simple: -Context. The command in EF6 was a bit less intuitive and was named -ConfigurationTypeName .

In general, working with multiple contexts should be discouraged, but it is not impossible. There is added complexity that comes into play with multiple contexts. However, there are definite benefits in clear boundaries between things like users and application data, as well as potential segregation of business units.