Getting started with EF Core on .NET Framework with an Existing Database

In this tutorial, you build a console application that performs basic data access against a Microsoft SQL Server database using Entity Framework. You create an Entity Framework model by reverse engineering an existing database.

View this article's sample on GitHub.

Prerequisites

Create Blogging database

This tutorial uses a Blogging database on the 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# > Windows Desktop

  • Select the Console App (.NET Framework) project template

  • Make sure that the project targets .NET Framework 4.6.1 or later

  • Name the project ConsoleApp.ExistingDb and click OK

Install Entity Framework

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

  • Tools > NuGet Package Manager > Package Manager Console

  • Run Install-Package Microsoft.EntityFrameworkCore.SqlServer

In the next step, you use some Entity Framework Tools to reverse engineer the database. So install the tools package as well.

  • Run Install-Package Microsoft.EntityFrameworkCore.Tools

Reverse engineer the model

Now it's time to create the EF model based on an 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
    

Tip

You can specify the tables 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 and Post) and a derived context (BloggingContext) 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 ConsoleApp.ExistingDb
{
    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 ConsoleApp.ExistingDb
{
    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. It has methods that you can use to query and save instances of the entity classes.

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace ConsoleApp.ExistingDb
{
    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);
            });
        }
    }
}

Use the model

You can now use the model to perform data access.

  • Open Program.cs

  • Replace the contents of the file with the following code

    using System;
    
    namespace ConsoleApp.ExistingDb
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new BloggingContext())
                {
                    db.Blog.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                    var count = db.SaveChanges();
                    Console.WriteLine("{0} records saved to database", count);
    
                    Console.WriteLine();
                    Console.WriteLine("All blogs in database:");
                    foreach (var blog in db.Blog)
                    {
                        Console.WriteLine(" - {0}", blog.Url);
                    }
                }
            }
        }
    }
    
  • Debug > Start Without Debugging

    You see that one blog is saved to the database and then the details of all blogs are printed to the console.

    image

Additional Resources