Web Development

Combining GROUP BY and CASE in a Linq Query

19 January 2023

With SQL Server you have the ability to make changes to the raw table field data within a GROUP BY clause by using a CASE statement. e.g.

1SELECT [QuestionId]
2      ,CASE WHEN Answer LIKE 'Other%' THEN 'Other'
3			ELSE Answer END AS [Answer]
4      ,count(*) as Count
5  FROM [SurveyResults-Q4-2022]
6  GROUP BY	QuestionId, 
7		CASE WHEN Answer LIKE 'Other%' THEN 'Other'
8		     ELSE Answer END

When using EF Core though rather than writing SQL directly we write Linq queries which get translated to the appropriate SQL query.

To achieve a CASE statement in a GROUP BY you can write the following.

1var q = from r in _context.SurveyResultsQ42022s
2        group r by new { r.QuestionId, Answer = (r.Answer.StartsWith("Other") ? "Other" : r.Answer) }
3        into g
4        select new { g.Key.QuestionId, g.Key.Answer, Count = g.Count() };

Turning flat data into a hierarchy using C#

13 January 2023

Sometimes you have flat data and what you really want is a hierarchy. This can often happen when data is stored in a relational database that you want to return as JSON in an API. One SQL call will return a flat structure, but as JSON can give a complete hierarchy it makes more sense to convert it.

Let's assume we have the following as our source data:

1{
2  Country: "UK",
3  City: "London",
4  Population: 8800000
5}, {
6  Country: "UK",
7  City: "Edinburgh",
8  Population: 495400
9}, {
10  Country: "France",
11  City: "Paris",
12  Population: 2244000
13}

What we want to create is a structure like this:

1{
2  Country: "UK",
3  Cities: [
4    {
5      City: "London",
6      Population: 8800000
7    }, {
8      City: "Edinburgh",
9      Population: 495400
10    }]  
11}, {
12  Country: "France",
13  Cities: [
14    {
15      City: "Paris",
16      Population: 2244000
17   }]
18}

To make the conversion of flat data to a hierarchy using C# we can use a LInq expression.

First I need two models to represent the final structure. One to represent the Country and the other to represent the City. The Country class contains a list of cities.

1public class Country {
2    public string Country { get; set; }
3    public List<City> Cities{ get; set; }
4}
5
6public class City {
7    public string City { get; set; }
8    public int Population { get; set; }
9}

The following Linq query will then create a list of Countries populating the City list by doing a sub-select on the original dataset.

1// flatData contains our flat data
2var groupedByCountry = flatData.ToList()
3   .GroupBy(x => new { x.Country })
4   .Select(y => new Country() { 
5       Country = y.Key.Country, 
6       Cities = y.Select(c => new City() { 
7            City = c.City, 
8            Population = c.Population }).ToList() 
9   });

Querying a Stored Procedure with EF Core

5 January 2023

If like me, you're a fan of EF Core using code first with migrations you may run into the scenario where you need to call a stored procedure to get some data. This doesn't happen particularly often, and in my case, the only reason I needed to do it was to be able to use SQL Servers PIVOT function to turn rows into columns which sadly at the time of writing can not be expressed in LINQ.

Adding the Stored Procedure with Migrations

Unlike regular table changes, you cannot define a stored procedure in C# and then have a tool create the migration file for you. That said, their not particularly hard to write yourself.

Create a migration file as follows with a method to create the stored proc and another to remove it for rollbacks.

1using System;
2using Microsoft.EntityFrameworkCore.Migrations;
3
4#nullable disable
5
6namespace MyApp.Database.Migrations
7{
8	public partial class ExportDataMigration : Migration
9	{
10		protected override void Up(MigrationBuilder migrationBuilder)
11		{
12			var sp = @"
13					CREATE PROCEDURE [dbo].[ExportData]	
14					AS
15					BEGIN
16
17					-- Add your script here
18					END	
19					GO";
20
21			migrationBuilder.Sql(sp);
22
23		}
24
25
26		protected override void Down(MigrationBuilder migrationBuilder)
27		{
28			migrationBuilder.Sql("DROP PROCEDURE [dbo].[ExportData]");
29		}
30	}
31}
32

When EF Core calls the Stored Proc, it's going to want to have a type definition for the response. So create a model with the right properties. In my case it's as follows but yours will be different.

