In our Fitradar backend solution we are heavily using a database in order to implement the necessary use cases. Almost every call to our backend API includes calls to the database. In order to make it easier to work with database from C# code we are using Entity Framework Core. The library allows us quickly and easy save C# objects in database and fetch persisted C# objects from the database. In many use cases the EF Core is used just for these basic operations – saving and fetching but the business logic is isolated in other classes. Nevertheless for some use cases we discovered that it is more straightforward to execute the business logic directly in the database in the form of stored procedures or user functions. And in this article I wanted to share some of the experience we had while were working on complex database operations and the solution we arrived at.

One of the main reasons why we started to consider moving some parts from the C# code to the database was the user experience. We noticed that some operations on the mobile application are taking considerable amounts of time and might degrade overall user experience and we started to look how to improve the performance of those slow operations. Some of the reasons we discovered that were causing the long response time were multiple calls to the database and unoptimized database operations. Striving for the clean code in our backend solution we tried to avoid stored procedures, user functions and triggers. Introducing SQL code that can’t be mapped to the C# code within a single use case would mean introducing another codebase. So far EF core allowed us to abstract from SQL code and we very happy to have all the logic in C# code. Besides one more language and codebase there were following issues that were holding us back from using SQL directly:

• Adding SQL code in DbContext in the form of strings would take away the Intellisense for SQL and that would make us deal not only with logic errors but with syntactic errors as well in the runtime.
• Refactoring with Visual Studio built-in tools would become more complex
• In case we needed to debug our method that uses stored procedure or fires SQL trigger we would have to debug C# and SQL code separately
• Working with two codebases readability of single use case is degrading – in order to understand the full logic we have to switch between C# and SQL
• There are different logging systems for ASP.NET Core and SQL and therefore it is harder to keep unified log format.
• More complex deploy process

But at the same time for complex persistence and database query operations we would have:

• Significant gain in performance
• Terse code, since SQL is specifically designed to work with relational databases and many operations can be expressed in much shorter code compared with C# and LINQ without losing the readability.

The main question we had to answer in order to understand whether we need to move some of the logic to stored procedures, triggers and user functions was: “Is the gain in performance worth it?” For some use cases we could see the improvements even with the test data and for the other use cases we predicted that changes we are planning to introduce will start to impact the performance once certain amount of data will be stored in a database. And so we decided to move some of our logic from C# to SQL code, but before that we really wanted to mitigate some of the above-mentioned problems we discovered during analysis. And to do that we decided to create separate SQL Server Database Project instead of keeping the SQL code in EF Core DbContext class and by doing that we solved the problem with structuring the code, Intellisense and SQL code maintenance.

P.S. Visit our website: https://www.fitradar.me/ and join the waiting list. We launch very soon!

Our Fitradar solution is using various third party services, and therefore the Integration tests in our solution became a very important part next to the Unit tests. In our backend application we are trying to follow the ideal Test Pyramid

but for some Use cases it makes more sense to run all the involved services rather than replace them with Mock objects, mostly because there is no complex logic involved in particular Use Case; it is rather straightforward without conditionals and loops. And on the other hand the Integration Tests give us more assurance that the Use Case is robust and works as expected. And so for some Use Cases we started to prefer Integration tests instead of Unit tests and our test pyramid became a little bit distorted. The following parts in our back end application are tested by Integration Tests:

• ASP.NET Core Model Binding. Once we discovered that produced JSON data for some C# objects is not exactly as we expected, and that some incoming JSON is not parsed as we expected we started to test and verify that incoming and outcoming data are transformed as we are expecting
• ASP.NET Core and third party libraries configurations defined in Startup file. The configurations can be tested only at runtime and therefore to test these configurations we have to run the test web server and make some requests against the server. Some of the configurations we are testing are following:
  • Dependency injection
  • AddMediatR and FluentValidation configuration
  • ASP.NET Core Authentication and Authorization integration with  Firebase Authentication
• Entity Framework produced SQL code. Especially for complex queries. And Entity Framework save and update logic for complex types where at runtime some nested objects might have different EntityState than parent object. And any complex logic interacting with Entity Framework
• Integration with Azure Services like Service Bus and Azure Web Functions
• Integration with Stripe

The crucial factor that made our team write more Integration Tests is the new ASP.NET Core test web host and test server client. It allowed us to quickly setup the test server with our back-end application and run the requests against our API. By using the test host we were able to test most of the configurations in the Setup file. It was harder to test our back-end parts that were part of the Azure Functions, but after some error and trial we were able to create a test web host for the Azure functions as well and issue the calls that triggered the Functions. And here is how we test the sport event cancellation Use Case that is triggered by message from Service Bus queue:

        [Fact]
        public async Task TestUnpaidEventCancelled()
        {
            var resolver = new RandomNameResolver();

            IHost host = new HostBuilder()
                .ConfigureWebJobs()
                .ConfigureDefaultTestHost<CancelSportEventFunction>(webJobsBuilder => {
                    webJobsBuilder.AddServiceBus();
                })
                .ConfigureServices(services => {
                    services.AddSingleton<INameResolver>(resolver);
                })
                .Build();

            using (host)
            {
                //Arrange
                await host.StartAsync();
                _testFixture.SeedSportEvent();
                await _testFixture.BookSportEventInstance();
                _testFixture.AddCommentToSportEventInstance();

                //Act
                await _testFixture.SendMessageAsync("CanceledEvents",
                    _testFixture.CanceledSportEventInstance.Id.ToString());
                await _testFixture.WaitUntilSportEventInstanceIsCancelled();

                //Assert
                //_output.WriteLine($"Found {parsedOrders.Count} invoices.");
                var calendarEvents = _testFixture.GetCalendarEvents();
                Assert.Empty(calendarEvents);
                var order = _testFixture.GetCancelledSportEventOrder();
                Assert.NotNull(order);
                var msg = _testFixture.GetVisitorsInboxMessage();
                Assert.NotNull(msg);
                Assert.Equal(MessageSource.CANCEL_EVENT, msg.Source);
                var statistics = _testFixture.GetVisitorsInboxStatistics();
                Assert.NotNull(statistics);
                Assert.Equal(1, statistics.NumberOfCancelMessages);
            }
        }

P.S. Visit our website: https://www.fitradar.me/ and join the waiting list. We launch very soon!