Create Entity Data Model using a Database First Approach

By:   |   Comments (2)   |   Related: > Entity Framework


Problem

Entity Framework is a popular ORM from Microsoft – it’s been in use for quite some time now. Entity Framework Core (EF Core) is the flavor of Entity Framework that can run on .NET Core. It is an open source, lightweight, cross platform ORM that can be used to model your entities much the same way you do with Entity Framework. This article presents a discussion on how we can create an Entity Data Model using the Database First approach in Entity Framework Core.

Solution

In this section we’ll examine how we can create an Entity Data Model using the Database First approach in EF Core. However, before we crate the Entity Data Model, let’s have a quick tour of the concepts.

The Entity Data Model

Like other ORMs, you can take advantage of Entity Framework Core to perform CRUD operations without the need of your application interacting with the underlying database directly. The Entity Data Model (also known as EDM) is an extended ER model that is used to present the conceptual model of the data. It is a set of concepts that describe the structure of the underlying data regardless of how the data is stored in the database. The EDM is also used to define the entities and their relationships.

Code First vs Model First vs Database First approaches

Now, to model your entities, there are three approaches in EF Core. These include: Code First, Model First and Database First. When following the Code First approach you would typically create the entity classes initially. The EF Core runtime will then create a database and the necessary tables from these entity classes. The advantage of Code First approach is that you have a clean and simple code and you have complete control of your code. In the Model First approach, you can design the model and then let the workflow generate the database script from the model and T4 templates generate your POCO entities. In the Database First approach, unlike the other two approaches, you should have the database available. The Model and the POCO entities can then be generated from the database. Basically, the Database First and Model First approaches are the same. The generated code from each of these approaches is the same. Hence, you can combine these two approaches as well. For example, you can create a database using the designer, alter the database using a script and then update the model. In this example, we’ll use the Database First approach.

To create an Entity Data Model from the database using the Database First approach in EF Core, we’ll follow these steps:

  • Create a new console application project
  • Create a Model using the Scaffold-DbContext Command

Creating a new console application project

In this example, we’ll use Visual Studio 2019. If you don’t have a copy of Visual Studio 2019 installed in your system, you can download a copy from here: https://visualstudio.microsoft.com/downloads/

If Visual Studio 2019 has been successfully installed in your system, follow the steps outlined below to create a new console application project in Visual Studio.

  • Open Visual Studio 2019
  • On the File menu, click on New > Project
  • In the "Create a new project" dialog, select "Console App (.NET Core)"
  • Click Next
  • Now specify the name and location of the project
  • Click Create as shown in Figure 1
restore database

This would create a new console application project in Visual Studio 2019. We'll use this project in the subsequent sections.

Creating a new database

To work with the Database First approach, we’ll need a database to be available. For the sake of simplicity and avoid creating a new database, we'll use AdventureWorks database here. If you don't have it in your system, you can download a copy of the AdventureWorks database from here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

If you have downloaded a backup copy of the AdventureWorks database, follow these steps to restore the AdventureWorks database in your system.

  • Open Microsoft SQL Server Management Studio
  • Select Databases > Restore database...
  • Select the database to be restored
  • Specify the path and the name of the destination database
  • Click OK

This would restore the AdventureWorks database in your system and you are now all set to use it.

restore database

Installing the necessary packages

Now that the database has been restored, specify the following commands at the NuGet Package Manager console to install the necessary packages.

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer			

Create a Model from the database

So far so good. To create the Entity Data Model, we'll take advantage of the Scaffold-DbContext command. Here's how the syntax of this command looks like:

Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>] 
                   [-DataAnnotations] [-Force] [-Project] [-StartupProject] [<CommonParameters>]			

To create the Entity Data Model for our AdventureWorks database, specify the following command at the Package Manager Console.

Scaffold-DbContext "Server=JOYDIP;Database=AdventureWorks2017;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
			

While the Server attribute is used to specify the name of the database server, the Database attribute is used to specify the database name. In our example, the name of the database is AdventureWorks2017. Once the command has run successfully, you’ll see that the entity classes and the DbContext class has been generated.

solution explorer

Programming the Entity Data Model

Now that the Model has been created, let’s write a program to test if we can fetch data. To do this, we’ll need to create an instance of the DbContext. While creating the EDM using the Scaffold-DbContext command, a DbContext will be created by default. In our case, a DbContext in the name of AdventureWorks2017Context is created. A DbContext is a class that represents a session of communication with the database. Here's how the AdventureWorks2017Context class looks like. Note that this class extends the DbContext class of the Microsoft.EntityFrameworkCore namespace.

public partial class AdventureWorks2017Context : DbContext
   {
        public AdventureWorks2017Context()
        {
        }
        public AdventureWorks2017Context(DbContextOptions<AdventureWorks2017Context> options)
            : base(options)
        {
        }
        public virtual DbSet<Department> Department { get; set; }
     //Other members (Omitted for clarity)
   }			

Here's how you can instantiate the AdventureWorks2017Context class.

AdventureWorks2017Context dbContext = new AdventureWorks2017Context();			

The following code snippet illustrates how you can use this DbContext instance to retrieve the names of the Department table in the AdventureWorks database.

 var data = dbContext.Department.ToList();
 foreach(var d in data)
  {
     Console.WriteLine(d.Name);
  }			

The complete code listing is given below for your reference.

namespace DBFirstDemoApp
{
    class Program
    {
        private static readonly AdventureWorks2017Context dbContext = new AdventureWorks2017Context();
        static void Main(string[] args)
        {
            var data = dbContext.Department.ToList();
            foreach(var d in data)
            {
                Console.WriteLine(d.Name);
            }
            Console.Read();
        }
    }
}			

When you execute the above program, here’s how the output would look.

dotnet

The Entity Data Model (EDM), an extended Entity-Relationship model, is used to describe a data structure regardless of the way the data is stored in the database. It uses entity type, association type and property to describe the data structure.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joydip Kanjilal Joydip Kanjilal - Microsoft MVP (2007 to 2012), Author and Speaker with more than 20 years of experience. Authored more than 500 articles, 8 books and reviewed a dozen books.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, March 22, 2022 - 11:15:38 AM - Rich Ward Back To Top (89915)
For anyone preferring to use the dotnet cli instead, and only wanting to scaffold a subset of tables you can use this:

dotnet ef dbcontext scaffold "server=<server name>;user=<user name>;password=>password>;database=<database>" Microsoft.EntityFrameworkCore.SqlServer -o Models -t <table name 1> -t <table name 2> -t <table name 3> -t <table name 4> -t <table name x> -f

Thursday, January 2, 2020 - 12:25:11 PM - mkamoski Back To Top (83601)

Thanks for the decent article, but please note that it is not possible to "install-package" if the machine does not have internet access.















get free sql tips
agree to terms