Getting Started with EF Core on ASP.NET Core with an Existing Database

In this tutorial, you build an ASP.NET Core MVC application that performs basic data access using Entity Framework Core. You reverse engineer an existing database to create an Entity Framework model.

View this article's sample on GitHub.

Prerequisites

Install the following software:

Create Blogging database

This tutorial uses a Blogging database on your LocalDb instance as the existing database. If you have already created the Blogging database as part of another tutorial, skip these steps.

  • Open Visual Studio
  • Tools -> Connect to Database...
  • Select Microsoft SQL Server and click Continue
  • Enter (localdb)\mssqllocaldb as the Server Name
  • Enter master as the Database Name and click OK
  • The master database is now displayed under Data Connections in Server Explorer
  • Right-click on the database in Server Explorer and select New Query
  • Copy the script listed below into the query editor
  • Right-click on the query editor and select Execute
CREATE DATABASE [Blogging];
GO

USE [Blogging];
GO

CREATE TABLE [Blog] (
    [BlogId] int NOT NULL IDENTITY,
    [Url] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Blog] PRIMARY KEY ([BlogId])
);
GO

CREATE TABLE [Post] (
    [PostId] int NOT NULL IDENTITY,
    [BlogId] int NOT NULL,
    [Content] nvarchar(max),
    [Title] nvarchar(max),
    CONSTRAINT [PK_Post] PRIMARY KEY ([PostId]),
    CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([BlogId]) ON DELETE CASCADE
);
GO

INSERT INTO [Blog] (Url) VALUES
('http://blogs.msdn.com/dotnet'),
('http://blogs.msdn.com/webdev'),
('http://blogs.msdn.com/visualstudio')
GO

Create a new project

  • Open Visual Studio 2017
  • File > New > Project...
  • From the left menu select Installed > Visual C# > Web
  • Select the ASP.NET Core Web Application project template
  • Enter EFGetStarted.AspNetCore.ExistingDb as the name and click OK
  • Wait for the New ASP.NET Core Web Application dialog to appear
  • Make sure that the target framework dropdown is set to .NET Core, and the version dropdown is set to ASP.NET Core 2.1
  • Select the Web Application (Model-View-Controller) template
  • Ensure that Authentication is set to No Authentication
  • Click OK

Install Entity Framework Core

To install EF Core, you install the package for the EF Core database provider(s) you want to target. For a list of available providers see Database Providers.

For this tutorial, you don't have to install a provider package because the tutorial uses SQL Server. The SQL Server provider package is included in the Microsoft.AspnetCore.App metapackage.

Reverse engineer your model

Now it's time to create the EF model based on your existing database.

  • Tools –> NuGet Package Manager –> Package Manager Console
  • Run the following command to create a model from the existing database:
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

If you receive an error stating The term 'Scaffold-DbContext' is not recognized as the name of a cmdlet, then close and reopen Visual Studio.

Tip

You can specify which tables you want to generate entities for by adding the -Tables argument to the command above. For example, -Tables Blog,Post.

The reverse engineer process created entity classes (Blog.cs & Post.cs) and a derived context (BloggingContext.cs) based on the schema of the existing database.

The entity classes are simple C# objects that represent the data you will be querying and saving. Here are the Blog and Post entity classes:

using System;
using System.Collections.Generic;

namespace EFGetStarted.AspNetCore.ExistingDb.Models
{
    public partial class Blog
    {
        public Blog()
        {
            Post = new HashSet<Post>();
        }

        public int BlogId { get; set; }
        public string Url { get; set; }

        public ICollection<Post> Post { get; set; }
    }
}
using System;
using System.Collections.Generic;

namespace EFGetStarted.AspNetCore.ExistingDb.Models
{
    public partial class Post
    {
        public int PostId { get; set; }
        public int BlogId { get; set; }
        public string Content { get; set; }
        public string Title { get; set; }

        public Blog Blog { get; set; }
    }
}

Tip

To enable lazy loading, you can make navigation properties virtual (Blog.Post and Post.Blog).

The context represents a session with the database and allows you to query and save instances of the entity classes.

public partial class BloggingContext : DbContext
{
   public BloggingContext()
   {
   }

   public BloggingContext(DbContextOptions<BloggingContext> options)
       : base(options)
   {
   }

   public virtual DbSet<Blog> Blog { get; set; }
   public virtual DbSet<Post> Post { get; set; }

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
       if (!optionsBuilder.IsConfigured)
       {
           #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
           optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;");
       }
   }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder.Entity<Blog>(entity =>
       {
           entity.Property(e => e.Url).IsRequired();
       });

       modelBuilder.Entity<Post>(entity =>
       {
           entity.HasOne(d => d.Blog)
               .WithMany(p => p.Post)
               .HasForeignKey(d => d.BlogId);
       });
   }
}

Register your context with dependency injection

The concept of dependency injection is central to ASP.NET Core. Services (such as BloggingContext) are registered with dependency injection during application startup. Components that require these services (such as your MVC controllers) are then provided these services via constructor parameters or properties. For more information on dependency injection see the Dependency Injection article on the ASP.NET site.

Register and configure your context in Startup.cs

To make BloggingContext available to MVC controllers, register it as a service.

  • Open Startup.cs
  • Add the following using statements at the start of the file
using EFGetStarted.AspNetCore.ExistingDb.Models;
using Microsoft.EntityFrameworkCore;

Now you can use the AddDbContext(...) method to register it as a service.

  • Locate the ConfigureServices(...) method
  • Add the following highlighted code to register the context as a service
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.Configure<CookiePolicyOptions>(options =>
    {
        // This lambda determines whether user consent for non-essential cookies is needed for a given request.
        options.CheckConsentNeeded = context => true;
        options.MinimumSameSitePolicy = SameSiteMode.None;
    });


    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

    var connection = @"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;ConnectRetryCount=0";
    services.AddDbContext<BloggingContext>(options => options.UseSqlServer(connection));
}

Tip

In a real application you would typically put the connection string in a configuration file or environment variable. For the sake of simplicity, this tutorial has you define it in code. For more information, see Connection Strings.

Create a controller and views

  • Right-click on the Controllers folder in Solution Explorer and select Add -> Controller...
  • Select MVC Controller with views, using Entity Framework and click Ok
  • Set Model class to Blog and Data context class to BloggingContext
  • Click Add

Run the application

You can now run the application to see it in action.

  • Debug -> Start Without Debugging
  • The application builds and opens in a web browser
  • Navigate to /Blogs
  • Click Create New
  • Enter a Url for the new blog and click Create

image

image