Tired of writing DTO classes just so that you can get some reporting data from the database into your application.
Eye fatigue from seeing all of those DTO classes cluttering up your well laid out domain model.
Well, look no further…..
The Brief
I am working on a new project to build and display reporting data based on our customer online ordering system.
Initially, it will be displayed within a management section of the ordering system, but I would also like to show it on a separate mobile app.
I have created a new set of SQL database tables, views and stored procedures to slice and dice the data for the reports.
Technologies
.Net 6
Entity Framework Core 7
Azure SQL Database
The way of the DTO
Previously, this would have required me to create DTO classes to match the schema of the data returned from the database calls. There was quite some time spent creating these DTOs that were only ever used to get the data from the database to the web page.
Some googling and tinkering later, I come up with a solution using SQL Server’s ability to format query results as JSON with FOR JSON.
Taking the initial, pre-JSON’ed, stored procedure
CREATE Procedure [dbo].[REP_AreaProfitability] @fromDate datetime, @toDate datetime AS
SELECT Area, SUM(Sale - Cost)*100/SUM(Sale) GrossMargin
FROM CustomerProfitability CP
WHERE DateSubmitted >= @fromDate AND DateSubmitted <= EOMONTH(@toDate)
GROUP BY Area
I would need the following C# DTO class created in the web api project:
using Microsoft.EntityFrameworkCore;
[Keyless]
public class AreaProfitability
{
public string Area { get; set; } = null!;
public decimal GrossMargin { get; set; } = null!;
}
along with the following configurations in the applications’s DbContext implementation:
using Microsoft.EntityFrameworkCore;
public partial class CamDirDbCtx : DbContext
{
public CamDirDbCtx() { }
public CamDirDbCtx(DbContextOptions<CamDirDbCtx> options) : base(options) { }
public virtual DbSet<AreaProfitability> AreaProfitabilities { get; set; } = null!;
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<AreaProfitability>(entity =>
{
entity.Property(e => e.DeliveryArea).HasMaxLength(100).IsUnicode(false);
entity.Property(e => e.GrossMargin).HasColumnType("decimal(12, 2)");
});
OnModelCreatingPartial(modelBuilder);
}
}
to allow me to retrieve the data from the database:
using Microsoft.EntityFrameworkCore;
public class AreaProfitabilityQuery
{
public List<AreaProfitability> results;
public AreaProfitabilityQuery(DbCtx dbCtx ,string fromDate, string toDate)
{
var fDate = DateTime.Parse(fromDate + "-01").ToString("yyyy-MM-dd");
var tDate = DateTime.Parse(toDate + "-01").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
results = dbCtx.AreaProfitabilities.FromSqlRaw($"EXECUTE dbo.REP_AreaProfitability '{fDate}','{tDate}'").ToList();
}
}
That’s a lot of boilerplate code to get a very basic set of data.
Enter the JSON
Changing the stored procedure to the following….
CREATE Procedure [dbo].[REP_AreaProfitability] @fromDate datetime, @toDate datetime AS
SELECT
(
SELECT Area, SUM(Sale - Cost)*100/SUM(Sale) GrossMargin
FROM CustomerProfitability CP
WHERE DateSubmitted >= @fromDate AND DateSubmitted <= EOMONTH(@toDate)
GROUP BY Area
ORDER BY 2 DESC
FOR JSON AUTO
) AS JSON
and reconfiguring the query class….
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
public class AreaProfitabilityQuery
{
public string results ="";
private CamDirDbCtx dbCtx;
private List<DbParameter> queryParams = new List<DbParameter>();
public DeliveryAreaProfitabilityQuery(CamDirDbCtx db, string fromDate, string toDate)
{
var fDate = DateTime.Parse(fromDate + "-01").ToString("yyyy-MM-dd");
var tDate = DateTime.Parse(toDate + "-01").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
queryParams.Add(new SqlParameter("@fromDate", SqlDbType.VarChar) { Value = fDate });
queryParams.Add(new SqlParameter("@toDate", SqlDbType.VarChar) { Value = tDate });
this.dbCtx = db;
}
public async Task<DeliveryAreaProfitabilityQuery> Execute()
{
DbConnection connection = dbCtx.Database.GetDbConnection();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "EXEC REP_DeliveryAreaProfitabilityForDateRange @fromDate, @toDate";
cmd.Parameters.AddRange(queryParams.ToArray());
if (connection.State.Equals(ConnectionState.Closed)) { connection.Open(); }
results = (string)await cmd.ExecuteScalarAsync();
}
if (connection.State.Equals(ConnectionState.Open)) { connection.Close(); }
return this;
}
}
allows me to execute it from the web api project like so….
app.MapGet("/areaProfitability", async (DbCtx dbCtx, [FromQuery(Name = "from")] string fromDate, [FromQuery(Name = "to")] string toDate) =>
{
var dlyAreaProfit = (await new AreaProfitabilityQuery(dbCtx, fromDate, toDate).Execute()).results;
return new { dlyAreaProfit = dlyAreaProfit };
}
No intermediary DTO class cluttering up the project.
Nested JSON Objects
When updating another query API endpoint to use this approach, I got an unexpected nesting of some of the properites in the JSON string. Tracked it down to the JOIN’ing of tables in the SELECT query, as described by Microsoft here.
The following query:
CREATE Procedure [dbo].[REP_AreaProfitability] @fromDate datetime, @toDate datetime AS
SELECT (
SELECT Area, SUM(Sale - Cost)*100/SUM(Sale) GrossMargin, C.CustomerName
FROM CustomerProfitability CP
JOIN Customer C ON C.CustomerID = CP.CustomerID
WHERE DateSubmitted >= @fromDate AND DateSubmitted <= EOMONTH(@toDate)
ORDER BY 2 DESC
FOR JSON AUTO
) AS JSON
….will return this JSON object, with a nested property, C, for the customer name:
[{"Area":"A1","GrossMargin":11.11,"C":[{"CustomerName":"A1 Customer"}]},
{"Area":"B2","GrossMargin":22.22,"C":[{"CustomerName":"B2 Customer"}]}]
Interestingly, amending the order of the columns in the SELECT command effects the properties that are nested:
CREATE Procedure [dbo].[REP_AreaProfitability] @fromDate datetime, @toDate datetime AS
SELECT (
SELECT Area, C.CustomerName, SUM(Sale - Cost)*100/SUM(Sale) GrossMargin
FROM CustomerProfitability CP
JOIN Customer C ON C.CustomerID = CP.CustomerID
WHERE DateSubmitted >= @fromDate AND DateSubmitted <= EOMONTH(@toDate)
ORDER BY 2 DESC
FOR JSON AUTO
) AS JSON
The GrossMargin property is now part of the C nested object.
[{"Area":"A1","C":[{"CustomerName":"A1 Customer"},"GrossMargin":11.11]},
{"Area":"B2","C":[{"CustomerName":"B2 Customer"},"GrossMargin":22.22]}]
Now, I didn’t want this nesting, as I am merely want to display each record as a row in a report. A simple answer I found on StackOverflow was to replace the use of FOR JSON AUTO with FOR JSON PATH:
CREATE Procedure [dbo].[REP_AreaProfitability] @fromDate datetime, @toDate datetime AS
SELECT (
SELECT Area, C.CustomerName, SUM(Sale - Cost)*100/SUM(Sale) GrossMargin
FROM CustomerProfitability CP
JOIN Customer C ON C.CustomerID = CP.CustomerID
WHERE DateSubmitted >= @fromDate AND DateSubmitted <= EOMONTH(@toDate)
ORDER BY 2 DESC
FOR JSON PATH
) AS JSON
References:
https://stackoverflow.com/questions/51087037/sql-server-json-truncated-even-when-using-nvarcharmax
https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16
https://dotnetthoughts.net/how-to-execute-storedprocedure-in-ef-core/
https://www.talkingdotnet.com/how-to-execute-stored-procedure-in-entity-framework-core/
https://www.stevejgordon.co.uk/introduction-to-httpclientfactory-aspnetcore