Using Entity Framework Core with DBFirst

March 04, 2017

Say what you like about ORM frameworks, but they do decrease time to market. My impression, as someone that has generally had very little exposure to them, is that, whilst they can make it very quick to get something up and running, they make it very easy to shoot yourself in the foot.

With all the hype about .Net Core, I thought I’d give EF Core a go, and this post is a document of my initial set-up which was, by no means, a straight forward process!

The General Idea of an ORM

The purpose of an ORM is to abstract data access, in a manner similar to the following:

ef1

The great advantage of this is that you can very quickly make database changes and maintain a layer of abstraction between the DB and the accessing layer. Obviously, the downside is that you don’t have the same level of control over this access.

Pre-requisites

The first step is to install the SDK from here. Like everything else in this post, this web-site is not as straight-forward as it appears. Make sure that you select “Current” and “SDK”:

ef2

I used x64. That matters when you get further down.

Database First

In this particular installation, I’m using the “Database First” model. What that means is that I already have a database, and it is sat on an accessible machine. The following project will create classes to access that. For details of how to create a database using a VS project, see this article that I wrote on unit testing databases.

Project set-up

The first step is to create your project.

ef3

What is the difference between ASP.NET Core (.NET Core) and ASP.NET Core (.NET Framework)?

In this instance, we’ll go with .Net Core. The difference between the two is that one of them (.NET Framework) references the .NET Framework, and so will not be cross platform. Obviously, picking .NET Core is your smallest footprint, and least functionality.

ef4

Pick Web API here, as we’re essentially just writing a service that accesses a DB (see the diagram above).

The next step is to install Entity Framework Core:

ef5

https://www.nuget.org/packages/Microsoft.EntityFrameworkCore/

A note on project.json

This is a new file that has been introduced into the .Net Core world, and it is (IMHO) a huge improvement on the flakey confusion of NuGet package management. The idea is that you declare your dependencies, in a similar way that you might declare your variables in a program. When you change this, or when you ask it to, VS will simply go and get these packages for you. This means, for example, that I can paste my project.json in this post, and you, the reader (or future me), can simply paste this directly into yours and VS will do the rest… so:



{
  "dependencies": {
    "Microsoft.ApplicationInsights.AspNetCore": "2.0.0",
    "Microsoft.AspNetCore.Hosting.Abstractions": "1.1.0",
    "Microsoft.AspNetCore.Mvc": "1.1.1",
    "Microsoft.AspNetCore.Routing": "1.1.0",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.1.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.1.0",
    "Microsoft.EntityFrameworkCore": "1.1.0",
    "Microsoft.EntityFrameworkCore.Design": "1.1.0",
    "Microsoft.EntityFrameworkCore.Relational.Design": "1.1.0",
    "Microsoft.EntityFrameworkCore.SqlServer": "1.1.0",
    "Microsoft.EntityFrameworkCore.Tools.DotNet": "1.1.0-preview4-final",
    "Microsoft.Extensions.Configuration.Json": "1.1.0",
    "Microsoft.Extensions.Logging": "1.1.0",
    "Microsoft.Extensions.Logging.Console": "1.1.0",
    "Microsoft.Extensions.Logging.Debug": "1.1.0",
    "Microsoft.Extensions.Options.ConfigurationExtensions": "1.1.0",
    "Microsoft.NETCore.App": {
      "version": "1.1.0",
      "type": "platform"
    },
    "NETStandard.Library": "1.6.1",
    "System.Collections.NonGeneric": "4.3.0",
 
    "Microsoft.EntityFrameworkCore.SqlServer.Design": {
      "version": "1.1.0",
      "type": "build"
    },
    "Microsoft.EntityFrameworkCore.Tools": {
      "version": "1.1.0-preview4-final",
      "type": "build"
    }
  },
 
  "tools": {
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final",
    "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final"
  },
 
  "frameworks": {
    "netcoreapp1.1": {
      "imports": [
        "dotnet5.6",
        "portable-net45+win8"
      ]
    }
  },
 
  "buildOptions": {
    "emitEntryPoint": true,
    "preserveCompilationContext": true
  },
 
  "runtimeOptions": {
    "configProperties": {
      "System.GC.Server": true
    }
  },
 
  "runtimes": {
    "win10-x64": {}
  },
 
  "publishOptions": {
    "include": [
      "wwwroot",
      "\*\*/\*.cshtml",
      "appsettings.json",
      "web.config"
    ]
  },
 
  "scripts": {
    "postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
  }
}

If you now simply open a package manager window in VS and type:

dotnet restore

VS should do the rest.

The next thing that you’ll need to update is the appsettings.json:



{
 
  "ConnectionStrings": {
    "JourneyDB": "Data Source=ServerName\\\\DatabaseInstance; Initial Catalog=MyDatabase; Integrated Security=SSPI"
  },
  
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  }
}

Scaffolding

So, you should now have a project that’s ready to go. I suggestion, unless you’re reading this in around a year from now (2018) when all this has been stabilised, is that you restart VS. In fact, this should be your first response if anything in this post doesn’t do what you expect (it is still in preview, so I’m not judging).

The next step is to call the following command:

Scaffold-DbContext "Server=ServerName\\DatabaseInstance;Database=MyDatabase;Trusted\_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

What should that do (because it doesn’t)?

Okay, it took me a good while to get this to actually work, so the chance of it working first time is pretty remote; but when it does work, you should get a mirror of your DB in the project:

ef6

Using it

As you can see, I have a `JourneyHeader`; here’s the code to get the contents of that table:



namespace JourneyService.Controllers
{
    [Route("api/[controller]")]
    public class JourneyController : Controller
    {
        // GET api/values
        [HttpGet]
        public IEnumerable<JourneyHeader> Get()
        {
            using (JourneyDatabaseContext context = new JourneyDatabaseContext())
            {
                return context.JourneyHeader.ToList();
            }            
        }

And here’s the proof that it works:

ef7

References

https://code.msdn.microsoft.com/How-to-using-Entity-1464feea

http://www.dotnetspeak.com/asp-net-core/write-your-first-api-with-asp-net-core-and-entity-framework-core/

https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db

http://michaelcrump.net/getting-started-with-aspnetcore/

https://github.com/dotnet/core/blob/master/release-notes/rc4-download.md

https://blogs.msdn.microsoft.com/dotnet/2016/11/16/announcing-entity-framework-core-1-1/

http://stackoverflow.com/questions/42393977/setting-up-database-first-ef-project-using-scaffold-dbcontext

http://stackoverflow.com/questions/42393977/setting-up-database-first-ef-project-using-scaffold-dbcontext?noredirect=1#comment71956574_42393977



Profile picture

A blog about one man's journey through code… and some pictures of the Peak District
Twitter

© Paul Michaels 2024