Blog

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