Implementations

SQL Server with .NET Core and EntityFramework on Mac

By on 14/03/2018

So I wanted to create the backend for a platform, and I wanted…

  • a cross platform solution, to be able to host in anywhere
  • (Microsoft) SQL Server
  • Entity Framework as ORM and for Migrations
  • to work exclusively from my Mac

To make this possible, I’m using ASP .NET Core with Entity Framework Core. For the database, I use a Docker image with Sql Server for Linux that can run on Mac.

So this is how it goes…

Docker Container with SQL Server

To setup a SQL Server with Docker, you just have to follow a few simple steps:

Install Docker

Download and install Docker for Mac.

Download the image

sudo docker pull microsoft/mssql-server-linux:2017-latest

Start a container

docker run -e ‘ACCEPT_EULA=Y’ -e ‘MSSQL_SA_PASSWORD=1StrongPassword!’ -p 1401:1433 –name sql1 -d microsoft/mssql-server-linux:2017-latest

Check the container is running

sudo docker ps -a

Test the connection

I recommend SQL Operations Studio:

For a deeper understandment of Docker for this case, I recommend you this page.

The executable .Net Core

For EntityFramework Core to work, you need an executable project in your solution. So for this demo, we just go with a simple .NET Core Console application. In VisualStudio on Mac, just go to

File – New Solution – .NET Core – App – Console Application

Then you add a simple class containing two properties Id and Name.

That’s it for the example project. Now we’ll look how we create a database with a ‘Person’ table with EntityFramework Core.

EntityFramework Core

Packages

For EF Core to work, you need to add just two packages. They contain all the dependencies for the other packages, and will pull them in.

  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Design

Actually this is the simple way of going, when you’re keeping everything inside one project. If you’re splitting your code in a data library (containing the DbContext), you ‘just’ need the Designer package in the executable, and the other package in the Data project. But let’s not worry about that right now.

DbContext

This is the main entry point for EntityFramework Core in your project. Create a class PersonContext that inherits from DbContext, and override the method OnConfiguring to configure the connection string to the database. Also, you’ll need to define the DbSets for each class you want to store in the database as a table.

Of course there’s better ways to configure this (through the application config file), but I want to keep it as simple as possible here.

EntityFramework Core CLI

Now comes the tricky part – using EntityFramework Core to generate migrations and create/update the database automatically. For this, we have the Package Manager commands in VisualStudio on Windows. But what about Mac?

You’ll have to add another NuGet package, but it’s not possible to install it through VisualStudio. Instead, you’ll have to add the reference manually in the csproj file:

<ItemGroup>
    <DotNetCliToolReferenceInclude=”Microsoft.EntityFrameworkCore.Tools.DotNet”Version=”1.0.0″/>
</ItemGroup>
Then just open a command line, go to the project folder, and run
dotnet restore.
If everything is fine, you should be able to run
dotnet ef
now – the entity framework command line interface. You should see the unicorn:

Now you can create your first migration using the command:

dotnet ef migrations add initial

(You can choose another name over ‘initial’ of course)

You can see your first migration in the project, after you refresh:

Now there is just one last step left – to execute the migration:

dotnet ef database update

And voilá! The new database and table appear in the database!

Now you can start using all the EntityFramework Core features in your code. If you want to learn more about that, I recommend you this Pluralsight course.

So long…

HAPPY PLANTING

TAG
  • Carlos Guerrero

    Hi! when i run the command docker run -e ‘ACCEPT_EULA=Y’ -e ‘MSSQL_SA_PASSWORD=1StrongPassword!’ -p 1401:1433 –name sql1 -d microsoft/mssql-server-linux:2017-latest docker does error: docker: invalid reference format.

  • Hi Carlos!

    Please validate the name of the container first by running ‘docker images’. Perhaps you can go with :latest instead – or perhaps you even have a 2018 version now (I didn’t update mine in a while). Make sure the container and version name are exactly like in the printout of the ‘images’ command (I worked with Mac, perhaps you’re on Windows, and it’s different??).

    Then you could also try to drop the ‘-name sql1’ part. Anyway this name is just a shortcut for stopping/starting the instance mainly. When you list your instances with ‘docker ps -a’ you can find the ContainerID and use it instead of the name after creating the instance.

    Also at some point, I had problems with quotes, which resulted in a very similiar ‘reference format’ error. So I re-wrote the commands (instead of copying them), trying to use ‘ or ” or ` instead …. and somewhen it worked. Perhaps this discussion can also help you.
    https://stackoverflow.com/questions/45682010/docker-invalid-reference-format

    I hope that helps! I would like to hearing again from you!