Tag Archives: vscode

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 .

Visual Studio Code Snippets to Make Coding EF Core a Little Simpler

I’ve been using the user snippet feature of Visual Studio Code to make it easier to get some of the code I commonly use for EF Core into my files. For example I have C# snippets for DbContext to create a constructor overload that takes in a DbContextOptions parameter, OnConfiguring or OnModeling . I have json snippets to add in the EFCore Commands dependency and the Tools section with EF Core tools.

github.com/julielerman/EFCoreSnippetsForVSCode

I finally created a github repository to share them. Since you’ll need to add the csharp snippets into your existing csharp snippets and the same with the json, i have put them into separate csharp.json and json.json files from which you can copy and past my snippets into your own.

Although the instructions are on the user defined snippets page I just linked to, the TL;DR is:

From menu, choose Code, Preferences then User Snippets

2016-10-30_11-56-58

That will open a list of snippet files. Choose C# for the C# snippets and Json for the Json snippets. Paste in my snippets!

2016-10-30_11-57-25