Archivo de la categoría: General

enhanced truncate table


During the develop and testing of any applications, probably you need to clean up test data, in order to test from scratch.

In SQL Server, you have the TRUNCATE TABLE statement to perform this.

However, the statement execution will fail if the table has references to other tables.

This code snippet allows you to truncate a table, avoiding the problem, by removing the relations, truncating the table and reestablishing the relations.

sp_fkeys

In this code, I use the sp_fkeys system stored procedure, which retrieve the definition of any relationship between tables.

You can find the procedure documentation here.

The procedure is an old one, I mean, it exists in several SQL server versions.

That’s the reason some of the parameters are about owners. In older versions, the objects belong to users, and that is the reason for name them “owners”.

However, consider that when speaking about owners in objects, in fact we are talking about schemas.

The snippet

These are the steps the snippet performs.

  • The first step is declaring the schema and the name of the table to be truncated. (We will define how to manage this in a Stored procedure later).
DECLARE 
       @TableName    SYSNAME = 'WebCustomers'
     , @TableSchema  SYSNAME = 'OnLineSales';
  • Then, an in-memory table is declared, for the different columns the sp_fkeys stored procedure returns.
DECLARE 
       @FKSTableVarOri TABLE
 (
   [PKTABLE_QUALIFIER]  SYSNAME
 , [PKTABLE_OWNER]      SYSNAME
 , [PKTABLE_NAME]       SYSNAME
 , [PKCOLUMN_NAME]      SYSNAME
 , [FKTABLE_QUALIFIER]  SYSNAME
 , [FKTABLE_OWNER]      SYSNAME
 , [FKTABLE_NAME]       SYSNAME
 , [FKCOLUMN_NAME]      SYSNAME
 , [KEY_SEQ]            SMALLINT
 , [UPDATE_RULE]        SMALLINT
 , [DELETE_RULE]        SMALLINT
 , [FK_NAME]            SYSNAME
 , [PK_NAME]            SYSNAME
 , [DEFERRABILITY]      SMALLINT
);

When retrieving the information of the relationships, the return value uses one row for each column implied ion a relation. So, if a relation is using a composite key, there will be more than one row for the same relation.

It is needed to consolidate this, to define just one entry by relation, with the columns defines as a comma separated list, for the re-creation of them later.

  • a second in memory table is defined to process the consolidation.
DECLARE 
       @FKSTableVar TABLE
 (
   [PKTABLE_OWNER]  SYSNAME
 , [PKTABLE_NAME]   SYSNAME
 , [PKCOLUMN_NAME]  SYSNAME
 , [FKTABLE_OWNER]  SYSNAME
 , [FKTABLE_NAME]   SYSNAME
 , [FKCOLUMN_NAME]  SYSNAME
 , [UPDATE_RULE]    SMALLINT
 , [DELETE_RULE]    SMALLINT
 , [FK_NAME]        SYSNAME
)
  • Using the sp_fkeys procedure, the snippet get the child relationships of the table to be truncated, and store them in the fist in-memory table.
INSERT INTO @FKSTableVarOri
EXEC [sp_fkeys] 
     @pktable_name = @TableName
   , @pktable_owner = @TableSchema;

Then, by using the STRING_AGG function, the second in-memory table is filled with one row by each relationship.

  • The STRING_AGG function, allows you to concatenate values from different rows in one string, using a delimiter between values.
INSERT INTO @FKSTableVar
       SELECT 
           [PKTABLE_OWNER]
         , [PKTABLE_NAME]
         , STRING_AGG(
                      '[' + 
                      [PKCOLUMN_NAME] + 
                      ']', ',') AS [PKCOLUMN_NAME]
         , [FKTABLE_OWNER]
         , [FKTABLE_NAME]
         , STRING_AGG(
                      '[' + 
                      [FKCOLUMN_NAME] + 
                      ']', ',') AS [FKCOLUMN_NAME]
         , [UPDATE_RULE]
         , [DELETE_RULE]
         , [FK_NAME]
       FROM 
          @FKSTableVarOri
       GROUP BY 
           [FK_NAME]
         , [PKTABLE_OWNER]
         , [PKTABLE_NAME]
         , [FKTABLE_OWNER]
         , [FKTABLE_NAME]
         , [UPDATE_RULE]
         , [DELETE_RULE];
  • There is a chance no relationships exist. The snippet check if this happen, to proceed directly to the truncation of the table.
DECLARE 
       @References  SMALLINT;
 
SELECT 
    @References = COUNT(*)
FROM 
   @FKSTableVar;
 
IF @References > 0 --there are relationships
  BEGIN
  • It is possible that the tables using our candidate table as reference, have rows with referenced values. In that case, it is impossible to truncate the table, or the relationships will be broken. To avoid this, the snippet dynamically creates a script to sum de row-count of all the child tables. In case there are rows, the process aborts with an error.
DECLARE 
       @Counters  NVARCHAR(MAX);
