Well, the title is surely overstated, and we can assert the same for all the ORM. As per everything, we need to know how they are made, in order to use them with awareness. Anyway, it was the first thing that comes on my mind as, during a consulting meeting with a new client, we realize that, despite our concentration, we were looking at the wrong criminal (in that case, Azure). Let’s proceed in an orderly fashion.
Following the event in Napoli Hello Azure DevOps (https://www.blexin.com/it-IT/Event/Hello-Azure-DevOps-5), a company did contact us to ask for our cooperation in the migration of their application. It was a Window Form solution, that has to migrate to more up-to-date technologies. Its requirements were: to be cross -platform, to have a as-a-service solution and performances in agreement with their standards.
We were happy for this request and I went by them to plan some consulting days to prepare a prototype to test the migration feasibility. We focus particularly ourselves on the aspect, which was much critical for them: the check-in for a healthcare facility. The scenario is a public office, where the employee must manage a queue of people waiting the sooner as possible.
From a technological point of view, we choose to test the Asp.Net Core for the backend, Angular for the frontend, leaving SQL Server as database, but with the possibility to change to PostgreSQL. Everything can be hosted both on Azure and on premise, and both on Windows and on Linux.
We start from the backend with one of the Asp.Net Core standard templates, which are provided from the Command Line Interface (CLI). We add the authentication with Asp.Net Identity, which is needed to profile users, and provide us also a DbContext Entity Framework Core to use to manage our entities. If you already installed the .NET Core CLI (https://dotnet.microsoft.com/) , you just need to create a folder and run the following command:
dotnet new mvc --auth Individual
For simplicity’s sake, we will focus on a single aspect of the prototype: the research of patients’ personal data. The patient usually arrives to the check-in and provides his/her name and last name. It is a quite simple class to create, as the example below:
public class Paziente
{
public int Id { get; set; }
[Required]
[StringLength(50)]
public string Nome { get; set; }
[Required]
[StringLength(50)]
public string Cognome { get; set; }
public DateTime DataNascita { get; set; }
[Required]
[StringLength(16)]
public string CodiceFiscale { get; set; }
[Required]
[StringLength(200)]
public string Indirizzo { get; set; }
[Required]
[StringLength(6)]
public string CAP { get; set; }
}
Add then the class to the ApplicationDbContext:
public class ApplicationDbContext : IdentityDbContext
{
public DbSet<Paziente> Pazienti { get; set; }
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
}
To simulate the worst situation one could face, we create e WebApp on Azure on a Service Plan F1 (the free one) and a SqlAzure Basic instance for € 4,21/month (there’s also a free instance for SqlAzure). You can have same results and much more with a free Azure trial. Once services have been created, you can take the connection string to database, but please remember to add your IP to the firewall, if you want to link to the server to make local tests:
Copy the connection string (note that username and password are not present for security reasons, but you have chosen them during the creation of the service):
and paste it in the file appsettings.json:
{
"ConnectionStrings": {
"DefaultConnection": "Server=tcp:efcoretest.database.windows.net,1433;Initial Catalog=efcoretest;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
},
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*"
}
we just need to create the Entity Framework Migration and apply it to the database:
dotnet ef migrations add Pazienti
dotnet ef database update
We have obviously uploaded some data in the test, about 25000 lines.
If you are using a Mac, please note: in the Startup.cs file the default provider of the template for Entity Framework is set on SqlLite and you have to change it to SqlServer:
services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(
Configuration.GetConnectionString("DefaultConnection")));
If you need to test the connection to the database from a Mac, you can use Azure Data Studio (https://docs.microsoft.com/it-it/sql/azure-data-studio/download?view=sql-server-2017): it’s free and very similar to VSCode!
At this stage, we only have to add a controller and write our query:
public class PazientiController : Controller
{
private readonly ApplicationDbContext ctx;
public PazientiController(ApplicationDbContext ctx)
{
this.ctx = ctx;
}
public IActionResult RicercaPazienti(string nome, string cognome)
{
var results = this.ctx.Pazienti
.Where(x => x.Nome.StartsWith(nome) && x.Cognome.StartsWith(cognome))
.Take(50)
.ToList();
return Ok(results);
}
}
It works, good. Unfortunately, in Windows Form application the selection of personal data gests users used to digit name and last name, with an update of result list every time one of these parameters changes. The effect is that this API will be recalled at any digit in one of these two fields, and we replicate this behavior in Angular.
The obvious result is that we don’t obtain the same feedback of the original application. Someone may asserts that is normal to lose something between the query and the serialization of the result in JSON: you are passing from a local environment to a Web one, from SQL Server in local network to Sql Azure in a Cloud, from an application on client, to an interaction Angular-Asp.Net Core-SqlServer. The purpose of the prototype was to test these chances indeed, then, together with Roberto, the client’s reference person, we start to make some tests.
We began from simplest activities, such as ascending the database, passing to a stronger and more expensive instance. The situation improves itself, of course, but not enough. Roberto adds two indices on name and last name: the situation improves again, but not enough. We try to change the UX, then press the Enter key to start the research: a great result, but we were still not satisfied at all. There was something wrong. Roberto, that works for year with data and SQL Server suddenly said to me: “Can you please let me have a look at the query that generates Entity Framework?”. The query is easily visible in console logs:
The new question then was: why did it make that type of WHERE? Note that Entry Framework is an ORM and the Core version has been completely rewrote to support some new scenarios, as databases in memory (very useful for automatic tests) and the management of non-relational databases (well, also ask myself, why should I use an ORM with a non-relational database). Furthermore, LINQ can be used also in scenarios with no database, and this is one of its point of strength.
We search on the web too, and we find out that this is a known issue, and It is generally linked to the use of the StartWith() and the Contains() on the strings. Frameworks developers lucky provide us specific functions to allow us to be more direct on LINQ, when we know we will work on a database:
public IActionResult RicercaPazienti(string nome, string cognome)
{
var results = this.ctx.Pazienti
.Where(x => EF.Functions.Like(x.Nome, nome + "%") && EF.Functions.Like(x.Cognome, cognome + "%"))
.Take(50)
.ToList();
return Ok(results);
}
The resulting query is:
The result is amazing: the speed detected from the application is exactly the one we need, even coming back to the base version of Azure SQL.
The bottom line is: when you are working with an ORM, Entity Framework or any other, don’t lose the sight of the generated queries. Disable the Lazy Load and manually set the fetch plan with the Include() when needed, and try on, try on, try on!
Happy Coding!
