I’m not going to go into detail here describing what is REST (Representational State Transfer) an API (Application Programming Interface) or JSON (JavaScript Object Notation). I start from the base that is already known to which we refer.
In this case, using the database published in the previous entry Data for demos, I will present how to expose the information from that database, in a read-only API.
At the same time, I will try to show advantages and disadvantages of different methods to achieve the same objective.
Entity Framework
Although in general, I dislike its use, I will try to include it in each demonstration, to see what advantages it brings, and the disadvantages that arise.
The project within the solution is ApiRestEF
Dapper
As a data access package/library, it allows you to easily obtain information from databases.
The project, within the solution is ApiRestDapper
Coding directly
In this case, I’ll be showing how to do, the same thing, but step by step, without libraries.
The project, within the solution is ApiRestCode
First requirement
A method is needed to obtain the information that is displayed, for a continent indicated as a parameter.
| Continent | Country | Total Cases | Total Deaths | Total Cases per Million | Total Deaths per Million | Population | Population Density | GDP per Capita |
| Africa | Seychelles | 12466 | 46 | 126764 | 468 | 98340 | 208 | 26382 |
| Africa | Cape Verde | 31433 | 271 | 56535 | 487 | 555988 | 136 | 6223 |
| Africa | Tunisia | 362658 | 13305 | 30685 | 1126 | 11818618 | 74 | 10849 |
| Africa | South Africa | 1722086 | 57410 | 29036 | 968 | 59308690 | 47 | 12295 |
| Africa | Libya | 188386 | 3155 | 27416 | 459 | 6871287 | 4 | 17882 |
| Africa | Botswana | 59480 | 896 | 25293 | 381 | 2351625 | 4 | 15807 |
| Africa | Namibia | 61374 | 968 | 24154 | 381 | 2540916 | 3 | 9542 |
| Africa | Eswatini | 18705 | 676 | 16123 | 583 | 1160164 | 79 | 7739 |
| Africa | Morocco | 522765 | 9192 | 14163 | 249 | 36910558 | 80 | 7485 |
| Africa | Djibouti | 11570 | 154 | 11711 | 156 | 988002 | 41 | 2705 |
| Africa | Gabon | 24696 | 156 | 11096 | 70 | 2225728 | 8 | 16562 |
Entity Framework
The code used obtains the continent and its constituent countries in a single query but required to go through the countries, to obtain from each one, the demographic data.
As I said, I don’t like EF and maybe that’s why my research into methods to do it, may not have found another way.
Of course, if someone offers another proposal in the comments, I’ll add it here, and I’ll also proceed to evaluate the corresponding metric.
public async Task<ActionResult<string>> GetContinent(int id) { var continent = await _context.Continents.FindAsync(id); continent.Countries = await _context.Countries .Where(x => x.ContinentId == id) .Select(x => new Country { Country1 = x.Country1, Id = x.Id, ContinentId = x.ContinentId } ) .ToListAsync(); foreach (var item in continent.Countries) { item.OwidCountriesData = await _context.OwidCountriesData .Where(x => x.CountriesId == item.Id) .Select(x => new OwidCountriesDatum { CountriesId = item.Id, TotalCases = x.TotalCases, TotalDeaths = x.TotalDeaths, TotalCasesPerMillion = x.TotalCasesPerMillion, TotalDeathsPerMillion = x.TotalDeathsPerMillion, Population = x.Population, PopulationDensity = x.PopulationDensity, GdpPerCapita = x.GdpPerCapita }).ToListAsync(); } if (continent == null) { return NotFound(); } string json = JsonSerializer.Serialize(continent, new JsonSerializerOptions() { WriteIndented = true, ReferenceHandler = ReferenceHandler.Preserve }); return json; }
Dapper
For the case of implementation with Dapper, we directly use the «DapperRow» types as the query return, thus decreasing the mapping between columns and properties. If defined classes were used, the response time would surely be longer.
public async Task<ActionResult<string>> GetContinent(int id) { string sql = @" SELECT [C].[Continent] , [CO].[Country] , [D].[total_cases] , [D].[total_deaths] , [D].[total_cases_per_million] , [D].[total_deaths_per_million] , [D].[population] , [D].[population_density] , [D].[gdp_per_capita] FROM [OwidCountriesData] AS[D] INNER JOIN [Continents] AS[C] ON[D].[ContinentId] = [C].[Id] INNER JOIN [Countries] AS[CO] ON[D].[CountriesId] = [CO].[Id] WHERE([C].[Id] = @continent) ORDER BY [D].[total_cases_per_million] DESC".Replace("@continent", id.ToString()); ; var continent = await _dal.GetDataAsync(sql); if (continent == null) { return NotFound(); } string json = JsonSerializer.Serialize(continent, new JsonSerializerOptions() { WriteIndented = true, ReferenceHandler = ReferenceHandler.Preserve }); return json; }
Only Code
Finally, for direct query by code, we optimize using the FOR JOSN modifier, and then directly obtaining the resulting JSON string.
public async Task<ActionResult<string>> GetContinent(int id) { string sql = @" SELECT [C].[Continent] , [CO].[Country] , [D].[total_cases] , [D].[total_deaths] , [D].[total_cases_per_million] , [D].[total_deaths_per_million] , [D].[population] , [D].[population_density] , [D].[gdp_per_capita] FROM [OwidCountriesData] AS [D] INNER JOIN [Continents] AS [C] ON [D].[ContinentId] = [C].[Id] INNER JOIN [Countries] AS [CO] ON [D].[CountriesId] = [CO].[Id] WHERE([C].[Id] = @continent) ORDER BY [D].[total_cases_per_million] DESC FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT('CountriesInfo');"; SqlCommand com = _dal.CreateCommand(sql); com.Parameters.AddWithValue("@continent", id); return await _dal.GetJSONDataAsync(com); }
Note
In both the Dapper and code projects, a minimal data access layer was built to emulate functionality similar to that provided by EF-generated code.
Performance
The graph below shows the comparison in CPU utilization, lifetime, and disk reads, in each of the cases.
A picture is worth a thousand words. 🙂
In the graph, values are evaluated only from the point of view of the database, not the .Net code, nor its runtime. I will add this in the next installment.
Universidad Net 