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(100) select 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;