Archivo de la etiqueta: C#

Languages and countries in your Apps


In our current globalized world, at this time, any web site must be multilingual, enabling the user to select the language to use.

Moreover, a global company needs to know from which country the user connected.

A lot of sites, more of the streaming services as examples, identifies the country based on the IP address. Which could be a mistake, since a lot of people are in a different country of which belongs to.  

Anyway, your database must need a language list and a countries list.

This post shows you a sample application which, using the standardized languages and countries from .Net Framework, and adding some information from external sources, set up some tables to manage this in your databases.

System.Globalization Namespace

The tool uses information from the System.Globalization namespace, which appears with .Net Framework from it very beginning.

The CultureInfo type exposes each of the languages in use around the world thru the GetCultures member, based in the ISO 639-1: Codes for the Representation of Names of Languages  and in the ISO – ISO 3166 — Country Codes.

Each CultureInfo class instance exposes these properties among others:

Name The identifier of the culture in the culturecode2-country/regioncode2 format
EnglishName The culture name in Enlgish
DisplayName The culture name in the culture of your current configuration
NativeName The culture name in it own culture
IetfcultureTag The culture part of the name
LCID The Windows Language code
ThreeLetterISOcultureName ISO 639-2 code
TextInfo Information about text management
Calendar Calendars used by the culture
DateTimeFormat How the dates and times are managed
NumberFormat How the numbers, currencies etc. Are used
IsNeutralCulture Indicates if it is just a language or a language and country specification

Finally, I get information from two external sources. I got the GPS coordinates of each country from here meanwhile the flag’s pictures are from here. You can found the urls inside the code as well.

Storage’s schema.

Languages/Countries database schema

The tool create 3 tables as you can see in the Diagram.

It is necessary this way, because some countries use more than one language, and the relationship must be preserved.

The tables have a InUse column, to enable/disable each row for your application. So, you can query the Languages table for all the rows with the InUse value in 1, to display only those languages you desire use, or have enabled.

Note: It is important using nvarchar/nchar data types, since several Native names are in UTF-8 chars.

Using the tool.

The tool expects at least the connection string to your database. It accepts a second parameter for the schema name under the tables will be created. If this value is not provided, the tool assumes “Masters” as schema name.

In any case, the DDL scripts manage the creation of the schema if it does not exist.

The source code of the DataGen solution is in my GitHub.

If you prefer just use a T-SQL script to add the tables, here is the script.

In future posts, I will show some faqncy methods for site AND CONTENT localization.

using the API REST (JSON)


In this example, we will see how we can test the different versions from a web application.

It is a simple page that calls consecutively the different versions, repeatedly (100), and returns the number of milliseconds involved in the whole process.

To increase the impedance of the evaluation, the option is given in a check box, to perform the same query, 100 times, but for all countries.

In any case, the responses will be in approximately the same proportion.

Performance

As you can see, from the point of view of the client application, the final result is faster with EF than with simple code, exactly the opposite of the performance in the first test.

It should be noted, however, that in that first test, the process requested was not the same at all, since data from different tables were required, while, in this case, the data came from only one.

In any case, the comparison could be established with the second publication of this series, which obtains the same data as the latter.

In any case, this last test has some advantage on the part of EF, compared to the rest.

In other words, let us point out conclusions:

  • The result is not always as expected. You must try. ALWAYS.
  • There is no single way to do things. We must investigate to improve the quality of the applications we generate.
  • An unit test of the final set of the application can lead to false conclusions, since other factors, such as communication, data transformation, etc., also influence performance. That is, unit tests are highly valid for testing functionality, but they are not always valid for evaluating performance.
  • In fact, most likely, in a real-world application environment, performance results can also change.

Therefore, it is important to monitor the application deployed in production, include counters, logs etc. to have permanent information and be able to periodically evaluate it, to anticipate possible problems.

We’ll see more of this in other posts.

Puedes encontrar la solución completa en este repositorio

Usando la API REST (JSON)


En este ejemplo, veremos como podemos probar las distintas versiones desde un aplicativo web.

Se trata de una página sencilla que, llama consecutivamente a las distintas versiones, repetidas veces (100), y nos retorna la cantidad de milisegundos implicados en todo el proceso.

Para incrementar la impedancia de la evaluación, se da la opción en una casilla, de realizar la misma consulta, 100 veces, pero para todos los países.

En cualquier caso, las respuestas serán aproximadamente en la misma proporción.

Rendimiento

Como se puede ver, desde el punto de vista de la aplicación cliente, el resultado final es más rápido con EF que con código plano, exactamente lo contrario a la respuesta en la primera prueba.

Es de notar, sin embargo, que en aquella primera prueba, el proceso solicitado no era el mismo en lo absoluto, dado que se requerían datos de distintas tablas, mientras que, en este caso, los datos provienen de una sola.

En todo caso, la comparativa la podríamos establecer con la segunda publicación de esta serie, que obtiene los mismos datos que ésta última.

En cualquier caso, esta última prueba presenta alguna ventaja de parte de EF, respecto del resto.

O sea, puntualicemos conclusiones:

  • No siempre el resultado es el esperado. Hay que probar. SIEMPRE.
  • No hay una sola forma de hacer las cosas. Hay que investigar para mejorar la calidad de las aplicaciones que generamos.
  • Una prueba aislada del conjunto final de la aplicación, puede llevarnos a falsas conclusiones, ya que otros factores, como comunicación, transformación de datos, etc., también influyen en el rendimiento.
    O sea, las pruebas unitarias son muy validad para comprobar funcionalidad, pero no siempre son válidas para evaluar rendimiento.

De hecho, muy probablemente, en un entorno de una aplicación real, también los resultados de rendimiento pueden cambiar.

Por ello, es importante hacer seguimiento de la aplicación desplegada en producción, incluir contadores, bitácoras etc. para tener información permanente y poder evaluar periódicamente la misma, para adelantarnos a posibles problemas.

Ya veremos más de esto.

Puedes encontrar la solución completa en este repositorio

GENERATING A REST API (JSON) [3]


Finally, I’ll add projects to use stored procedures instead of statements constructed in code.

Moreover, and based on the recommendation of a greater entity framework expert than me, I added “AsNoTracking()” to the Entity Framework LINQ query set to  Generating a REST API (JSON)[2].

The Stored Procedure.

This is the stored procedure which receives the country ID, the date from and date to, and the page to display.

Is the stored procedure which is responsible for setting values that are appropriate to the date parameters, rather than setting them from the component in C#.

CREATE PROCEDURE [dbo].[Owid Covid Data Get By Country] 
   @fromDate   SMALLDATETIME NULL
 , @toDate     SMALLDATETIME NULL
 , @CountryId  INT
 , @Page       INT           = 1
