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(100) select 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<string> GetCountryData( 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(0, 10)}')) 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<string> GetCountryData( 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.
Universidad Net 
5 comentarios en “GENERANDO UNA API REST (JSON) [2]”