Entity Framework Core Database-First Tutorial for Full .NET Framework

Entity Framework Core supports Database-First approach via the Scaffold-DbContext command of Package Manager Console. This command scaffolds a DbContext and entity type classes for a specified database.

This tutorial shows how to create a simple console application, powered by Entity Framework Core and using Database-First approach. In less than 10 minutes you will have a ready-to-use data access layer for your business objects.

This article consists of the following sections:

Requirements

If you want to target Entity Framework Core 3.1, this tutorial requires the following:

  • Visual Studio 2019 (16.3) or higher
  • .NET Core SDK 2.2
  • Latest version of NuGet Package Manager
  • Latest version of Windows PowerShell

If you want to target Entity Framework Core 2.2, this tutorial requires the following:

  • Visual Studio 2017 or higher
  • .NET Core SDK 2.2
  • Latest version of NuGet Package Manager
  • Latest version of Windows PowerShell

If you want to target Entity Framework Core 1.1, this tutorial requires the following:

  • Visual Studio 2015 Update 3 or higher
  • Latest version of NuGet Package Manager
  • Latest version of Windows PowerShell

We will use the tables, we have created in the Creating Database Objects and Inserting Data Into Tables tutorials. In this tutorial it is assumed that you already have the database objects created. If you haven't created them yet, please do it before starting this tutorial.

dotConnect for MySQL

This article uses classes from dotConnect for MySQL, a high-performance ADO.NET provider with the ORM support (Entity Framework, NHibernate, and LinqConnect).

Try code examples from this article yourself!

*Trial and free versions are available

Creating New Project

  1. Open Visual Studio
  2. On the File menu point to New and then click Project. The New Project dialog box will open.
  3. On the left side of the dialog box select Templates -> Visual C# -> Windows.
  4. On the left side of the dialog box select Console Application and click Create.
  5. For Entity Framework Core 2 or 3.1, ensure you are targeting .NET Framework 4.6.1 or later. For Entity Framework Core 1.1, ensure you are targeting .NET Framework 4.5.1 or later.
  6. Enter the project name and location if necessary.
  7. Click OK. A new project will be created.

Installing NuGet Packages

After we created a new project, we need to add the necessary NuGet packages to it. Let's install the necessary packages:

  1. On the Tools menu point to NuGet Package Manager and then click Package Manager Console.
  2. For Entity Framework Core 3.1, run the following command in the Package Manager Console:
    Install-Package Microsoft.EntityFrameworkCore.Tools -Version 2.2.6

    For Entity Framework Core 2.2, run the following command in the Package Manager Console:
    Install-Package Microsoft.EntityFrameworkCore.Tools -Version 2.2.6

    For Entity Framework Core 1.1, the command will be the following:
    Install-Package Microsoft.EntityFrameworkCore.Tools -Version 1.1.5

Adding References to dotConnect for MySQL Assemblies

Additionally you need to add references to the following assemblies to your project:

  • Devart.Data.dll
  • Devart.Data.MySql.dll
  • Devart.Data.MySql.Entity.EFCore.dll
  • For Entity Framework Core 1.1 - Devart.Data.MySql.Entity.EFCore.Design.dll

Please note that there are three versions of Devart.Data.MySql.Entity.EFCore.dll assemblies for different Entity Framework Core versions - 1.1, 2.2, and 3.1. They are located respectively in \Entity\EFCore, \Entity\EFCore2, and \Entity\EFCore3 subfolders of the dotConnect for MySQL installation folder.

Creating a Model From the Database

For Entity Framework Core, creating a model from the database is as easy as entering the Scaffold-DbContext command with a connection string and a provider as parameters. For example, you can run the following command in the Package Manager Console:

Scaffold-DbContext "User Id=root;Host=localhost;Database=Test;" Devart.Data.MySql.Entity.EFCore

If you have other tables in your database, you may use additional parameters - -Schemas and -Tables - to filter the list of schemas and/or tables that are added to the model. For example, you can use the following command:

Scaffold-DbContext "User Id=root;Host=localhost;Database=Test;" Devart.Data.MySql.Entity.EFCore -Tables dept,emp

As the result, a context class and entity classes are generated, based on the Dept and Emp tables.