SELECT 
    @Counters = STRING_AGG(
                           '(select count(*) from [' + 
                           [FKTABLE_OWNER] + 
                           '].[' + 
                           [FKTABLE_NAME] + 
                           '])', '+')
FROM 
   @FKSTableVar;
SET @Counters =
                'Select @ForeignRows=' + 
                @Counters;
PRINT @Counters;
DECLARE 
       @ForeignRows  INT;
EXEC [sp_executesql] 
         @Counters
   , N'@ForeignRows int OUTPUT'
   , @ForeignRows OUTPUT;
SELECT 
    @ForeignRows;
IF @ForeignRows > 0
  BEGIN
    RAISERROR(
    'There are dependent rows in other tables', 10, 1);
  END;
   ELSE
  • If it is ok to proceed, two dynamically generated scripts is built, using the STRING_AGG function again, one for drop the relationships of the table, and other for recreate them after the truncate.
DECLARE 
       @DropRefs    NVARCHAR(MAX)
     , @CreateRefs  NVARCHAR(MAX);
SELECT 
    @DropRefs = STRING_AGG(
                           'ALTER TABLE [' + 
                           [FKTABLE_OWNER] + 
                           '].[' + 
                           [FKTABLE_NAME] + 
                           '] DROP CONSTRAINT [' + 
                           [FK_NAME] + 
                           ']', ';')
FROM 
   @FKSTableVar;
SELECT 
    @CreateRefs = STRING_AGG(
                             'ALTER TABLE [' + 
                             [FKTABLE_OWNER] + 
                             '].[' + 
                             [FKTABLE_NAME] + 
                             ']  WITH CHECK ADD  CONSTRAINT ['
                             + 
                             [FK_NAME] + 
                             '] FOREIGN KEY(' + 
                             [FKCOLUMN_NAME] + 
                             ') REFERENCES [' + 
                             [PKTABLE_OWNER] + 
                             '].[' + 
                             [PKTABLE_NAME] + 
                             ']  (' + 
                             [PKCOLUMN_NAME] + 
                             ') ' + 
                                     (CASE [DELETE_RULE]
                                 WHEN 1
                                 THEN
                                 ' ON DELETE CASCADE '
                                 ELSE ''
                              END) + 
                                     (CASE [UPDATE_RULE]
                                 WHEN 1
                                 THEN
                                 ' ON UPDATE CASCADE '
                                 ELSE ''
                              END), ';')
FROM 
   @FKSTableVar;
  • The script for drop is executed.
        EXEC [sp_executesql] 
             @DropRefs;
END;
  • The script for truncate the table is built and executed.
DECLARE 
       @Truncate  NVARCHAR(MAX) =
                  N'TRUNCATE TABLE [' + 
                  @TableSchema + 
                  '].[' + 
                  @TableName + 
                  ']';
 
EXEC [sp_executesql] 
     @Truncate;
 
  • Finally, in case there were relationships, the script for recreating is executed, finishing the process.
IF @References > 0
  BEGIN --there are relationships
    EXEC [sp_executesql] 
         @CreateRefs;
  END;

Using the sp_executesql procedure

A special note about the use of sp_executesql system store procedure.
It can just execute a string containing a T-SQL script, but it can use parameters.
For that purpose, the parameters must be declared in a second string parameter and passed one by one as the next parameters.
Notice the string containing the script, as well as the parameters declaration MUST BE nvarchars.

The snippet as store procedure.

Of course, you can define this snippet as a stored procedure in your database during the devlopment process, to facilitate the execution.

I propose you to have it in a separate schema, as well as any other “tool” for your development work.

This can be defined by using the two first variables declared in the snippet as parameters of the procedure.

CREATE PROCEDURE [Development].[TruncateX] 
   @TableName    SYSNAME
 , @TableSchema  SYSNAME = NULL
AS
  BEGIN
    SET NOCOUNT ON;
    SET @TableSchema = ISNULL(@TableSchema, 'dbo'); -- assume the dbo schema by default

You can found the snippet and the Stored Procedure at my GitHub repository, SqlSamples.

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.

Demos Data


Lately, I have detected in social networks, various queries about the generation of datasets to expose through, for example, REST APIs.

Having answered some of them directly, I thought it better to leave this published, to facilitate the task of possible future doubts.

For this and other possible examples to come, I decided to generate a sample database, which can be built from freely used published data, by Our World in Data,regarding COVID-19.

This sample database, using real data but not containing personal information of any kind, will allow me to set out some issues related to real-world work. For example, that the volume of data to be used may be large, or that the structure of the data is not always in accordance with what we would like to have.

For the generation of such a database, I decided to create a Jupyter Notebook,which is available  here. On the same share, I also left a  backpac file that allows you to import the sample base directly into a SQL Server.

However, I also left the Notebook, because that way you can run it to generate the database with updated data, as many times as you want.

Here I will leave as a reference, those other publications that make use of this database.

Generating a REST API (JSON)

2021-06-24

Generating a REST API (JSON) [2]

2021-06-25