Blog

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.

Creating Redirects with Prismic and NextJs

7 December 2022

If you've done web development for any length of time, you'll know an important part of SEO is the ability to set up 301 Redirects on a page. The reason is simple, while pages exist people link to them, and they generate SEO value. When they're removed, the SEO value is removed, and even worse there could now be dead links existing pointing at that now removed page. The solution is to set up a redirect to that page that replaces it resulting in no dead links and the SEO value being transferred to the new page.

However, despite this being an almost essential part of any website, countless CMSs don't come with any inbuilt functionality to set them up and most guides will point you to config files requiring a developer to make the change. Not only does the average developer not want to spend their time adding redirects to config files, but content editors also don't want to have to wait for them to do it either.

Create a Document Type in Prismic

To give content editors the power to create redirects themselves, first, they will need a document type in Prismic with the relevant fields. The custom type needs to be repeatable so that the editor can enter multiple redirects, and it will need fields for source, destination and if the redirect is temporary or permanent.

For the redirect type, I've used a select drop-down with the default set to Permanent.

Using the redirects in NextJs

Since version 9.5.0 of NextJs, you have been able to set redirects in the next.config.js file (official NextJs documentation). What might not be immediately obvious though is the config file is a regular JS file so although all the examples are static hard-coded redirects there's no reason you couldn't just call a function that returns an array.

If like me, you're using TypeScript throughout your projects this is where the good news ends. The config file cannot be a TypeScript file and therefore if you've made a nice service layer with all your queries to the Prismic client, your logic for redirects, unfortunately, can't go there.

To create your client to retrieve content from Prismic, you likely import the createClient function from the prismicio.js file. Two things will trip you up here, the first will be an error about using import statements outside a module.

1(node:21168) Warning: To load an ES module, set "type": "module" in the package.json or use the .mjs extension.
2(Use `node --trace-warnings ...` to show where the warning was created)
3error - Failed to load next.config.js, see more info here https://nextjs.org/docs/messages/next-config-error
4C:\GitHub\TeamSport2022\next.config.js:1
5import * as prismic from '@prismicio/client'
6^^^^^^
7
8SyntaxError: Cannot use import statement outside a module
9    at Object.compileFunction (node:vm:352:18)
10    at wrapSafe (node:internal/modules/cjs/loader:1032:15)
11    at Module._compile (node:internal/modules/cjs/loader:1067:27)
12    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1157:10)
13    at Module.load (node:internal/modules/cjs/loader:981:32)
14    at Function.Module._load (node:internal/modules/cjs/loader:822:12)
15    at ModuleWrap.<anonymous> (node:internal/modules/esm/translators:168:29)
16    at ModuleJob.run (node:internal/modules/esm/module_job:197:25)
17    at async Promise.all (index 0)
18    at async ESMLoader.import (node:internal/modules/esm/loader:337:24)

Thankfully next's config file does support ECMAScript modules and you can rename the file to next.cofig.msj. Just make sure you follow all the other changes in Next's documentation.

The next will be an error message that looks helpful with a suggested change.

1import { createClient } from './prismicio.js'
2         ^^^^^^^^^^^^
3SyntaxError: Named export 'createClient' not found. The requested module './prismicio.js' is a CommonJS module, which may not support all module.exports as named exports.
4CommonJS modules can always be imported via the default export, for example using:
5
6import pkg from './prismicio.js';
7const { createClient } = pkg;
8
9    at ModuleJob._instantiate (node:internal/modules/esm/module_job:127:21)
10    at async ModuleJob.run (node:internal/modules/esm/module_job:193:5)
11    at async Promise.all (index 0)
12    at async ESMLoader.import (node:internal/modules/esm/loader:337:24)
13    at async importModuleDynamicallyWrapper (node:internal/vm/module:437:15)
14    at async Object.loadConfig [as default] (C:\GitHub\TeamSport2022\node_modules\next\dist\server\config.js:68:36)
15    at async NextServer.prepare (C:\GitHub\TeamSport2022\node_modules\next\dist\server\next.js:127:24)
16    at async C:\GitHub\TeamSport2022\node_modules\next\dist\cli\next-dev.js:142:9