[C#]
using System;
using System.Collections.Generic;

namespace ConsoleApplication1
{
   public partial class Dept
   {
       public Dept()
       {
           Emp = new HashSet<Emp>();
       }

       public int Deptno { get; set; }
       public string Dname { get; set; }
       public string Loc { get; set; }

       public virtual ICollection<Emp> Emp { get; set; }
   }
}
[C#]
using System;
using System.Collections.Generic;

namespace ConsoleApplication1
{
   public partial class Emp
   {
       public int Empno { get; set; }
       public string Ename { get; set; }
       public string Job { get; set; }
       public int Mgr { get; set; }
       public DateTime Hiredate { get; set; }
       public double Sal { get; set; }
       public double Comm { get; set; }
       public int Deptno { get; set; }

       public virtual Dept DeptnoNavigation { get; set; }
   }
}

Entity classes are just simple classes, representing the data, stored in the database. The context represents a session with the database and allows you to query and save instances of the entity classes.

[C#]
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace ConsoleApplication1
{
    public partial class ModelContext : DbContext
    {
        public virtual DbSet<Dept> Dept { get; set; }
        public virtual DbSet<Emp> Emp { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            #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.UseMySql(@"User Id=root;Host=localhost;Database=Test;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Dept>(entity =>
            {
                entity.HasKey(e => e.Deptno)
                    .HasName("PK_dept");

                entity.ToTable("DEPT", "DEMOBASE");

                entity.Property(e => e.Deptno).HasColumnName("DEPTNO");

                entity.Property(e => e.Dname)
                    .HasColumnName("DNAME")
                    .HasColumnType("varchar")
                    .HasMaxLength(14);

                entity.Property(e => e.Loc)
                    .HasColumnName("LOC")
                    .HasColumnType("varchar")
                    .HasMaxLength(13);
            });

            modelBuilder.Entity<Emp>(entity =>
            {
                entity.HasKey(e => e.Empno)
                    .HasName("PK_emp");

                entity.ToTable("EMP", "DEMOBASE");

                entity.Property(e => e.Empno).HasColumnName("EMPNO");

               entity.Property(e => e.Comm)
                   .HasColumnName("COMM");

                entity.Property(e => e.Deptno).HasColumnName("DEPTNO");

                entity.Property(e => e.Ename)
                    .HasColumnName("ENAME")
                    .HasColumnType("varchar")
                    .HasMaxLength(10);

                entity.Property(e => e.Hiredate)
                    .HasColumnName("HIREDATE")
                    ;

                entity.Property(e => e.Job)
                    .HasColumnName("JOB")
                    .HasColumnType("varchar")
                    .HasMaxLength(9);

                entity.Property(e => e.Mgr).HasColumnName("MGR");

               entity.Property(e => e.Sal)
                   .HasColumnName("SAL");

                entity.HasOne(d => d.DeptnoNavigation)
                    .WithMany(p => p.Emp)
                    .HasForeignKey(d => d.Deptno)
                    .HasConstraintName("emp_fk");
            });
        }
    }
}

Here we may comment or delete the warning about the connection string. After this we can start using our model to retrieve and modify data.

Using Model

Open your Program.cs file and paste the following code instead of the Main function:

[C#]
        static void Main(string[] args)
        {
            using (var db = new ModelContext())
            {
                // Creating a new department and saving it to the database
                var newDept = new Dept();
                newDept.Deptno = 60;
                newDept.Dname = "Development";
                newDept.Loc = "Houston";
                db.Dept.Add(newDept);
                var count=db.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);

                // Retrieving and displaying data
                Console.WriteLine();
                Console.WriteLine("All departments in the database:");
                foreach (var dept in db.Dept)
                {
                    Console.WriteLine("{0} | {1}", dept.Dname, dept.Loc);
                }
            }
        }

Conclusion

This article shows how to create a console application, working with a MySQL database via Entity Framework Core, using dotConnect for MySQL as an Entity Framework Core provider. dotConnect for MySQL is an ADO.NET provider from Devart with support for such ORM solutions as Entity Framework v1 - v6, Entity Framework Core, NHibernate, and Devart's own ORM LinqConnect.

This tutorial uses the standard Entity Framework Core Scaffold-DbContext command to generate entity classes from a MySQL database. This is a quick and easy way to generate entity classes, but it doesn't provide much options for customization. dotConnect for MySQL includes Entity Developer - a powerful visual ORM model designer with support for Database First, Model First, and mixed development approaches and powerful code generation.

More dotConnect for MySQL tutorials

Back to list