AS
  BEGIN
    SELECT 
        @froMDate = ISNULL(@fromDate,
    (
        SELECT 
            MIN([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    SELECT 
        @toDate = ISNULL(@toDate,
    (
        SELECT 
            MAX([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    DECLARE 
           @Skip  INT = ((@Page - 1) * 100);
    BEGIN
      SELECT 
          [o].[ContinentId]
        , [o].[CountriesId]
        , [o].[date]
        , [o].[hosp_patients]
        , [o].[hosp_patients_per_million]
        , [o].[icu_patients]
        , [o].[icu_patients_per_million]
        , [o].[new_cases]
        , [o].[new_cases_per_million]
        , [o].[new_cases_smoothed]
        , [o].[new_cases_smoothed_per_million]
        , [o].[new_deaths]
        , [o].[new_deaths_per_million]
        , [o].[new_deaths_smoothed]
        , [o].[new_deaths_smoothed_per_million]
        , [o].[new_tests]
        , [o].[new_tests_per_thousand]
        , [o].[new_tests_smoothed]
        , [o].[new_tests_smoothed_per_thousand]
        , [o].[new_vaccinations]
        , [o].[new_vaccinations_smoothed]
        , [o].[new_vaccinations_smoothed_per_million]
        , [o].[people_fully_vaccinated]
        , [o].[people_fully_vaccinated_per_hundred]
        , [o].[people_vaccinated]
        , [o].[people_vaccinated_per_hundred]
        , [o].[positive_rate]
        , [o].[tests_per_case]
        , [o].[tests_units]
        , [o].[total_cases]
        , [o].[total_cases_per_million]
        , [o].[total_deaths]
        , [o].[total_deaths_per_million]
        , [o].[total_tests]
        , [o].[total_tests_per_thousand]
        , [o].[total_vaccinations]
        , [o].[total_vaccinations_per_hundred]
        , [o].[weekly_hosp_admissions]
        , [o].[weekly_hosp_admissions_per_million]
        , [o].[weekly_icu_admissions]
        , [o].[weekly_icu_admissions_per_million]
      FROM 
         [Owid Covid Data] AS [o]
      WHERE [o].[date] >= @fromDate
            AND [o].[date] <= @toDate
            AND [o].[CountriesId] = @CountryId
      ORDER BY 
          date
      OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY;
    END;
  END;

Exactly the same, but with “FOR JSON PATH” at the end, is used in the project that uses pure code.

El Cambio en Entity Framework

Based on the proposal and comment, the code is as follows:

public IEnumerable<OwidCovidDatum> GetCountryData(
   int CountryId,
   DateTime? fromDate=null, 
   DateTime? toDate=null,
   int Page=1)
{
   fromDate=fromDate??
      (from el in _context.OwidCovidData orderby el.Date select el.Date).FirstOrDefault();
   toDate=toDate??
      (from el in _context.OwidCovidData orderby el.Date descending select el.Date).FirstOrDefault();
 
   return (from OwidCovidDatum el in 
              _context.OwidCovidData
              .Where(x=> x.Date>=fromDate && x.Date<=toDate && x.CountriesId==CountryId)
              .Skip((Page-1)*100)
              .Take(100select el).AsNoTracking().ToList();
}

Dapper Using Stored Procedure

We use Dapper’s stored procedure execution capability, which is capable of assign values to parameters by name matching.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
 
   var result =await _dal.GetDataAsync("[Owid Covid Data Get By Country]",new { fromDate, toDate, CountryId, Page });
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Code using Stored Procedure

In the case of direct code, we assign the parameters one by one, also specifying the data type, which allows greater specificity.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   var command = _dal.CreateCommand("[Owid Covid Data Get By Country JSON]");
   command.Parameters.Add("@fromDate", System.Data.SqlDbType.SmallDateTime).Value = fromDate;
   command.Parameters.Add("@toDate", System.Data.SqlDbType.SmallDateTime).Value = toDate;
   command.Parameters.Add("@CountryId", System.Data.SqlDbType.Int).Value = CountryId;
   command.Parameters.Add("@skip", System.Data.SqlDbType.Int).Value = Page;
   var json =await _dal.GetJSONDataAsync(command);
   return json;
}

Performance

The graph shows that even when you use features that improve effectiveness, the simplicity of the code improves performance.

That is, for better response to the user, more time should be invested by developers in improving their development.

As a detail, stored procedure calls directly make calls to the SP, instead of using, as we saw in the previous post, sp_executesql.

EXEC [Owid Covid Data Get By Country] 
     @fromDate = NULL
   , @toDate = NULL
   , @CountryId = 4
   , @Page = 3;

GENERANDO UNA API REST (JSON) [3]


Aquí, finalmente, agregaré proyectos para utilizar procedimientos almacenados en lugar de sentencias construidas en el código.

De paso, y por recomendación de un mayor experto que yo en Entity Framework, agregué “AsNoTracking()” a la consulta LINQ de Entity Framework establecida en Generando una API REST (JSON) [2].

El procedimiento Almacenado.

Este es el procedimiento almacenado que recibe, el Id de país, la fecha desde y la fecha hasta, y la página a mostrar.

Es el procedimiento almacenado el responsable de establecer valores adecuados a los parámetros de fecha, en lugar de establecerlos desde el componente en C#.

CREATE PROCEDURE [dbo].[Owid Covid Data Get By Country] 
   @fromDate   SMALLDATETIME NULL
 , @toDate     SMALLDATETIME NULL
 , @CountryId  INT
 , @Page       INT           = 1
AS
  BEGIN
    SELECT 
        @froMDate = ISNULL(@fromDate,
    (
        SELECT 
            MIN([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    SELECT 
        @toDate = ISNULL(@toDate,
    (
        SELECT 
            MAX([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    DECLARE 
           @Skip  INT = ((@Page - 1) * 100);
    BEGIN
      SELECT 
          [o].[ContinentId]
        , [o].[CountriesId]
        , [o].[date]
        , [o].[hosp_patients]
        , [o].[hosp_patients_per_million]
        , [o].[icu_patients]
        , [o].[icu_patients_per_million]
        , [o].[new_cases]
        , [o].[new_cases_per_million]
        , [o].[new_cases_smoothed]
        , [o].[new_cases_smoothed_per_million]
        , [o].[new_deaths]
        , [o].[new_deaths_per_million]
        , [o].[new_deaths_smoothed]
        , [o].[new_deaths_smoothed_per_million]
        , [o].[new_tests]
        , [o].[new_tests_per_thousand]
        , [o].[new_tests_smoothed]
        , [o].[new_tests_smoothed_per_thousand]
        , [o].[new_vaccinations]
        , [o].[new_vaccinations_smoothed]
        , [o].[new_vaccinations_smoothed_per_million]
        , [o].[people_fully_vaccinated]
        , [o].[people_fully_vaccinated_per_hundred]
        , [o].[people_vaccinated]
        , [o].[people_vaccinated_per_hundred]
        , [o].[positive_rate]
        , [o].[tests_per_case]
        , [o].[tests_units]
        , [o].[total_cases]
        , [o].[total_cases_per_million]
        , [o].[total_deaths]
        , [o].[total_deaths_per_million]
        , [o].[total_tests]
        , [o].[total_tests_per_thousand]
        , [o].[total_vaccinations]
        , [o].[total_vaccinations_per_hundred]
        , [o].[weekly_hosp_admissions]
        , [o].[weekly_hosp_admissions_per_million]
        , [o].[weekly_icu_admissions]
        , [o].[weekly_icu_admissions_per_million]
      FROM 
         [Owid Covid Data] AS [o]
      WHERE [o].[date] >= @fromDate
            AND [o].[date] <= @toDate
            AND [o].[CountriesId] = @CountryId
      ORDER BY 
          date
      OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY;
    END;
  END;

Exactamente igual, pero con “FOR JSON PATH” al final, se usa en el proyecto que utiliza código puro.

El Cambio en Entity Framework

Basado en la propuesta y comentario, el código queda como sigue:

public IEnumerable<OwidCovidDatum> GetCountryData(
   int CountryId,
   DateTime? fromDate=null, 
   DateTime? toDate=null,
   int Page=1)
{
   fromDate=fromDate??
      (from el in _context.OwidCovidData orderby el.Date select el.Date).FirstOrDefault();
   toDate=toDate??
      (from el in _context.OwidCovidData orderby el.Date descending select el.Date).FirstOrDefault();
 
   return (from OwidCovidDatum el in 
              _context.OwidCovidData
              .Where(x=> x.Date>=fromDate && x.Date<=toDate && x.CountriesId==CountryId)
              .Skip((Page-1)*100)
              .Take(100select el).AsNoTracking().ToList();
}

Dapper Usando Procedimientos Almacenados

Utilizamos la capacidad de ejecución de procedimientos almacenados de Dapper, que es capaz de asignar valores a los parámetros por coincidencia de nombres.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
 
   var result =await _dal.GetDataAsync("[Owid Covid Data Get By Country]",new { fromDate, toDate, CountryId, Page });
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Código usando Procedimientos Almacenados

En el caso del código directo, asignamos los parámetros uno a uno, especificando además el tipo de dato, que permite una mayor especificidad.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   var command = _dal.CreateCommand("[Owid Covid Data Get By Country JSON]");
   command.Parameters.Add("@fromDate", System.Data.SqlDbType.SmallDateTime).Value = fromDate;
   command.Parameters.Add("@toDate", System.Data.SqlDbType.SmallDateTime).Value = toDate;
   command.Parameters.Add("@CountryId", System.Data.SqlDbType.Int).Value = CountryId;
   command.Parameters.Add("@skip", System.Data.SqlDbType.Int).Value = Page;
   var json =await _dal.GetJSONDataAsync(command);
   return json;
}

Rendimiento

El gráfico muestra que, aún cuando se utilizan características que mejoran la efectividad, la simpleza del código mejora el rendimiento.

O sea, para mejor respuesta al usuario, se deberá invertir más tiempo de los desarrolladores en mejorar su desarrollo.

Como detalle, las llamadas de procedimiento almacenado, realizan directamente llamadas al mismo, en lugar de utilizar, como vimos en la publicación anterior, sp_executesql.

EXEC [Owid Covid Data Get By Country] 
     @fromDate = NULL
   , @toDate = NULL
   , @CountryId = 4
   , @Page = 3;

GENERATING A REST API (JSON) [2]


Let’s consider another requirement, to evaluate how you can best take advantage of the features of the Entity Framework and emulate that functionality in cases where it cannot be used, or it is more convenient to do something else.

In this example, we are using the same database explained in Data-for-demos

The requirement

You need to get the statistical information of cases, vaccinations, etc. By country, between certain dates, with the following conditions:

  • If no start date is entered, the first available date is used.
  • If the end date is not entered, the last available date is used.
  • The information must be returned in batches of 100 entries, so the requested page number must be received.

In this case, it will be implemented in the “Country” controller

Entity Framework.

The code leverages EF’s Fluent capabilities to nest conditions. Similarly, below, the Entity Framework generates a statement according to the data engine in use, in this case, SQL Server.

public async Task<ActionResult<IEnumerable<OwidCovidDatum>>> GetCountryData(
   int CountryId,
   DateTime? fromDate=null, 
   DateTime? toDate=null,
   int Page=1)
{
   fromDate=fromDate??
      (from el in _context.OwidCovidData orderby el.Date select el.Date).FirstOrDefault();
   toDate=toDate??
      (from el in _context.OwidCovidData orderby el.Date descending select el.Date).FirstOrDefault();
 
   return (from OwidCovidDatum el in 
              _context.OwidCovidData
              .Where(x=> x.Date>=fromDate && x.Date<=toDate && x.CountriesId==CountryId)
              .Skip((Page-1)*100)
              .Take(100select el).ToList();
}

Dapper

Using the returned DapperRows, we implement the call with an SQL statement that is almost the same as the one automatically generated by EF.

[HttpGet]
public async Task<stringGetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   int skip = ((Page - 1* 100);
   fromDate = fromDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MIN([o].[date]) FROM [Owid Covid Data] AS [o];");
   toDate = toDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MAX([o].[date]) FROM [Owid Covid Data] AS [o];");
   string sql = $@"SELECT 
       [o].[ContinentId]
     , [o].[CountriesId]
     , [o].[date]
     , [o].[hosp_patients]
     , [o].[hosp_patients_per_million]
     , [o].[icu_patients]
     , [o].[icu_patients_per_million]
     , [o].[new_cases]
     , [o].[new_cases_per_million]
     , [o].[new_cases_smoothed]
     , [o].[new_cases_smoothed_per_million]
     , [o].[new_deaths]
     , [o].[new_deaths_per_million]
     , [o].[new_deaths_smoothed]
     , [o].[new_deaths_smoothed_per_million]
     , [o].[new_tests]
     , [o].[new_tests_per_thousand]
     , [o].[new_tests_smoothed]
     , [o].[new_tests_smoothed_per_thousand]
     , [o].[new_vaccinations]
     , [o].[new_vaccinations_smoothed]
     , [o].[new_vaccinations_smoothed_per_million]
     , [o].[people_fully_vaccinated]
     , [o].[people_fully_vaccinated_per_hundred]
     , [o].[people_vaccinated]
     , [o].[people_vaccinated_per_hundred]
     , [o].[positive_rate]
     , [o].[tests_per_case]
     , [o].[tests_units]
     , [o].[total_cases]
     , [o].[total_cases_per_million]
     , [o].[total_deaths]
     , [o].[total_deaths_per_million]
     , [o].[total_tests]
     , [o].[total_tests_per_thousand]
     , [o].[total_vaccinations]
     , [o].[total_vaccinations_per_hundred]
     , [o].[weekly_hosp_admissions]
     , [o].[weekly_hosp_admissions_per_million]
     , [o].[weekly_icu_admissions]
     , [o].[weekly_icu_admissions_per_million]
   FROM
      [Owid Covid Data] AS[o]
   WHERE(([o].[date] >= '{fromDate.Value.ToString("u").Substring(0,10)}')
         AND([o].[date] <= '{ toDate.Value.ToString("u").Substring(010)}'))
        AND([o].[CountriesId] = {CountryId})
   ORDER BY
       date
   OFFSET {skip} ROWS FETCH NEXT 100 ROWS ONLY; ";
   var result =await _dal.GetDataAsync(sql);
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Code

As in the previous example, we create a parameterized Command object that returns a string of characters with the resulting JSON, implemented in the SQL statement.

         [HttpGet]
   public async Task<stringGetCountryData(
      int CountryId, 
      DateTime? fromDate = null, 
      DateTime? toDate = null, 
      int Page = 1)
   {
      int skip = ((Page - 1* 100);
      fromDate = fromDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MIN([o].[date]) FROM [Owid Covid Data] AS [o];");
      toDate = toDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MAX([o].[date]) FROM [Owid Covid Data] AS [o];");
      string sql = $@"SELECT 
       [o].[ContinentId]
     , [o].[CountriesId]
     , [o].[date]
     , [o].[hosp_patients]
     , [o].[hosp_patients_per_million]
     , [o].[icu_patients]
     , [o].[icu_patients_per_million]
     , [o].[new_cases]
     , [o].[new_cases_per_million]
     , [o].[new_cases_smoothed]
     , [o].[new_cases_smoothed_per_million]
     , [o].[new_deaths]
     , [o].[new_deaths_per_million]
     , [o].[new_deaths_smoothed]
     , [o].[new_deaths_smoothed_per_million]
     , [o].[new_tests]
     , [o].[new_tests_per_thousand]
     , [o].[new_tests_smoothed]
     , [o].[new_tests_smoothed_per_thousand]
     , [o].[new_vaccinations]
     , [o].[new_vaccinations_smoothed]
     , [o].[new_vaccinations_smoothed_per_million]
     , [o].[people_fully_vaccinated]
     , [o].[people_fully_vaccinated_per_hundred]
     , [o].[people_vaccinated]
     , [o].[people_vaccinated_per_hundred]
     , [o].[positive_rate]
     , [o].[tests_per_case]
     , [o].[tests_units]
     , [o].[total_cases]
     , [o].[total_cases_per_million]
     , [o].[total_deaths]
     , [o].[total_deaths_per_million]
     , [o].[total_tests]
     , [o].[total_tests_per_thousand]
     , [o].[total_vaccinations]
     , [o].[total_vaccinations_per_hundred]
     , [o].[weekly_hosp_admissions]
     , [o].[weekly_hosp_admissions_per_million]
     , [o].[weekly_icu_admissions]
     , [o].[weekly_icu_admissions_per_million]
   FROM
      [Owid Covid Data] AS[o]
   WHERE(([o].[date] >= @fromDate)
         AND([o].[date] <= @toDate))
        AND([o].[CountriesId] = @CountryId)
   ORDER BY
       date
   OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY FOR JSON PATH; ";
      SqlCommand com = _dal.CreateCommand(sql);
      com.Parameters.AddWithValue("@CountryId", CountryId);
      com.Parameters.AddWithValue("@fromDate", fromDate);
      com.Parameters.AddWithValue("@toDate", toDate);
      com.Parameters.AddWithValue("@skip", skip);
      return await _dal.GetJSONDataAsync(com);
   }
}

SQL sentences

For ease of comparison, here are, together, the three SQL statements used.

Entity Framework

EXEC [sp_executesql] 
     N'SELECT 
    [o].[ContinentId]
  , [o].[CountriesId]
  , [o].[date]
  , [o].[hosp_patients]
  , [o].[hosp_patients_per_million]
  , [o].[icu_patients]
  , [o].[icu_patients_per_million]
  , [o].[new_cases]
  , [o].[new_cases_per_million]
  , [o].[new_cases_smoothed]
  , [o].[new_cases_smoothed_per_million]
  , [o].[new_deaths]
  , [o].[new_deaths_per_million]
  , [o].[new_deaths_smoothed]
  , [o].[new_deaths_smoothed_per_million]
  , [o].[new_tests]
  , [o].[new_tests_per_thousand]
  , [o].[new_tests_smoothed]
  , [o].[new_tests_smoothed_per_thousand]
  , [o].[new_vaccinations]
  , [o].[new_vaccinations_smoothed]
  , [o].[new_vaccinations_smoothed_per_million]
  , [o].[people_fully_vaccinated]
  , [o].[people_fully_vaccinated_per_hundred]
  , [o].[people_vaccinated]
  , [o].[people_vaccinated_per_hundred]
  , [o].[positive_rate]
  , [o].[tests_per_case]
  , [o].[tests_units]
  , [o].[total_cases]
  , [o].[total_cases_per_million]
  , [o].[total_deaths]
  , [o].[total_deaths_per_million]
  , [o].[total_tests]
  , [o].[total_tests_per_thousand]
  , [o].[total_vaccinations]
  , [o].[total_vaccinations_per_hundred]
  , [o].[weekly_hosp_admissions]
  , [o].[weekly_hosp_admissions_per_million]
  , [o].[weekly_icu_admissions]
  , [o].[weekly_icu_admissions_per_million]
FROM 
   [Owid Covid Data] AS [o]
WHERE(([o].[date] >= @__fromDate_0)
      AND ([o].[date] <= @__toDate_1))
     AND ([o].[CountriesId] = @__CountryId_2)
ORDER BY
(
    SELECT 
        1
)
OFFSET @__p_3 ROWS FETCH NEXT @__p_4 ROWS ONLY;'
   , N'@__fromDate_0 datetime,@__toDate_1 datetime,@__CountryId_2 int,@__p_3 int,@__p_4 int'
   , @__fromDate_0 = '2020-01-01 00:00:00'
   , @__toDate_1 = '2021-06-11 00:00:00'
   , @__CountryId_2 = 4
   , @__p_3 = 300
   , @__p_4 = 100;


Dapper

SELECT 
    [o].[ContinentId]
  , [o].[CountriesId]
  , [o].[date]
  , [o].[hosp_patients]
  , [o].[hosp_patients_per_million]
  , [o].[icu_patients]
  , [o].[icu_patients_per_million]
  , [o].[new_cases]
  , [o].[new_cases_per_million]
  , [o].[new_cases_smoothed]
  , [o].[new_cases_smoothed_per_million]
  , [o].[new_deaths]
  , [o].[new_deaths_per_million]
  , [o].[new_deaths_smoothed]
  , [o].[new_deaths_smoothed_per_million]
  , [o].[new_tests]
  , [o].[new_tests_per_thousand]
  , [o].[new_tests_smoothed]
  , [o].[new_tests_smoothed_per_thousand]
  , [o].[new_vaccinations]
  , [o].[new_vaccinations_smoothed]
  , [o].[new_vaccinations_smoothed_per_million]
  , [o].[people_fully_vaccinated]
  , [o].[people_fully_vaccinated_per_hundred]
  , [o].[people_vaccinated]
  , [o].[people_vaccinated_per_hundred]
  , [o].[positive_rate]
  , [o].[tests_per_case]
  , [o].[tests_units]
  , [o].[total_cases]
  , [o].[total_cases_per_million]
  , [o].[total_deaths]
  , [o].[total_deaths_per_million]
  , [o].[total_tests]
  , [o].[total_tests_per_thousand]
  , [o].[total_vaccinations]
  , [o].[total_vaccinations_per_hundred]
  , [o].[weekly_hosp_admissions]
  , [o].[weekly_hosp_admissions_per_million]
  , [o].[weekly_icu_admissions]
  , [o].[weekly_icu_admissions_per_million]
FROM 
   [Owid Covid Data] AS [o]
WHERE(([o].[date] >= '01/01/2020 00:00:00')
      AND ([o].[date] <= '06/11/2021 00:00:00'))
     AND ([o].[CountriesId] = 4)
ORDER BY 
    date
OFFSET 300 ROWS FETCH NEXT 100 ROWS ONLY;

Code

EXEC [sp_executesql] 
     N'SELECT 
             [o].[ContinentId]
           , [o].[CountriesId]
           , [o].[date]
           , [o].[hosp_patients]
           , [o].[hosp_patients_per_million]
           , [o].[icu_patients]
           , [o].[icu_patients_per_million]
           , [o].[new_cases]
           , [o].[new_cases_per_million]
           , [o].[new_cases_smoothed]
           , [o].[new_cases_smoothed_per_million]
           , [o].[new_deaths]
           , [o].[new_deaths_per_million]
           , [o].[new_deaths_smoothed]
           , [o].[new_deaths_smoothed_per_million]
           , [o].[new_tests]
           , [o].[new_tests_per_thousand]
           , [o].[new_tests_smoothed]
           , [o].[new_tests_smoothed_per_thousand]
           , [o].[new_vaccinations]
           , [o].[new_vaccinations_smoothed]
           , [o].[new_vaccinations_smoothed_per_million]
           , [o].[people_fully_vaccinated]
           , [o].[people_fully_vaccinated_per_hundred]
           , [o].[people_vaccinated]
           , [o].[people_vaccinated_per_hundred]
           , [o].[positive_rate]
           , [o].[tests_per_case]
           , [o].[tests_units]
           , [o].[total_cases]
           , [o].[total_cases_per_million]
           , [o].[total_deaths]
           , [o].[total_deaths_per_million]
           , [o].[total_tests]
           , [o].[total_tests_per_thousand]
           , [o].[total_vaccinations]
           , [o].[total_vaccinations_per_hundred]
           , [o].[weekly_hosp_admissions]
           , [o].[weekly_hosp_admissions_per_million]
           , [o].[weekly_icu_admissions]
           , [o].[weekly_icu_admissions_per_million]
         FROM
            [Owid Covid Data] AS[o]
         WHERE(([o].[date] >= @fromDate)
               AND([o].[date] <= @toDate))
              AND([o].[CountriesId] = @CountryId)
         ORDER BY
             date
         OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY FOR JSON PATH; '
   , N'@CountryId int,@fromDate datetime,@toDate datetime,@skip int'
   , @CountryId = 4
   , @fromDate = '2020-01-01 00:00:00'
   , @toDate = '2021-06-11 00:00:00'
   , @skip = 200;

It’s striking that in the case of code, it uses sp_executesql,just like EF, which takes a bit longer to execute.

It seems that this will need to be improved in the next publication.

Performance

The same procedure was used as in the previous publication to evaluate the results.

As you can see, things have improved quite a bit for EF, although in this case, Dapper seems to be the one who works best.

The difference is precisely the sp_executesql.

GENERANDO UNA API REST (JSON) [2]


Consideremos otro requisito, para evaluar como puede aprovecharse mejor las características de Entity Framework y emular esa funcionalidad en los casos en que no se pueda utilizar, o sea más conveniente otra forma de realizar la tarea.

En este ejemplo, estamos utilizando la misma base de datos explicada en Datos-para-demos

El requisito

Se necesita obtener la información estadística de casos, vacunaciones, etc. Por país, entre determinadas fechas, con las siguientes condiciones:

  • Si no se consigna fecha de inicio, se usa la primera disponible.
  • Si no se consigna la fecha de fin, se usa la última disponible.
  • Se debe retornar la información en lotes de a 100 entradas, con lo cual, se deberá recibir el número de página solicitado.

En este caso, se implementará en el controlador “Country

Entity Framework.

El código aprovecha las funcionalidades Fluent de EF para anidar las condiciones. Igualmente, por debajo, Entity Framework genera una sentencia acorde al motor de datos en uso, en este caso, SQL Server.

public async Task<ActionResult<IEnumerable<OwidCovidDatum>>> GetCountryData(
   int CountryId,
   DateTime? fromDate=null, 
   DateTime? toDate=null,
   int Page=1)
{
   fromDate=fromDate??
      (from el in _context.OwidCovidData orderby el.Date select el.Date).FirstOrDefault();
   toDate=toDate??
      (from el in _context.OwidCovidData orderby el.Date descending select el.Date).FirstOrDefault();
 
   return (from OwidCovidDatum el in 
              _context.OwidCovidData
              .Where(x=> x.Date>=fromDate && x.Date<=toDate && x.CountriesId==CountryId)
              .Skip((Page-1)*100)
              .Take(100select el).ToList();
}

Dapper

Utilizando los DapperRow de retorno, implementamos la llamada con una sentencia SQL que es casi igual a la generada automáticamente por EF.

[HttpGet]
public async Task<stringGetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   int skip = ((Page - 1* 100);
   fromDate = fromDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MIN([o].[date]) FROM [Owid Covid Data] AS [o];");
   toDate = toDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MAX([o].[date]) FROM [Owid Covid Data] AS [o];");
   string sql = $@"SELECT 
       [o].[ContinentId]
     , [o].[CountriesId]
     , [o].[date]
     , [o].[hosp_patients]
     , [o].[hosp_patients_per_million]
     , [o].[icu_patients]
     , [o].[icu_patients_per_million]
     , [o].[new_cases]
     , [o].[new_cases_per_million]
     , [o].[new_cases_smoothed]
     , [o].[new_cases_smoothed_per_million]
     , [o].[new_deaths]
     , [o].[new_deaths_per_million]
     , [o].[new_deaths_smoothed]
     , [o].[new_deaths_smoothed_per_million]
     , [o].[new_tests]
     , [o].[new_tests_per_thousand]
     , [o].[new_tests_smoothed]
     , [o].[new_tests_smoothed_per_thousand]
     , [o].[new_vaccinations]
     , [o].[new_vaccinations_smoothed]
     , [o].[new_vaccinations_smoothed_per_million]
     , [o].[people_fully_vaccinated]
     , [o].[people_fully_vaccinated_per_hundred]
     , [o].[people_vaccinated]
     , [o].[people_vaccinated_per_hundred]
     , [o].[positive_rate]
     , [o].[tests_per_case]
     , [o].[tests_units]
     , [o].[total_cases]
     , [o].[total_cases_per_million]
     , [o].[total_deaths]
     , [o].[total_deaths_per_million]
     , [o].[total_tests]
     , [o].[total_tests_per_thousand]
     , [o].[total_vaccinations]
     , [o].[total_vaccinations_per_hundred]
     , [o].[weekly_hosp_admissions]
     , [o].[weekly_hosp_admissions_per_million]
     , [o].[weekly_icu_admissions]
     , [o].[weekly_icu_admissions_per_million]
   FROM
      [Owid Covid Data] AS[o]
   WHERE(([o].[date] >= '{fromDate.Value.ToString("u").Substring(0,10)}')
         AND([o].[date] <= '{ toDate.Value.ToString("u").Substring(010)}'))
        AND([o].[CountriesId] = {CountryId})
   ORDER BY
       date
   OFFSET {skip} ROWS FETCH NEXT 100 ROWS ONLY; ";
   var result =await _dal.GetDataAsync(sql);
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Código

Al igual que en el ejemplo previo, creamos un objeto Command con parámetros que retorne una cadena de caracteres con el JSON resultante, implementado en la sentencia SQL.

         [HttpGet]
   public async Task<stringGetCountryData(
      int CountryId, 
      DateTime? fromDate = null, 
      DateTime? toDate = null, 
      int Page = 1)
   {
      int skip = ((Page - 1* 100);
      fromDate = fromDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MIN([o].[date]) FROM [Owid Covid Data] AS [o];");
      toDate = toDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MAX([o].[date]) FROM [Owid Covid Data] AS [o];");
      string sql = $@"SELECT 
       [o].[ContinentId]
     , [o].[CountriesId]
     , [o].[date]
     , [o].[hosp_patients]
     , [o].[hosp_patients_per_million]
     , [o].[icu_patients]
     , [o].[icu_patients_per_million]
     , [o].[new_cases]
     , [o].[new_cases_per_million]
     , [o].[new_cases_smoothed]
     , [o].[new_cases_smoothed_per_million]
     , [o].[new_deaths]
     , [o].[new_deaths_per_million]
     , [o].[new_deaths_smoothed]
     , [o].[new_deaths_smoothed_per_million]
     , [o].[new_tests]
     , [o].[new_tests_per_thousand]
     , [o].[new_tests_smoothed]
     , [o].[new_tests_smoothed_per_thousand]
     , [o].[new_vaccinations]
     , [o].[new_vaccinations_smoothed]
     , [o].[new_vaccinations_smoothed_per_million]
     , [o].[people_fully_vaccinated]
     , [o].[people_fully_vaccinated_per_hundred]
     , [o].[people_vaccinated]
     , [o].[people_vaccinated_per_hundred]
     , [o].[positive_rate]
     , [o].[tests_per_case]
     , [o].[tests_units]
     , [o].[total_cases]
     , [o].[total_cases_per_million]
     , [o].[total_deaths]
     , [o].[total_deaths_per_million]
     , [o].[total_tests]
     , [o].[total_tests_per_thousand]
     , [o].[total_vaccinations]
     , [o].[total_vaccinations_per_hundred]
     , [o].[weekly_hosp_admissions]
     , [o].[weekly_hosp_admissions_per_million]
     , [o].[weekly_icu_admissions]
     , [o].[weekly_icu_admissions_per_million]
   FROM
      [Owid Covid Data] AS[o]
   WHERE(([o].[date] >= @fromDate)
         AND([o].[date] <= @toDate))
        AND([o].[CountriesId] = @CountryId)
   ORDER BY
       date
   OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY FOR JSON PATH; ";
      SqlCommand com = _dal.CreateCommand(sql);
      com.Parameters.AddWithValue("@CountryId", CountryId);
      com.Parameters.AddWithValue("@fromDate", fromDate);
      com.Parameters.AddWithValue("@toDate", toDate);
      com.Parameters.AddWithValue("@skip", skip);
      return await _dal.GetJSONDataAsync(com);
   }
}

Las sentencias SQL

Para facilitar la comparativa, aquí están, juntas, las tres sentencias SQL utilizadas.

Entity Framework

EXEC [sp_executesql] 
     N'SELECT 
    [o].[ContinentId]
  , [o].[CountriesId]
  , [o].[date]
  , [o].[hosp_patients]
  , [o].[hosp_patients_per_million]
  , [o].[icu_patients]
  , [o].[icu_patients_per_million]
  , [o].[new_cases]
  , [o].[new_cases_per_million]
  , [o].[new_cases_smoothed]
  , [o].[new_cases_smoothed_per_million]
  , [o].[new_deaths]
  , [o].[new_deaths_per_million]
  , [o].[new_deaths_smoothed]
  , [o].[new_deaths_smoothed_per_million]
  , [o].[new_tests]
  , [o].[new_tests_per_thousand]
  , [o].[new_tests_smoothed]
  , [o].[new_tests_smoothed_per_thousand]
  , [o].[new_vaccinations]
  , [o].[new_vaccinations_smoothed]
  , [o].[new_vaccinations_smoothed_per_million]
  , [o].[people_fully_vaccinated]
  , [o].[people_fully_vaccinated_per_hundred]
  , [o].[people_vaccinated]
  , [o].[people_vaccinated_per_hundred]
  , [o].[positive_rate]
  , [o].[tests_per_case]
  , [o].[tests_units]
  , [o].[total_cases]
  , [o].[total_cases_per_million]
  , [o].[total_deaths]
  , [o].[total_deaths_per_million]
  , [o].[total_tests]
  , [o].[total_tests_per_thousand]
  , [o].[total_vaccinations]
  , [o].[total_vaccinations_per_hundred]
  , [o].[weekly_hosp_admissions]
  , [o].[weekly_hosp_admissions_per_million]
  , [o].[weekly_icu_admissions]
  , [o].[weekly_icu_admissions_per_million]
FROM 
   [Owid Covid Data] AS [o]
WHERE(([o].[date] >= @__fromDate_0)
      AND ([o].[date] <= @__toDate_1))
     AND ([o].[CountriesId] = @__CountryId_2)
ORDER BY
(
    SELECT 
        1
)
OFFSET @__p_3 ROWS FETCH NEXT @__p_4 ROWS ONLY;'
   , N'@__fromDate_0 datetime,@__toDate_1 datetime,@__CountryId_2 int,@__p_3 int,@__p_4 int'
   , @__fromDate_0 = '2020-01-01 00:00:00'
   , @__toDate_1 = '2021-06-11 00:00:00'
   , @__CountryId_2 = 4
   , @__p_3 = 300
   , @__p_4 = 100;


Dapper

SELECT 
    [o].[ContinentId]
  , [o].[CountriesId]
  , [o].[date]
  , [o].[hosp_patients]
  , [o].[hosp_patients_per_million]
  , [o].[icu_patients]
  , [o].[icu_patients_per_million]
  , [o].[new_cases]
  , [o].[new_cases_per_million]
  , [o].[new_cases_smoothed]
  , [o].[new_cases_smoothed_per_million]
  , [o].[new_deaths]
  , [o].[new_deaths_per_million]
  , [o].[new_deaths_smoothed]
  , [o].[new_deaths_smoothed_per_million]
  , [o].[new_tests]
  , [o].[new_tests_per_thousand]
  , [o].[new_tests_smoothed]
  , [o].[new_tests_smoothed_per_thousand]
  , [o].[new_vaccinations]
  , [o].[new_vaccinations_smoothed]
  , [o].[new_vaccinations_smoothed_per_million]
  , [o].[people_fully_vaccinated]
  , [o].[people_fully_vaccinated_per_hundred]
  , [o].[people_vaccinated]
  , [o].[people_vaccinated_per_hundred]
  , [o].[positive_rate]
  , [o].[tests_per_case]
  , [o].[tests_units]
  , [o].[total_cases]
  , [o].[total_cases_per_million]
  , [o].[total_deaths]
  , [o].[total_deaths_per_million]
  , [o].[total_tests]
  , [o].[total_tests_per_thousand]
  , [o].[total_vaccinations]
  , [o].[total_vaccinations_per_hundred]
  , [o].[weekly_hosp_admissions]
  , [o].[weekly_hosp_admissions_per_million]
  , [o].[weekly_icu_admissions]
  , [o].[weekly_icu_admissions_per_million]
FROM 
   [Owid Covid Data] AS [o]
WHERE(([o].[date] >= '01/01/2020 00:00:00')
      AND ([o].[date] <= '06/11/2021 00:00:00'))
     AND ([o].[CountriesId] = 4)
ORDER BY 
    date
OFFSET 300 ROWS FETCH NEXT 100 ROWS ONLY;

Código

EXEC [sp_executesql] 
     N'SELECT 
             [o].[ContinentId]
           , [o].[CountriesId]
           , [o].[date]
           , [o].[hosp_patients]
           , [o].[hosp_patients_per_million]
           , [o].[icu_patients]
           , [o].[icu_patients_per_million]
           , [o].[new_cases]
           , [o].[new_cases_per_million]
           , [o].[new_cases_smoothed]
           , [o].[new_cases_smoothed_per_million]
           , [o].[new_deaths]
           , [o].[new_deaths_per_million]
           , [o].[new_deaths_smoothed]
           , [o].[new_deaths_smoothed_per_million]
           , [o].[new_tests]
           , [o].[new_tests_per_thousand]
           , [o].[new_tests_smoothed]
           , [o].[new_tests_smoothed_per_thousand]
           , [o].[new_vaccinations]
           , [o].[new_vaccinations_smoothed]
           , [o].[new_vaccinations_smoothed_per_million]
           , [o].[people_fully_vaccinated]
           , [o].[people_fully_vaccinated_per_hundred]
           , [o].[people_vaccinated]
           , [o].[people_vaccinated_per_hundred]
           , [o].[positive_rate]
           , [o].[tests_per_case]
           , [o].[tests_units]
           , [o].[total_cases]
           , [o].[total_cases_per_million]
           , [o].[total_deaths]
           , [o].[total_deaths_per_million]
           , [o].[total_tests]
           , [o].[total_tests_per_thousand]
           , [o].[total_vaccinations]
           , [o].[total_vaccinations_per_hundred]
           , [o].[weekly_hosp_admissions]
           , [o].[weekly_hosp_admissions_per_million]
           , [o].[weekly_icu_admissions]
           , [o].[weekly_icu_admissions_per_million]
         FROM
            [Owid Covid Data] AS[o]
         WHERE(([o].[date] >= @fromDate)
               AND([o].[date] <= @toDate))
              AND([o].[CountriesId] = @CountryId)
         ORDER BY
             date
         OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY FOR JSON PATH; '
   , N'@CountryId int,@fromDate datetime,@toDate datetime,@skip int'
   , @CountryId = 4
   , @fromDate = '2020-01-01 00:00:00'
   , @toDate = '2021-06-11 00:00:00'
   , @skip = 200;

Es llamativo que en el caso de código, utiliza sp_executesql, al igual que EF, lo cual demora un poco la ejecución.

Parece que esto habrá que mejorarlo en la siguiente publicación.

Rendimiento

Se utilizó el mismo procedimiento que en la publicación anterior, para evaluar los resultados.

Como se ve, la cosa ha mejorado bastante para EF, aunque en este caso, Dapper parece ser quien da mejor resultado.

La diferencia está, precisamente, el sp_executesql.

GENERATING A REST API (JSON)


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.

ContinentCountryTotal CasesTotal DeathsTotal Cases per MillionTotal Deaths per MillionPopulationPopulation DensityGDP per Capita
AfricaSeychelles12466461267644689834020826382
AfricaCape Verde31433271565354875559881366223
AfricaTunisia36265813305306851126118186187410849
AfricaSouth Africa17220865741029036968593086904712295
AfricaLibya1883863155274164596871287417882
AfricaBotswana59480896252933812351625415807
AfricaNamibia6137496824154381254091639542
AfricaEswatini18705676161235831160164797739
AfricaMorocco52276591921416324936910558807485
AfricaDjibouti1157015411711156988002412705
AfricaGabon2469615611096702225728816562

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.

Generando una API REST (JSON)


No voy a entrar aquí en detalles de descripción de lo que es REST (Representational State Transfer) una API (Application Programming Interface) o JSON (JavaScript Object Notation). Parto de la base que ya se conoce a que nos referimos.

En este caso, utilizando la base de datos publicada en la entrada anterior Datos para demos, presentaré como exponer la información de esa base, en una API de sólo lectura.

Al mismo tiempo, intentaré mostrar ventajas y desventajas de distintos métodos para lograr el mismo objetivo.

Entity Framework

Aunque en líneas generales, me disgusta bastante su utilización, intentaré incluirlo en cada demostración, por ver que ventajas aporta, y los inconvenientes que surjan.

El proyecto dentro de la solución es ApiRestEF

Dapper

Como paquete/biblioteca de acceso a datos, permite fácilmente obtener información de bases de datos.

El proyecto, dentro de la solución es ApiRestDapper

Código directo

En este caso, estaré mostrando como hacer, lo mismo, pero paso a paso, sin bibliotecas.

El proyecto, dentro de la solución es ApiRestCode

Primer requisito.

Se necesita un mecanismo por el cual obtener la información que se muestra, para un continente indicado como parámetro.

ContinentCountryTotal CasesTotal DeathsTotal Cases per MillionTotal Deaths per MillionPopulationPopulation DensityGDP per Capita
AfricaSeychelles12466461267644689834020826382
AfricaCape Verde31433271565354875559881366223
AfricaTunisia36265813305306851126118186187410849
AfricaSouth Africa17220865741029036968593086904712295
AfricaLibya1883863155274164596871287417882
AfricaBotswana59480896252933812351625415807
AfricaNamibia6137496824154381254091639542
AfricaEswatini18705676161235831160164797739
AfricaMorocco52276591921416324936910558807485
AfricaDjibouti1157015411711156988002412705
AfricaGabon2469615611096702225728816562

Entity Framework

El código utilizado obtiene el continente y sus países constitutivos en una sola consulta pero requirió recorrer los países, para obtener de cada uno, los datos demográficos.

Como ya dije, no me gusta EF y quizás por ello, mi investigación de métodos para hacerlo, puede no haber encontrado otra forma.

Por supuesto, si alguien ofrece otra propuesta en los comentarios, la agregaré aquí, y también procederé a evaluar la métrica correspondiente.

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

para el caso de la implementación con Dapper, utilizamos directamente los tipos “DapperRow” como retorno de la consulta, disminuyendo así el mapeo entre columnas y propiedades. De usarse clases definidas, seguramente el tiempo de respuesta sería mayor.

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;
}

Código

Finalmente, para la consulta directa por código, optimizamos utilizando el modificador FOR JOSN, y obteniendo entonces directamente la cadena JSON resultante.

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);
 
}

Aclaración

Tanto en el proyecto Dapper como en el de código, se construyó una capa de acceso a datos mínima, para emular similar funcionalidad a la brindada por el código generado por EF.

Rendimiento

El gráfico inferior muestra la comparativa en utilización de CPU, tiempo de duración, y lecturas a disco, en cada uno de los casos.
Una imagen, vale más que mil palabras. 🙂

En el gráfico, solo se evalúan valores desde el punto de vista de la base de datos, no del código .Net, ni su tiempo de ejecución. Agregaré esto en la próxima entrega.

PAGING DATA, NOT CLIENT SIDE (2) (Blazor client)


In the previous post,Paging data, NOT client side. | Universidad Net, I described how to use OFFSET-FETCH pair to paginate data in the server side. In this one, I will describe an example of how to use it in a Web Assembly App.

Note: To test this code, you must create a new Blazor app in Visual Studio.

The Model.

The data model will be a class with two properties, one for the list of items and other with the page’s information.

This classes will be added in the Shared project of the Application.

The first class vary depending on the data you want to display, but the second one will be always the same, enhanced by some code.

Since the pager class will be a standardized and enhanced version of the data retrieved by using the stored procedure, and could be used in different projects, let’s define an interface for it.

IPagesInfo interface.

This is the code for the Interface.

public interface IPagesInfo
{
   #region Properties
 
   /// <summary>
   /// Gets or sets the current page.....
   /// </summary>
   System.Int32 CurrentPage { getset; }
 
   /// <summary>
   /// Gets the current first page number..
   /// </summary>
   Int32 FirstPageNumber { get; }
 
   /// <summary>
   /// Gets the HasNextGroup
   /// Gets a value indicating whether this instance has next page..
   /// </summary>
   Boolean HasNextGroup { get; }
 
   /// <summary>
   /// Gets the HasPreviousGroup
   /// Gets a value indicating whether this instance has previous page..
   /// </summary>
   Boolean HasPreviousGroup { get; }
 
   /// <summary>
   /// Gets the last page number..
   /// </summary>
   Int32 LastPageNumber { get; }
 
   /// <summary>
   /// Gets or sets the number of links to show..
   /// </summary>
   Int32 NumberOfLinks { getset; }
 
   /// <summary>
   /// Gets or sets the size of the page.....
   /// </summary>
   System.Int32 PageSize { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows skipped from the top of the select.....
   /// </summary>
   System.Int32 Skip { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows taken.....
   /// </summary>
   System.Int32 Take { getset; }
 
   /// <summary>
   /// Gets or sets the amount of items to display.
   /// </summary>
   System.Int32 TotalItems { getset; }
 
   /// <summary>
   /// Gets the total pages available to display..
   /// </summary>
   Int32 TotalPages { get; }
   /// <summary>
   /// Gets the page number for the previous group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   Int32 PreviousGroupStart { get; }
   /// <summary>
   /// Gets the  page number for the next group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   Int32 NextGroupStart { get; }
   #endregion
}

PagerInfo class.

Here, you have the code for the class implementing the IPagesInfo interface.

Notice the class is responsible of the calculations about page numbers displayed, if there are next or previous groups of pages, etc.

public class PagerInfo : IPagesInfo
{
   #region Fields
 
   /// <summary>
   /// Defines the lastPageNumber.
   /// </summary>
   internal Int32 lastPageNumber = 0;
 
   #endregion
 
   #region Properties
   public Int32 TotalPages
   {
      get => (Int32)Math.Ceiling(TotalItems / (Double)PageSize);
   }
   /// <summary>
   /// Gets the page number for the previous group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   public Int32 PreviousGroupStart => LastPageNumber - NumberOfLinks;
   /// <summary>
   /// Gets the  page number for the next group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   public Int32 NextGroupStart => LastPageNumber + 1;
 
   /// <summary>
   /// Gets or sets the current page.
   /// </summary>
   public System.Int32 CurrentPage { getset; }
 
   /// <summary>
   /// Gets the current first page number..
   /// </summary>
   public Int32 FirstPageNumber
   {
      get => LastPageNumber - (NumberOfLinks - 1);
   }
 
   /// <summary>
   /// Gets a value indicating whether this instance has next page.
   /// </summary>
   public Boolean HasNextGroup
   {
      get => CurrentPage + NumberOfLinks < TotalPages;
   }
 
   /// <summary>
   /// Gets a value indicating whether this instance has previous page.
   /// </summary>
   public Boolean HasPreviousGroup
   {
      get => CurrentPage > NumberOfLinks;
   }
 
   /// <summary>
   /// Gets the last page number..
   /// </summary>
   public Int32 LastPageNumber
   {
      get
      {
         lastPageNumber = (Int32)Math.Ceiling((Double)CurrentPage / NumberOfLinks) * NumberOfLinks;
         if (lastPageNumber > TotalPages)
         {
            lastPageNumber = TotalPages;
         }
         return lastPageNumber;
      }
   }
 
   /// <summary>
   /// Gets or sets the number of links to show..
   /// </summary>
   public Int32 NumberOfLinks { getset; } = 10;
 
   /// <summary>
   /// Gets or sets the size of the page.....
   /// </summary>
   public System.Int32 PageSize { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows skipped from the top of the select.....
   /// </summary>
   public System.Int32 Skip { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows taken.....
   /// </summary>
   public System.Int32 Take { getset; }
 
   /// <summary>
   /// Gets or sets the amount of items to display.
   /// </summary>
   public System.Int32 TotalItems { getset; }
 
   /// <summary>
   /// Gets the total pages available to display..
   /// </summary>
 
   #endregion
 
}

The Data Class.

This class will contain your data and the IPagesInfo properties. In this sample, it will be called PersonsPager.

Note: You can easily create it by executing the stored procedure, copying the result and in a new code window (for example, an empty one created for PersonsPager), paste it by the Edit – Paste Special-Past JSON as Classes.

Then replace the names autogenerated by your own and change the type for the second one for the PagesInfo class.

The pasted code names the main class as Rootobject, which has been renamed to PersonsPager.

The Pager class will be changed by PagesInfo, and the Pager class defined could be removed.

This is the final code for the PersonsPager class.

public class PersonsPager
{
 
   public List[] List { getset; }
   public PagerInfo Pager { getset; }
}
public class List
{
   public int BusinessEntityID { getset; }
   public string FirstName { getset; }
   public string MiddleName { getset; }
   public string LastName { getset; }
}

The Pager Component.

You must add a Razor Component. In this sample, it is called Pager.razor.

In the UI code, I use an UL tag, adding a button for previous group of pages (when the user moves beyond the first set of page numbers), buttons for the different page numbers, and a button for the next group as well.

For the buttons, the pagination, page-item, and page-link classes are used.

<nav class="text-center">
   <ul class="pagination">
      @{//Previous page group button.
 
         string className = "page-item " + (!PagesInfo.HasPreviousGroup ? " disabled" : ""); //If there is in previous group, the button will be disabled
         <li class="@className">
            <button class="page-link "
                    @onclick="@(()=>
                                   {
                                      if (PagesInfo.HasPreviousGroup)
                                        ChangePage(PagesInfo.PreviousGroupStart);
                                   }
               )">
               ⏪
            </button>
         </li>
      }
      @*Buttons for page numbers*@
      @for (Int32 i = PagesInfo.FirstPageNumber; i <= PagesInfo.LastPageNumber; i++)
      {
         int pageSelector = i;
 
         className = "page-item " + (i == PagesInfo.CurrentPage ? " active" : "");
         <li class="@className">
            <button class="page-link" @onclick="@(()=>ChangePage(pageSelector))">@string.Format("{0:00}"pageSelector)</button>
         </li>
      }
      @{//Next page group button.
         className = "page-item  " + (!PagesInfo.HasNextGroup ? " disabled" : "");
         <li class="@className">
            <button class="page-link " @onclick="@(()=>
                                                      { if (PagesInfo.HasNextGroup)
                                                            ChangePage(PagesInfo.NextGroupStart);
                                                      }
               )">
               ⏩
            </button>
 
         </li>
      }
   </ul>
</nav>

In the code section, parameters are defined for:

  • an instance of a class implementing the IPagerInfo
  • a value to persist the selected page
  • an EventCallback to notify the client page about the changes in the selection by the user.

Finally, the ChangePage function called every time the user clicks ant of the buttons is defined to change the selected page and notify the client page.

@code {
   [Parameter]
   public IPagesInfo PagesInfo { getset; }
   [Parameter]
   public int SelectedPage { get => PagesInfo.CurrentPage; set => PagesInfo.CurrentPage = value; }
   [Parameter]
   public EventCallback OnPageChange { getset; }
   void ChangePage(int newPage)
   {
      PagesInfo.CurrentPage = newPage;
      SelectedPage = newPage;
      OnPageChange.InvokeAsync(SelectedPage);
   }
}

The controller.

To get the information from the database, you will need an API REST controller which returns the JSON string from the stored procedure.

The method must be decorated with the HttpGet attribute to react when the client page code calls it.

[Route("/[controller]")]
[ApiController]
public class PersonDataController : ControllerBase
{
   public PersonDataController(IConfiguration configuration)
   {
      Configuration = configuration;
   }
 
   public IConfiguration Configuration { get; }
 
   [HttpGet]
   public async Task<stringGetPersons(int selectedPageint pageSize = 10)
   {
      using SqlConnection con = new SqlConnection(Configuration.GetConnectionString("aw"));
      try
      {
         SqlCommand com = new SqlCommand("[Person].[Person_GetforPager]"con);
         com.CommandType = System.Data.CommandType.StoredProcedure;
         com.Parameters.AddWithValue("@skip", (selectedPage == 0 ? 0 : selectedPage - 1* pageSize);
         com.Parameters.AddWithValue("@take"pageSize);
         con.Open();
         string values = (await com.ExecuteScalarAsync()).ToString();
         return values;
      }
      catch (System.Exception ex)
      {
 
         throw;
      }
   }
}

The client page.

A new component will be defined to display the data.

It will contain any type of display for your data (in the sample, it is just a UL list), and an instance of the Pager component.

As usual in a Web assembly app, you must check if you have data to display before performing the UI generation.

Moreover, you can decide if you need display the pager component in case of no more than one page is needed to display the information.

@inject HttpClient httpClient
<h3>Persons</h3>
@if (result != null)
{
   <div>@result.StatusCode</div>
   <div>@result.Content.ReadAsStringAsync().Result</div>
   <div>@httpClient.BaseAddress</div>
}
@if (personsPager != null && personsPager.List.Count() > 0)
{
   <ul>
      @foreach (var item in personsPager.List)
      {
         <li>@item.LastName</li>
 
      }
   </ul>
   @if (personsPager.Pager != null && personsPager.Pager.TotalPages > 1)
   {
      <Pager PagesInfo="PagesInfo" OnPageChange="ChangePage" />
      @**@
   }
}
else SelectedPage = 1;

The code of this page will call the controller get method to retrieve the information.

@code {
   PersonsPager personsPager;
   int SelectedPage;
   public IPagesInfo PagesInfo { get => personsPager.Pager; }
   HttpResponseMessage result;
   protected async override Task OnInitializedAsync()
   {
      await GetDataAsync();
   }
   async void ChangePage()
   {
      SelectedPage = personsPager.Pager.CurrentPage;
      //SelectedPage = PagesInfo.CurrentPage;
      await GetDataAsync();
      this.StateHasChanged();
 
   }
   async Task GetDataAsync()
   {
      //result = await httpClient.GetAsync($"PersonData?Selectedpage={SelectedPage}");
      personsPager = await httpClient.GetFromJsonAsync<PersonsPager>($"PersonData?Selectedpage={SelectedPage}");
 
   }
 
}

And this is the result:

Persons page with Pager sample
Persons page with Pager sample

Note: The “aw” connection string points to the AdventureWorks2017 database where the stored procedure from the previous post has been created.

BTW here is the sample