But that will just result in the first error message again, but now for the prismicio file.

The solution I ended up with was to recreate some of the logic from the prismicio file to create the client. Thankfully none of the routing logic needs to be replicated so it's still fairly minimal code, the one downside is the API endpoint now needs to come from the .env file rather than Prismics sm.json file. I did try using the sm.json file, however that leads to another error around loading json files.

1import * as prismic from '@prismicio/client'
2
3const apiEndpoint = process.env.PRISMIC_API_ENDPOINT
4export const repositoryName = prismic.getRepositoryName(apiEndpoint)
5
6export const createClient = (config = {}) => {
7  const client = prismic.createClient(apiEndpoint, {
8    undefined,
9    ...config,
10  })
11
12  return client
13}
14
15async function redirects() {
16  const client = createClient()
17  const redirects = await client.getAllByType('redirect')
18
19  return redirects.map((r) => {
20    return {
21      source: r.data.from_url,
22      destination: r.data.to_url,
23      permanent: r.data.redirect_type == 'Permanent',
24    }
25  })
26}
27
28/**
29 * @type {import('next').NextConfig}
30 */
31const nextConfig = {
32  /* config options here */
33  redirects,
34}
35
36export default nextConfig
37

As you can see once the client is created, it's a simple case of retrieving the redirects and mapping them into the correct format.

Overall, I'm happy that the redirects can be added through Prismic and they use the official NextJs config for creating redirects. However, I'm not so keen that my solution now has two places where the API endpoint is defined and the query for loading redirects being in what is essentially a config file, rather than with all my others and benefitting from the type checking it would have received had it been in typescript.

Array parameter in WebAPI Get Request

24 November 2022

REST APIs are great, and ASP.NET Web API is a nice way of creating one. The auto-generated code that Visual Studio gives you will also get you 90% of the way there to knowing how to build one without reading much documentation.

However, while it will give you all the GET, POST, PUT, DELETE etc endpoints there's one thing it doesn't give you an example for, and that's query-string parameters on your GET request.

In the real world, the likelihood of you wanting the GET route to return everything is quite slim. You may have small datasets where this is true but more often than not, you're going to want some sort of query string values to use as a filter.

Fortunately, ASP.NET makes this simple with parameter binding. Just add them as parameters to your function.

1// GET: api/<People>
2[HttpGet]
3public IEnumerable<Person> Get(int? age, string? gender)
4{
5    ...
6}

Make sure to make them nullable if you don't want them as required fields.

Swagger will even pick up on them and include them as parameter options.

It's also likely that you'll want an array of values so that in my example you could select multiple ages or genders. However, if you just try turning them into arrays you will end up with this build error.

1'API.Controllers.PeopleController.Get (API)' has more than one parameter that was specified or inferred as bound from request body. Only one parameter per action may be bound from body. Inspect the following parameters, and use 'FromQueryAttribute' to specify bound from query, 'FromRouteAttribute' to specify bound from route, and 'FromBodyAttribute' for parameters to be bound from

If we only had one array things would be ok as the error is for specifying multiple. Except if we look at swagger things still aren't quite right.

The age parameter is now showing as coming from the body rather than the query-string. It also explains why you could only specify one array as a parameter.

The solution is to specify that these parameters should come from the query-string which we can do by using the [FromQuery] attribute.

1// GET: api/<PeopleController>
2[HttpGet]
3public IEnumerable<Person> Get([FromQuery] int[]? age, [FromQuery] string[]? gender)
4{
5     ...
6}

The solution will now run even with multiple array parameters, and swagger will also pick up that these are query-string array parameters and give you the option to add individual items when you test a request.