Blog

Querying a Stored Procedure with EF Core

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.

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace MyApp.Database.Migrations
{
  public partial class ExportDataMigration : Migration
  {
  	protected override void Up(MigrationBuilder migrationBuilder)
  	{
  		var sp = @"
  				CREATE PROCEDURE [dbo].[ExportData]	
  				AS
  				BEGIN

  				-- Add your script here
  				END	
  				GO";

  		migrationBuilder.Sql(sp);

  	}


  	protected override void Down(MigrationBuilder migrationBuilder)
  	{
  		migrationBuilder.Sql("DROP PROCEDURE [dbo].[ExportData]");
  	}
  }
}

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.

namespace Database
{
  public partial class ExportData
  {
  	public Guid ParticipantId { get; set; }
  	public DateTime StartTime { get; set; }
  	public DateTime? EndTime { get; set; }
  	public int? DurationSeconds { get; set; }
  	public int? LastQuestionViewed { get; set; }
  	public string? Source { get; set; }
  	public string? Country { get; set; }
  	public string? Age { get; set; }
  }
}

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.

using Microsoft.EntityFrameworkCore;

namespace Database
{
  public partial class MyContext : DbContext
  {
      public MyContext()
      {
      }

      public MyContext(DbContextOptions<MyContext> options)
          : base(options)
      {
      }

      //... Other DbSets removed

      public virtual DbSet<ExportData> ExportData { get; set; } = null!;

      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
          //... other code removed for clarity
      }

      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          //... other code removed for clarity

          modelBuilder.Entity<ExportData>(entity =>
          {
              entity.HasKey(e => e.ParticipantId);
          });

          OnModelCreatingPartial(modelBuilder);
      }

      partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
  }
}

Calling the stored procedure with EF Core

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

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

Export CSV in ASP.NET Core Web API

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.

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

using (var memoryStream = new MemoryStream())
{
  using (var streamWriter = new StreamWriter(memoryStream))
  using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
  {
     csvWriter.WriteRecords(data);
  }
}

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:

  1. The Get method return type is Task<FileResult> to define that the return type should be a file.
  2. In the attributes I have [Produces("text/csv")] to inform the swagger docs that this API returns CSV files.
  3. The final response is calling return File.
using CsvHelper;
using MyApp.Database;
using Microsoft.AspNetCore.Mvc;
using System.Globalization;
using Microsoft.EntityFrameworkCore;

namespace API.Controllers
{
  [Route("api/[controller]")]
  [ApiController]
  public class CSVExportController : ControllerBase
  {
      private readonly Context _context;

      public CSVExportController(Context context)
      {
          _context = context;
      }

      // GET: api/CSVExport
      [HttpGet]
      [ProducesResponseType(StatusCodes.Status200OK)]
      [Produces("text/csv")]
      public async Task<FileResult> Get()
      {

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

          using (var memoryStream = new MemoryStream())
          {
              using (var streamWriter = new StreamWriter(memoryStream))
              using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
              {
                  csvWriter.WriteRecords(data);
              }

              return File(memoryStream.ToArray(), "text/csv", $"Export-{DateTime.Now.ToString("s")}.csv");
          }
      }

  }
}

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

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.

Prismic custom type redirect

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.

(node:21168) Warning: To load an ES module, set &quot;type&quot;: &quot;module&quot; in the package.json or use the .mjs extension.
(Use `node --trace-warnings ...` to show where the warning was created)
error - Failed to load next.config.js, see more info here https://nextjs.org/docs/messages/next-config-error
C:\GitHub\TeamSport2022\next.config.js:1
import * as prismic from &#39;@prismicio/client&#39;
^^^^^^

SyntaxError: Cannot use import statement outside a module
  at Object.compileFunction (node:vm:352:18)
  at wrapSafe (node:internal/modules/cjs/loader:1032:15)
  at Module._compile (node:internal/modules/cjs/loader:1067:27)
  at Object.Module._extensions..js (node:internal/modules/cjs/loader:1157:10)
  at Module.load (node:internal/modules/cjs/loader:981:32)
  at Function.Module._load (node:internal/modules/cjs/loader:822:12)
  at ModuleWrap.&lt;anonymous&gt; (node:internal/modules/esm/translators:168:29)
  at ModuleJob.run (node:internal/modules/esm/module_job:197:25)
  at async Promise.all (index 0)
  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.

import { createClient } from &#39;./prismicio.js&#39;
       ^^^^^^^^^^^^
SyntaxError: Named export &#39;createClient&#39; not found. The requested module &#39;./prismicio.js&#39; is a CommonJS module, which may not support all module.exports as named exports.
CommonJS modules can always be imported via the default export, for example using:

import pkg from &#39;./prismicio.js&#39;;
const { createClient } = pkg;

  at ModuleJob._instantiate (node:internal/modules/esm/module_job:127:21)
  at async ModuleJob.run (node:internal/modules/esm/module_job:193:5)
  at async Promise.all (index 0)
  at async ESMLoader.import (node:internal/modules/esm/loader:337:24)
  at async importModuleDynamicallyWrapper (node:internal/vm/module:437:15)
  at async Object.loadConfig [as default] (C:\GitHub\TeamSport2022\node_modules\next\dist\server\config.js:68:36)
  at async NextServer.prepare (C:\GitHub\TeamSport2022\node_modules\next\dist\server\next.js:127:24)
  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.

import * as prismic from '@prismicio/client'

const apiEndpoint = process.env.PRISMIC_API_ENDPOINT
export const repositoryName = prismic.getRepositoryName(apiEndpoint)

export const createClient = (config = {}) => {
const client = prismic.createClient(apiEndpoint, {
  undefined,
  ...config,
})

return client
}

async function redirects() {
const client = createClient()
const redirects = await client.getAllByType('redirect')

return redirects.map((r) => {
  return {
    source: r.data.from_url,
    destination: r.data.to_url,
    permanent: r.data.redirect_type == 'Permanent',
  }
})
}

/**
* @type {import('next').NextConfig}
*/
const nextConfig = {
/* config options here */
redirects,
}

export default nextConfig

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.

Tagged: