All posts by Julie Lerman

New Pluralsight Course: Interacting with SQL Server data in Visual Studio Code on Win, Mac, Linux

Tools, , , , , , , ,

imageMy latest course on Pluralsight, Cross-platform SQL Server Management for Developers using VS Code, went live earlier this month (just as I was about to hop on a plane for 2 weeks of conference travel!)

This is a course on a Visual Studio Code extension that I enjoy using so much that I wanted to share it with you. It is the mssql extension which lets you interact with SQL Server in a fairly rich way that belies the lightness of the IDE which it extends. Because VS Code is cross-platform, so are all of its extensions. So you can use this while you are coding on Windows, Mac or Linux and want to do some basic interaction with a SQL Server database.

As SQL Server examples, I used SQL Server LocalDb on Windows, SQL Server for Linux in a Docker container on a Mac and Azure SQL in the cloud. The course starts not only y showing you how to install VS Code (and some VS Code basics) and the extension but also by walking you through how to set up each of the database servers. That means it also has a lesson on Docker , installing and running an image as well as a quick start on creating a new SQL database in the Azure portal.

Once everything is set up, I dig through the features and functionality of the mssql extension. And I turned over ever possible stone to make sure you don’t miss helpful features which is the norm if you just start using such a tool without any preparation.

The course is mostly demos and very light on Powerpoint slides and I do work in Windows, on my Macbook and even in a Linux virtual machine.

imageWhat I’m also proud about this course is that if you’ve never used VS Code before, you’ll learn how to get around this amazing editor. If you’ve never used Docker before, I provide a really helpful and gentle introduction and you’ll be able to work with it. I had some great support from the team responsible for this extension as they were so happy to have this kind of attention paid to it.

So whatever language you code in, whatever O/S you work on, if you are using SQL Server (or interested in using it), this course should be a great help in mastering this very handy extension!

Below is the Table of Contents for the course.

If you need a 30-day free trial to the Pluralsight library so that you can watch this, send me a note!

Module 1: Introducing and Installing VS Code and the mssql Extension

In this module you’ll get a short introduction to the cross-platform and free developer IDE, Visual Studio Code and its mssql extension, The extension allow you to perform some key interactions with a SQL Server database without leaving the IDE. You’ll also walk through installing both the IDE and the extension on Windows and macOS

  • Module and Course Overview
  • Introducing Visual Studio Code and the mssql Extension
  • Installing Visual Studio Code on Windows
  • Installing Visual Studio Code on macOS
  • Introducing Visual Studio Code’s Coding Super Powers
  • Installing the mssql Extension in VS Code

 

 

Module 2: Preparing SQL Server for Any Platform, Locally and in the Cloud

In this module, you’ll learn how to set up a variety of SQL Servers. All of them are quick to install. You’ll learn to install SQL Server LocalDb on Windows, create an Azure SQL Database in the cloud and use a Docker image of SQL Server for Linux to quickly spin up SQL Server in a container on macOS. This will ensure that you have a SQL Server database to interact with in the rest of the course.

  • SQL Server LocalDB: The Simplest SQL Server
  • Setting Up an Azure SQL Database in the Cloud
  • Verifying the New Azure SQL Database
  • Setting Up the Last Details of Your Azure SQL Database
  • Using a Docker Container to Host SQL Server for Linux on Any O/S
  • Installing Docker and Getting the SQL Server Container Running
  • Verifying the Containerized Database
  • Understanding Persistence and Lack of Persistence in Containers
  • Pulling a Custom Image with the Sample Database in Place

 

Module 3: Connecting to the Various SQL Servers From Various Platforms

In this module, you’ll learn the ins and outs of connecting to a variety of local, cloud and containerized SQL Servers with the mssql extension. You’ll learn how to use the commands and shortcuts, how connection profiles and passwords are stored and even how to create a handy shortcut for getting mssql started.

  • mssql’s Commands and Execution Engine
  • Connecting to LocalDB While Learning More About mssql Connections
  • Connecting to Azure SQL from Windows and macOS
  • Demonstrating How mssql Securely Stores Your Passwords
  • Using ADO.NET Connection Strings to Connect
  • Connecting to the Database in the Docker Container
  • Connection Keyboard and Status Bar Shortcuts
  • Creating a Keyboard Shortcut to Start up mssql and Connect

Module 4 : Learning the mssql Basics to Connect, Query and Create

In this module you’ll start interacting with the database, executing queries and commands, and exploring  result sets. You’ll learn about the snippets and also learn about attaching to existing database files and creating new databases from scratch. Most importantly  you’ll learn about the great SQL editor and result view support that the mssql extension brings to you.

  • Attaching an Existing Database File
  • Interacting with the Results of Your First Query
  • The Intelligent Editor Window
  • Using Snippets to Speed Up Command Building
  • Exploring Multiple Result Sets Further
  • Using Snippets to See Database Metadata
  • Creating Databases, Tables and Data

Module 5: Leveraging Advanced Tips & Tricks

This module will dig deeper into mssql and provide tips that take advantage not just of mssql features, but also capabilities of Visual Studio Code to make using mssql easier.

  • Exporting Results to CSV, JSON or Excel
  • Localization of mssql’s Messages
  • Controlling Behavior Through VS Code’s Settings
  • Formatting Code in the Editor Window
  • Results Window Tricks, Shortcuts & Settings
  • Checking Out the Last Few Settings
  • Creating Your Own SQL Snippets in VS Code
  • Looking Ahead to Integrated Authentication on Mac and Linux

What’s in that sqlservr.sh file on the mssql-sqlserver-linux docker image anyway?

Data Access, ,

Update June 3, 2017: The team has revised the docker image and the bash file is gone, presumably with its logic broken up in to various locations. Still I’m glad I grabbed this when I did to satisfy my curiosity!

Microsoft has created 4 official Docker images for SQL Server: SQL Server for Linux, SQL Server Developer Edition, SQL Server Express and (windows) SQL Server vNext) . They can be found on the Docker hub (e.g. https://hub.docker.com/r/microsoft/mssql-server-linux/) and there is also a Github repository for them at github.com/Microsoft/mssql-docker. Some of the files that go along with that image are not on Github. The Dockerfile files for each image run some type of startup script. The Windows images have a PowerShell script called start.ps1. You can see those in the Github repo. The Linux image runs a bash file called sqlservr.sh. That’s not included in the repo though and I was curious what it did.

Note: I wrote a blog post about using the SQL Server for Linux container (Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code)  and I’m also writing an article about using the containers for my July MSDN Magazine Data Points column (watch this space).

Still a bit of a bash noob, I learned how to read a file from a docker container on ..you guessed it…StackOverflow.  Following those instructions, I created a snapshot of my running container

➜ MySqlServerLinuImage git:(master) docker commit juliesqllinux  mysnapshot

sha256:9b552a1e24df7652af0c6c265ae5e2d7cb7832586c431d4b480c30663ab713f0

and ran the snapshot with bash:


➜  MySqlServerLinuImage git:(master) docker run -t -i mysnapshot bin/bash

root@2a6f950face2:/#

Then at the new prompt (#), used ls to get the listing

root@2a6f950face2:/# ls

SqlCmdScript.sql  SqlCmdStartup.sh  bin  boot  dev  entrypoint.sh  etc  home  install.sh  lib  lib64  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var

then navigated to  folder where the bash file is and listed its contents:

root@2a6f950face2:/opt/mssql/bin# ls

compress-dump.sh  generate-core.sh  mssql-conf  paldumper  sqlpackage  sqlservr  sqlservr.sh

Once I was there I used the cat command to list out the contents of the sqlservr.sh file and see what it does. Here is the secret sauce in case, like me, you NEED to know what’s going on under the covers!

root@2a6f950face2:/opt/mssql/bin# cat sqlservr.sh 

#!/bin/bash

#

# Microsoft(R) SQL Server(R) launch script for Docker

#

ACCEPT_EULA=${ACCEPT_EULA:-}

SA_PASSWORD=${SA_PASSWORD:-}

#COLLATION=${COLLATION:-SQL_Latin1_General_CP1_CI_AS}

have_sa_password=""

#have_collation=""

sqlservr_setup_prefix=""

configure=""

reconfigure=""

# Check system memory

#

let system_memory="$(awk '/MemTotal/ {print $2}' /proc/meminfo) / 1024"

if [ $system_memory -lt 3250 ]; then

    echo "ERROR: This machine must have at least 3.25 gigabytes of memory to install Microsoft(R) SQL Server(R)."

    exit 1

fi

# Create system directories

#

mkdir -p /var/opt/mssql/data

mkdir -p /var/opt/mssql/etc

mkdir -p /var/opt/mssql/log

# Check the EULA

#

if [ "$ACCEPT_EULA" != "Y" ] && [ "$ACCEPT_EULA" != "y" ]; then

 echo "ERROR: You must accept the End User License Agreement before this container" > /dev/stderr

 echo "can start. The End User License Agreement can be found at " > /dev/stderr

 echo "http://go.microsoft.com/fwlink/?LinkId=746388." > /dev/stderr

 echo ""

 echo "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement." > /dev/stderr

 exit 1

fi

# Configure SQL engine

#

if [ ! -f /var/opt/mssql/data/master.mdf ]; then

 configure=1

 if [ ! -z "$SA_PASSWORD" ] || [ -f /var/opt/mssql/etc/sa_password ]; then

 have_sa_password=1

 fi

# if [ ! -z "$COLLATION" ] || [ -f /var/opt/mssql/etc/collation ]; then

# have_collation=1

# fi

 if [ -z "$have_sa_password" ]; then

        echo "ERROR: The system administrator password is not configured. You can set the" > /dev/stderr

        echo "password via environment variable (SA_PASSWORD) or configuration file" > /dev/stderr

        echo "(/var/opt/mssql/etc/sa_password)." > /dev/stderr

 exit 1

 fi

fi

# If user wants to reconfigure, set reconfigure flag

#

if [ -f /var/opt/mssql/etc/reconfigure ]; then

 reconfigure=1

fi

# If we need to configure or reconfigure, run through configuration

# logic

#

if [ "$configure" == "1" ] || [ "$reconfigure" == "1" ]; then

 sqlservr_setup_options=""

# if [ -f /var/opt/mssql/etc/collation ]; then

# sqlservr_setup_options+="-q $(cat /var/opt/mssql/etc/collation)"

# else

# if [ ! -z "$COLLATION" ]; then

# sqlservr_setup_options+="-q $COLLATION "

# fi

# fi

 set +e

 cd /var/opt/mssql

 echo 'Configuring Microsoft(R) SQL Server(R)...'

 if [ -f /var/opt/mssql/etc/sa_password ]; then

 SQLSERVR_SA_PASSWORD_FILE=/var/opt/mssql/etc/sa_password /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 elif [ ! -z "$SA_PASSWORD" ]; then

 SQLSERVR_SA_PASSWORD_FILE=<(echo -n "$SA_PASSWORD") /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 else

 if [ ! -z '$sqlservr_setup_options' ]; then

 /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 fi

 fi

 retcode=$?

 if [ $retcode != 0 ]; then

 echo "Microsoft(R) SQL Server(R) setup failed with error code $retcode. Please check the setup log in /var/opt/mssql/log for more information." > /dev/stderr

 exit 1

 fi

 set -e

 rm -f /var/opt/mssql/etc/reconfigure

 rm -f /var/opt/mssql/etc/sa_password

 echo "Configuration complete."

fi

# Start SQL Server

#

exec /opt/mssql/bin/sqlservr $*

Quick Start EF Core Videos on Channel 9

Data Access, , , ,

My Pluralsight course, Entity Framework Core: Getting Started, is a pretty thorough exploration for learning about EF Core. But at 5 hours, it may not be the VERY first thing you want to look at. So I’ve created some 12 minute videos on Channel 9 that will, in that very short time, walk you through building some tiny apps that use EF Core so you can get your hands dirty. While you won’t learn much in the way of ins & outs, they will walk you through:

  • creating a new app from scratch,
  • adding in a domain class
  • add in EF Core
  • creating an EF Core data model
  • creating a database from the model
  • creating and saving some data
  • reading, updating and modifying some data.

That’s a lot for 12 minutes, but I have done it in a way that you can just watch and learn, or follow along to build the apps yourself . For some of the code that is not related to the EF Core lesson, you can even copy that code from my GitHub account and paste it into your solution.

There are currently 2 videos.

EF Core in a Full .NET Application Using Visual Studio 2015
This one is designed to assure you that you can use EF Core in a full .NET application and using totally familiar tools. You’ll build a little .NET console app that uses EF Core to read and write some movie data.

EFCoreQS1Lerman_512 2017-03-22_18-21-35

EF Core in an ASP.NET Core Application Using Visual Studio 2017
This video will give you the full leading edge .NET Core experience as you use the brand new Visual Studio 2017 to create a tiny ASP.NET Core MVC App that uses EF Core to interact with the data. Again, some of the code that is not about EF Core is available to copy/paste from my GitHub account, making it easy to follow along with the demo.EFCoreQS2Lerman_512

2017-03-22_18-19-44

I’ll be adding anther one showing  Creating an ASP.NET Core App with EF Core Using Visual Studio Code in OS X . You can keep an eye on my Channel 9 Niners page or this blog to see what that gets published.

When your ready to really learn about EF Core, head over to Pluralsight for my Entity Framework Core: Getting Started Course. If you don’t have a subscription, contact me for a 30-day free trial code.

2017-03-22_18-35-49

Changes to EF Core With the RTM of VS2017 and Tools

Data Access, ,

When Visual Studio 2017 released today a few other things happened that are relevant to Entity Framework Core.

For more on EF Core, watch my EF Core: Getting Started course on Pluralsight.

EF Core Migrations Tools Release

First – something we were prepared for – the .NET Core SDK was also released. The last stable version was 1.0.0-preview2-1-003137. It’s now simply 1.0.0. Along with this, its dependent tooling, including EF Core Tools for PowerShell and dotnet were also released. As the .NET Core support evolved from project.json to msbuild, the EF Core tools split . We have been using 1.0.0-preview4 (for .NET and project.json) and 1.0.0-msbuild3 for msbuild/csproj support.

Now the tool packages are 1.1.0 (Tools) and 1.0.0 (Tools.DotNet)

For PowerShell support: Microsoft.EntityFrameworkCore.Tools 1.1.0
For dotnet CLI support: Microsoft.EntityFrameworkCore.Tools.DotNet 1.0.0

In Visual Studio 2015 (for full .NET projects) and Visual Studio 2017 (shown here, for full .NET or .NET Core projects), the Package Manager will show the RTM versions:

image

Notice that I do not have “Include prerelease” checked.

If using PMC to install, it’s just

install-package Microsoft.EntityFrameworkCore.Tools

That’s for the PowerShell tools, otherwise, add .DotNet to the name.

But notice that you no longer need to add the –pre.

When using the CLI version of the tools, the command

dotnet ef –version

results in

Entity Framework Core .NET Command Line Tools 1.0.0-rtm-10308

Changes to Migrations Commands

As the tools evolved through the previews, some details changed for example, the scaffolding command got smarter.

But one change that is notable is with respect to EF Core in class libraries. You still need to point to an executable project (exe or test) to run most of the commands, but now you can at least just use “dotnet ef” to get the help file without having to set the –startup-project parameter. There are a few other commands that will run without knowledge of the startup project. You can read more about this in this GitHub thread. Check some of the later comments by Brice Lambson as he worked on evolving the commands.

EF Core 1.1.1 – Patch

This was a more subtle part of the release. Even though the 1.1.1 milestone on GitHub had 30 bug fixes that are all closed , there hadn’t been any mention that this was going to get pushed out and the milestone had no target date on it. Though I had my suspicions! Here’s a screenshot I happened to take on March 5.

image

And yes, the newest version of the EF Core packages is now 1.1.1. These are bug fixes …as the increment suggests.  Most of them are edge cases, but regardless, you should definitely update your EF Core packages to ensure you have these latest fixes. If you’re creating new projects, 1.1.1 is what you’ll see available from NuGet.

Note: there was a regression introduced in EF Core 1.1.1 that is targeted to get fixed with the next patch. You can read about this issue here: http://stackoverflow.com/questions/42708522/loading-related-data-aspnet-core-1-1

You can learn much more about EF Core in my EF Core: Getting Started course on Pluralsight.

Vermont .NET Birthday Cakes Throughout the Years

dotNET

As Visual Studio prepares for the VS2017 launch and the 20th anniversary of Visual Studio, I started reminiscing of Vermont.NET birthdays of the past. Our first meeting was in February 2002. Here’s a screenshot (thanks to the way back machine!) from October 2002 of our hand-made (by yours truly) ASP.NET 1.0 website:

image

More fun though is the various cakes we’ve had throughout the years when we’ve celebrated the passing time.

First is the cake made by user group member Laura Blood for our Feb 2003 meeting, our first anniversary.

image

According to my blog, we had cake at our Feb 2006 meeting with special guest (thank you INETA), Ken Getz. I don’t seem to have a picture though.

Another was from our 6th year. We had  a presentation on unit testing that night by Sarah Cameron who came down from Montreal.

vtdotnet6b

2010 was our 8th year. We didn’t have cake for our Feb meeting but we DID for the April meeting which was the launch of Visual Studio 2010.  Dave Burke designed this cake and it was implemented by the bakery at a local supermarket:

IMG_3373

For our 10th year, the supermarket bakery suggested balloons and I asked for green ones in honor of Vermont. Many jokes have been made about this cake. I did NOT see the problem until someone pointed it out at the meeting. Then we were all giggling like school boys.

Rob Hale was quick to tweet it so I was able to find this picture easily enough!

730514006

Here was my own creation for Vermont.NET’s 12th anniversary in 2014. Notice that the sprinkles are dinosaurs. Smile

Image result for cake vtdotnet

I wasn’t here for our February 2017 meetup, but our upcoming March meetup will celebrate the VS2017 launch so I plan to make or get a cake to celebrate for sure!

Entity Framework Core: Getting Started on Pluralsight

Data Access, ,

I’m happy to share a new course on Pluralsight with you – Entity Framework Core: Getting Started.

Here’s how I described it in the trailer:

Most software – whether for business or entertainment – is driven by data that users need to interact with. In Entity Framework Core: Getting Started, you will learn how to use Microsoft’s modern data access platform, Entity Framework Core. You will learn how to build data models, use EF Core to bridge your software with  your data store and how to incorporate all of this into desktop, mobile and web applications. When you’re finished with this course, you will have a foundational knowledge of Entity Framework Core that will help you as you move forward to build software in .NET, whether you are targeting Windows, OS X or Linux. Software required: Visual Studio 2015 or Visual Studio 2017.

pluralsight course

2017-02-15_17-54-44

 

Here is the list of modules in the course. You can see the titles of various clips in each module on Pluralsight.

To see the full list of my courses on Pluralsight, go to pluralsight.com/authors/julie-lerman

EF Core CLI Commands with VS2017 RC3

Data Access, , ,

Visual Studio 2017 RC3 was released yesterday but unfortunately an install issue has take it back off the shelf for a brief period. Watch this space for the return of RC3!

But I did manage to get it installed and wanted to show you that the EF Core CLI commands are now working. If you’ve been playing with VS2017 RC and EF Core you may have run into the problem that the EF Core tooling package was not in sync yet with the MSBuild tooling for .NET Core. That’s fixed now and not only does it work but there’s a change that I’m really happy to see.

As always, I have my dbcontext in its own project. Here are the csproj contents for that project:

image

 

Notice that the DotNetCliToolReference is pointing to Microsoft.EntityFrameworkCore.Tools.DotNet . The dotnet and PowerShell commands are exposed in separate packages. With “.DotNet” is the package that has the CLI commands. Without “.DotNet” is the package that contains the PowerShell commands.

More importantly, the package version went from “1.1.0-preview4” to “1.0.0-msbuild3-final”. I can’t explain why we went from 1.1.0 down to 1.0.0 but this is the newer and correct package.

With that in place,  I then open up a command prompt. I can use a regular one but I’m using a PowerShell command for a single benefit…that I can shorten the prompt. Here’s the command I did to trim most but not all of the path:

Quora: How do I get just the current folder name in my Windows Powershell prompt function?

Remember that I’m pointed to the path of a class library. DotNet EF requires you to point to a path containing an executable in order to run the commands. However with the latest bits, you can get HELP without having to point to the executable. Thank you Brice Lambson. It was a little meta to have to figure that out because rather than just getting help from the command, you had to googlebing for help on how to get help. So here are a simple dotnet ef command to get top level dotnet ef help, followed by dotnet ef dbcontext to get help on the dbcontext sub-commands.

image

To run commands that depend on the APIs, you still have to point to a startup-project if you are running the commands from a class library. Here I’ve run the command to list the migrations in my project. I’ve only got one, sqlite-init.

image

Some Insights into Features (Besides EDMX) Being Dropped in the Move From EF6 to EF Core

Data Access,

I had written these details for my Pluralsight EF Core course (hopefully published at end of Jan 2017) but decided not to spend the time on this explanation in the course. Instead, I’ll put it here and the course will have a link to this blog post! Clever, huh?

You’ve probably heard a lot about EF Core not bringing forward  the designer based EDMX from EF Core. This is a feature cut that I’ve heard the most feedback about. But there are other EF features that are also getting cut. These are not as worrisome to developers — based on my own experience and paying attention to response in social media — but it’s important to be aware of the biggest of these cut features.

ObjectContext API

The first is the ObjectContext API. This was the original mechanism for EF’s change tracking and database interaction. Since EF4.1 was released with the DbContext in early 2011, Microsoft has recommended that all new projects use DbContext. The DbContext sits on top of the ObjectContext and does the more cumbersome work of interacting with the ObjectContext on your behalf. But the ObjectContext has remained a public API for backwards compatibility with EF4 and EF3.5 projects. Also, we could access the ObjectContext to do low level tasks as needed.

There will not be an ObjectContext API in EF Core. Rather than relying on the ObjectContext for metadata work, change tracking and database interaction, this low-level activity is being  restructured and we’ll get at it directly from the DbContext. If you have old software that is still using the ObjectContext and you haven’t updated it by now, hopefully, you won’t want to update it to EFCore anyway. I wrote a 2 part article for MSDN magazine in 2014 that included guidance for moving ObjectContext code to DbContext if you think you may want to explore that.

Data Points : Tips for Updating and Refactoring Your Entity Framework Code Part 1

Data Points : Tips for Updating and Refactoring Your Entity Framework Code Part 2

Entity SQL

Entity SQL was the original string-based querying SQL like language written for EF. By the time EF was first released, it had already embraced the also-new LINQ. ESQL is only usable with the ObjectContext API. I think I  used to be one of the few people in the world who really knew how to use ESQL because I wrote about it extensively in my first EF book, giving it equal visibility as LINQ to Entities. In the 2nd edition, I had split the ESQL details out into their own chapter because by then it was clear that it was barely being used. I haven’t had any reason to use ESQL in many years. I’ve not heard of anyone using it either. So it is going to fade away along with the ObjectContext API and won’t be part of EFCore.

Edge-Case Mappings & the Original Metadata APIs

Entity Framework has allowed a lot of variations on mappings between your classes/properties and your database tables/fields. It has even let you combine many of these crazy mappings in one model. The EF team blog post highlights and example: “an inheritance hierarchy that combined TPH, TPT, and TPC mappings as well as Entity Splitting all in the same hierarchy.” This was possible because of the Metadata Workspace API. But building in this flexibility also meant that using that API was very complex. Internal query compilation was difficult to design. And for developers, discovering information about a model’s metadata has been very cumbersome.

So, EFCore has a simpler metadata model which means some of the truly edge case mappings won’t be achievable. This doesn’t mean things like inheritance will go away (although currently, EF Core only supports TPH), just the funky, rare mapping combinations.

MEST (Multiple Entities for a Single Type)

One single mapping technique that will go away is MEST. In all of my years of working with EF, I’ve never come across anyone who was taking advantage of it. It was only supported with EDMX and ObjectContext and the team decided not to bring it forward to the code-based model and DbContext for EFCore.

Automatic Migrations

Migrations are a critical technique for evolving a database schema from a code-based model. We’ve had two ways to use EF migrations – the default way which is to explicitly add migrations through the package manager console and then to apply those migrations using a variety of techniques. Another option has been to use automatic migrations that are worked out and executed on the fly at run time. Supporting automatic migrations caused a number of major headaches for migrations support overall. It forced migrations to store model snapshots directly in the database. This caused problems for developers using regular migrations – especially with source control. I’ve been in loops where I can’t add a migration because it thinks I need to execute one, but when I try to execute a migration it tells me I have to add one. I’m  not the only one who has gotten all tangled up in some circular problems when trying to manage migrations. These problems will go away because EFCore will not attempt to automate migrations at all. You can read more about this in Brice Lambson’s blog post about EFCore Migrations at design time. Brice is an engineer on the EF team and has a lot of other interesting blog posts worth checking out.

These are the most notable EF features that the team is not planning to implement at all in EFCore. Personally, I have not been using any of them ever or in a long time and I have guided my clients away from them as well. So if you are on that same path, you are well-positioned to use EF Core without having to worry about them.

EF Core, Postgres and the Camel-Cased Identity Tables

Data Access, ,

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 .