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.