1namespace Database
2{
3    public partial class ExportData
4    {
5		public Guid ParticipantId { get; set; }
6		public DateTime StartTime { get; set; }
7		public DateTime? EndTime { get; set; }
8		public int? DurationSeconds { get; set; }
9		public int? LastQuestionViewed { get; set; }
10		public string? Source { get; set; }
11		public string? Country { get; set; }
12		public string? Age { get; set; }
13	}
14}
15

To avoid an error when calling the stored proc, we also need to add this model to our DbContext as a DbSet with a primary key, just don't make it a table.

1using Microsoft.EntityFrameworkCore;
2
3namespace Database
4{
5    public partial class MyContext : DbContext
6    {
7        public MyContext()
8        {
9        }
10
11        public MyContext(DbContextOptions<MyContext> options)
12            : base(options)
13        {
14        }
15
16        //... Other DbSets removed
17
18        public virtual DbSet<ExportData> ExportData { get; set; } = null!;
19
20        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
21        {
22            //... other code removed for clarity
23        }
24
25        protected override void OnModelCreating(ModelBuilder modelBuilder)
26        {
27            //... other code removed for clarity
28
29            modelBuilder.Entity<ExportData>(entity =>
30            {
31                entity.HasKey(e => e.ParticipantId);
32            });
33
34            OnModelCreatingPartial(modelBuilder);
35        }
36
37        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
38    }
39}
40

Calling the stored procedure with EF Core

To call the stored procedure we use the FromSqlRaw method on the DbSet in the content.

1var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();

Export CSV in ASP.NET Core Web API

29 December 2022

A common request for any application containing data, is for that data to be exportable to a CSV file so that users can easily use the data applications such as Excel.

Over the years I've seen many developers create and even try to read CSVs by assuming a CSV is as simple as a bunch of values with a comma between them. Now while a data in this format is a valid CSV, it's not the only possibility. For instance what if one of the values contains a comma? The CSVs spec supports all these scenarios and as a result, if you want to do anything with CSV files, I'd always use a library that has already been written to handle it for you.

Creating a CSV

The first part of exporting a CSV in ASP.NET is to first have a CSV file to export. To create the file I'm using a library called CsvHelper.

As good as this is, quite annoyingly the only instructions are for saving a file to a disc. While I'm sure this is relevant for many people, I'd rather hold it in memory and then return it.

In this example, I'm doing an EF Core query to a stored procedure to create my result set and then using a stream writer to a memory stream for the CSV writer to write to.

1var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
2
3using (var memoryStream = new MemoryStream())
4{
5    using (var streamWriter = new StreamWriter(memoryStream))
6    using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
7    {
8       csvWriter.WriteRecords(data);
9    }
10}

Export the CSV File from a Controller

I now have some code which has loaded my CSV into a memory stream. The next part is to create a controller that returns the file.

This is a regular controller inheriting from ControllerBase, but there's a few things to note:

The Get method return type is Task<FileResult> to define that the return type should be a file.
In the attributes I have [Produces("text/csv")] to inform the swagger docs that this API returns CSV files.
The final response is calling return File.

1using CsvHelper;
2using MyApp.Database;
3using Microsoft.AspNetCore.Mvc;
4using System.Globalization;
5using Microsoft.EntityFrameworkCore;
6
7namespace API.Controllers
8{
9    [Route("api/[controller]")]
10    [ApiController]
11    public class CSVExportController : ControllerBase
12    {
13        private readonly Context _context;
14
15        public CSVExportController(Context context)
16        {
17            _context = context;
18        }
19
20        // GET: api/CSVExport
21        [HttpGet]
22        [ProducesResponseType(StatusCodes.Status200OK)]
23        [Produces("text/csv")]
24        public async Task<FileResult> Get()
25        {
26
27            var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
28
29            using (var memoryStream = new MemoryStream())
30            {
31                using (var streamWriter = new StreamWriter(memoryStream))
32                using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
33                {
34                    csvWriter.WriteRecords(data);
35                }
36
37                return File(memoryStream.ToArray(), "text/csv", $"Export-{DateTime.Now.ToString("s")}.csv");
38            }
39        }
40
41    }
42}
43

In the parameters for the File method I'm calling .ToArray() on the memory stream to turn it into a byte array. I'm also specifying the content type and a filename containing the current datetime. The .ToString("s") on the datetime will format the date into a sortable string.