EF Core, Postgres and the Camel-Cased Identity Tables

PostgreSQL doesn’t really like camel-case too much. I’m no expert but I know that at least.

I was doing yet another exploration of  EF Core in Visual Studio Code on my MacBook. Usually I use JetBrain’s DataGrip (database IDE) to check out the actual data. But this time I wanted to play with  the Visual Studio Code extension called “vscode-database” which lets you interact with MySQL and PostgreSQL right in the IDE.

There are a bunch of database extensions in fact:

image

I’ve already been using the mssql extension to muck about with SQL Server data inside of VS Code:

As I was using a default web app from the template, ASP.NET Identity was involved and the context to manage identity inherits from Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityDbContext

And IdentityDbContext has fluent API code to explicitly map table names which are not the same as the entity’s they are mapping. And it makes those names all camel-cased.

DataGrip did not mind the camel cased identity table names but vscode-database didn’t comprehend the table names when it was reading the database to build Intellisense for the tooling. It’s anticipating lowercase and throwing exceptions when it hits the camelcased names. Shay Rojansky, who maintains the npgsql PostgreSQL providers for EF & EF Core, explained to me that if the tool simply placed quotes around the names, it would be okay.

Update: The vscode-database maintainers have fixed the problem and you can now easily use the identity tables …just remember to put quotes around the camel-cased database objects when typing SQL. Although right now the installer hasn’t been updated with the new bits yet. I just fixed up the extension files manually for the time being.

If you prefer not to have to do that and do want the identity tables etc to be all lower case, then you may still find my hack to be useful.

For a temporary workaround,  I wanted to just change them to lower case so I could reap the benefits of vscode-database extension.

Since the IdentityDbContext class creates the table names in its OnModelCreating override, I needed to change those names to lower case after the fact.

It’s easy enough to do if you know the right path through the APIs.

Here’s my DbContext class that handles Identity in my ASP.NET Core app. I’ve added the foreach clause to lower case the name after the base class (IdentityDbContext) has already changed the names.

I’m grabbing the ToTable name that was specified by ApplicationDbContext and then re-applying the name as lower case.

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using WebApplication.Models;

namespace WebApplication.Data {

  public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
  {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) {
    }

    protected override void OnModelCreating(ModelBuilder builder) {
      base.OnModelCreating(builder);
      // Customize the ASP.NET Identity model and override the defaults if needed.
      // For example, you can rename the ASP.NET Identity table names and more.
      // Add your customizations after calling base.OnModelCreating(builder);
      //quick and dirty takes care of my entities not all scenarios
      foreach (var entity in builder.Model.GetEntityTypes()) {
        var currentTableName = builder.Entity(entity.Name).Metadata.Relational().TableName;
        builder.Entity(entity.Name).ToTable(currentTableName.ToLower());
      }
    }
  }
}

vscode-database has some cool features with Intellisense. It has some UX issues to work on (or maybe I need some training and I’ll raise an issue just in case) but it’s free and let’s you execute queries and commands on the database for those times you don’t need the bells and whistles of a full IDE .

Leave a Reply

Your email address will not be published. Required fields are marked *