Access application data with Entity Framework in .NET Core [Tutorial]

In this tutorial, we will get started with using the Entity Framework and create a simple console application to perform CRUD operations. The intent is to get started with EF Core and understand how to use it. Before we dive into coding, let us see the two development approaches that EF Core supports:

• Code-first
• Database-first

These two paradigms have been supported for a very long time and therefore we will just look at them at a very high level. EF Core mainly targets the code-first approach and has limited support for the database-first approach, as there is no support for the visual designer or wizard for the database model out of the box. However, there are third-party tools and extensions that support this. The list of third-party tools and extensions can be seen at https://docs.microsoft.com/en-us/ef/core/extensions/.

This tutorial has been extracted from the book .NET Core 2.0 By Example, by Rishabh Verma and Neha Shrivastava.

In the code-first approach, we first write the code; that is, we first create the domain model classes and then, using these classes, EF Core APIs create the database and tables, using migration based on the convention and configuration provided. We will look at conventions and configurations a little later in this section. The following diagram illustrates the code-first approach:

In the database-first approach, as the name suggests, we have an existing database or we create a database first and then use EF Core APIs to create the domain and context classes. As mentioned, currently EF Core has limited support for it due to a lack of tooling. So, our preference will be for the code-first approach throughout our examples. The reader can discover the third-party tools mentioned previously to learn more about the EF Core database-first approach as well. The following image illustrates the database-first approach:

Building Entity Framework Core Console App

Now that we understand the approaches and know that we will be using the code-first approach, let's dive into coding our getting started with EF Core console app. Before we do so, we need to have SQL Express installed in our development machine. If SQL Express is not installed, download the SQL Express 2017 edition from https://www.microsoft.com/en-IN/sql-server/sql-server-downloads and run the setup wizard. We will do the Basic installation of SQL Express 2017 for our learning purposes, as shown in the following screenshot:

Our objective is to learn how to use EF Core and so we will not do anything fancy in our console app. We will just do simple Create Read Update Delete (CRUD) operations of a simple class called Person, as defined here:

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Gender { get; set; }
        public DateTime DateOfBirth { get; set; }
        public int Age
        {
            get
            {
                var age = DateTime.Now.Year - this.DateOfBirth.Year;

if (DateTime.Now.DayOfYear < 
this.DateOfBirth.DayOfYear)
{
age = age - 1;
}

return age;
}
} 
}

As we can see in the preceding code, the class has simple properties. To perform the CRUD operations on this class, let's create a console app by performing the following steps:

1. Create a new .NET Core console project named GettingStartedWithEFCore, as shown in the following screenshot:

2. Create a new folder named Models in the project node and add the Person class to this newly created folder. This will be our model entity class, which we will use for CRUD operations.
3. Next, we need to install the EF Core package. Before we do that, it's important to know that EF Core provides support for a variety of databases. A few of the important ones are:
   • SQL Server
   • SQLite
   • InMemory (for testing)

The complete and comprehensive list can be seen at https://docs.microsoft.com/en-us/ef/core/providers/. We will be working with SQL Server on Windows for our learning purposes, so let's install the SQL Server package for Entity Framework Core. To do so, let's install the Microsoft.EntityFrameworkCore.SqlServer package from the NuGet Package Manager in Visual Studio 2017. Right-click on the project. Select Manage Nuget Packages and then search for Microsoft.EntityFrameworkCore.SqlServer. Select the matching result and click Install:

4. Next, we will create a class called Context, as shown here:

    public class Context : DbContext
    {
        public DbSet<Person&gt; Persons { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder 
optionsBuilder)
{
//// Get the connection string from configuration 
optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS
;Database=PersonDatabase;Trusted_Connection=True;");
}

protected override void OnModelCreating(ModelBuilder 
modelBuilder)
{
modelBuilder.Entity<Person>
().Property(nameof(Person.Name)).IsRequired();
}
}

The class looks quite simple, but it has the following subtle and important things to make note of:

• • The Context class derives from DbContext, which resides in the Microsoft.EntityFrameworkCore namespace. DbContext is an integral part of EF Core and if you have worked with EF, you will already be aware of it. An instance of DbContext represents a session with the database and can be used to query and save instances of your entities. DbContext is a combination of the Unit Of Work and Repository Patterns. Typically, you create a class that derives from DbContext and contains Microsoft.EntityFrameworkCore.DbSet properties for each entity in the model.
  • If properties have a public setter, they are automatically initialized when the instance of the derived context is created.
  • It contains a property named Persons (plural of the model class Person) of type DbSet<Person&gt;. This will map to the Persons table in the underlying database.
  • The class overrides the OnConfiguring method of DbContext and specifies the connection string to be used with the SQL Server database. The connection string should be read from the configuration file, appSettings.json, but for the sake of brevity and simplicity, it's hardcoded in the preceding code. The OnConfiguring method allows us to select and configure the data source to be used with a context using DbContextOptionsBuilder. Let's look at the connection string. Server= specifies the server. It can be .\SQLEXPRESS, .\SQLSERVER, .\LOCALDB, or any other instance name based on the installation you have done. Database= specifies the database name that will be created. Trusted_Connection=True specifies that we are using integrated security or Windows authentication. An enthusiastic reader should read the official Microsoft Entity framework documentation on configuring the context at https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext.
  •  The OnModelCreating method allows us to configure the model using the ModelBuilder Fluent API. This is the most powerful method of configuration and allows configuration to be specified without modifying the entity classes. The Fluent API configuration has the highest precedence and will override conventions and data annotations. The preceding code has same effect as the following data annotation has on the Name property in the Person class:

[Required]
public string Name { get; set; }

5. The preceding point highlights the flexibility and configuration that EF Core brings to the table. EF Core uses a combination of conventions, attributes, and Fluent API statements to build a database model at runtime. All we have to do is to perform actions on the model classes using a combination of these and they will automatically be translated to appropriate changes in the database. Before we conclude this point, let's have a quick look at each of the different ways to configure a database model:
   • EF Core conventions: The conventions in EF Core are comprehensive. They are the default rules by which EF Core builds a database model based on classes. A few of the simpler yet important default conventions are listed here:
     • EF Core creates database tables for all DbSet<TEntity&gt; properties in a Context class with the same name as that of the property. In the preceding example, the table name would be Persons based on this convention.
     • EF Core creates tables for entities that are not included as DbSet properties but are reachable through reference properties in the other DbSet entities. If the Person class had a complex/navigation property, EF Core would have created a table for it as well.
     • EF Core creates columns for all the scalar read-write properties of a class with the same name as the property by default. It uses the reference and collection properties for building relationships among corresponding tables in the database. In the preceding example, the scalar properties of Person correspond to a column in the Persons table.
     • EF Core assumes a property named ID or one that is suffixed with ID as a primary key. If the property is an integer type or Guid type, then EF Core also assumes it to be IDENTITY and automatically assigns a value when inserting the data. This is precisely what we will make use of in our example while inserting or creating a new Person.
     • EF Core maps the data type of a database column based on the data type of the property defined in the C# class. A few of the mappings between the C# data type to the SQL Server column data type are listed in the following table:

There are many other conventions, and we can define custom conventions as well. For more details, please read the official Microsoft documentation at https://docs.microsoft.com/en-us/ef/core/modeling/.

• • Attributes: Conventions are often not enough to map the class to database objects. In such scenarios, we can use attributes called data annotation attributes to get the desired results. The [Required] attribute that we have just seen is an example of a data annotation attribute.
  • Fluent API: This is the most powerful way of configuring the model and can be used in addition to or in place of attributes. The code written in the OnModelConfiguring method is an example of a Fluent API statement.

6. If we check now, there is no PersonDatabase database. So, we need to create the database from the model by adding a migration. EF Core includes different migration commands to create or update the database based on the model. To do so in Visual Studio 2017, go to Tools | Nuget Package Manager | Package Manager Console, as shown in the following screenshot:

This will open the Package Manager Console window. Select the Default Project as GettingStartedWithEFCore and type the following command:

add-migration CreatePersonDatabase

If you are not using Visual Studio 2017 and you are dependent on .NET Core CLI tooling, you can use the following command:

dotnet ef migrations add CreatePersonDatabase

7. We have not installed the Microsoft.EntityFrameworkCore.Design package, so it will give an error:

Your startup project 'GettingStartedWithEFCore' doesn't reference Microsoft.EntityFrameworkCore.Design. This package is required for the Entity Framework Core Tools to work. Ensure your startup project is correct, install the package, and try again.

So let's first go to the NuGet Package Manager and install this package. After successful installation of this package, if we run the preceding command again, we should be able to run the migrations successfully. It will also tell us the command to undo the migration by displaying the message To undo this action, use Remove-Migration. We should see the new files added in the Solution Explorer in the Migrations folder, as shown in the following screenshot:

8. Although we have migrations applied, we have still not created a database. To create the database, we need to run the following commands.

In Visual Studio 2017:

update-database –verbose

In .NET Core CLI:

dotnet ef database update

If all goes well, we should have the database created with the Persons table (property of type DbSet<Person&gt;) in the database. Let's validate the table and database by using SQL Server Management Studio (SSMS). If SSMS is not installed in your machine, you can also use Visual Studio 2017 to view the database and table.

9. Let's check the created database. In Visual Studio 2017, click on the View menu and select Server Explorer, as shown in the following screenshot:

10. In Server Explorer, right-click on Data Connections and then select Add Connection. The Add Connection dialog will show up. Enter .\SQLEXPRESS in the Server name (since we installed SQL EXPRESS 2017) and select PersonDatabase as the database, as shown in the following screenshot:

11. On clicking OK, we will see the database named PersonDatabase and if we expand the tables, we can see the Persons table as well as the _EFMigrationsHistory table. Notice that the properties in the Person class that had setters are the only properties that get transformed into table columns in the Persons table. Notice that the Age property is read-only in the class we created and therefore we do not see an age column in the database table, as shown in the following screenshot:

This is the first migration to create a database. Whenever we add or update the model classes or configurations, we need to sync the database with the model using the add-migration and update-database commands. With this, we have our model class ready and the corresponding database created. The following image summarizes how the properties have been mapped from the C# class to the database table columns:

Now, we will use the Context class to perform CRUD operations.

12.  Let's go back to our Main.cs and write the following code. The code is well commented, so please go through the comments to understand the flow:

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Getting started with EF Core");
            Console.WriteLine("We will do CRUD operations on Person 
            class.");
            //// Lets create an instance of Person class.

Person person = new Person()
{
Name = "Rishabh Verma",
Gender = true, //// For demo true= Male, false = 
Female. Prefer enum in real cases.
DateOfBirth = new DateTime(2000, 10, 23)
};

using (var context = new Context())
{
//// Context has strongly typed property named Persons 
which referes to Persons table.

//// It has methods Add, Find, Update, Remove to 
perform CRUD among many others.
//// Use AddRange to add multiple persons in once.
//// Complete set of APIs can be seen by using F12 on 
the Persons property below in Visual Studio IDE.
var personData = context.Persons.Add(person);
//// Though we have done Add, nothing has actually 
happened in database. All changes are in context 
only.
//// We need to call save changes, to persist these 
changes in the database.
context.SaveChanges();

//// Notice above that Id is Primary Key (PK) and hence 
has not been specified in the person object passed 
to context.
//// So, to know the created Id, we can use the below 
Id
int createdId = personData.Entity.Id;
//// If all goes well, person data should be persisted 
in the database.
//// Use proper exception handling to discover 
unhandled exception if any. Not showing here for 
simplicity and brevity. createdId variable would 
now hold the id of created person.

//// READ BEGINS
Person readData = context.Persons.Where(j => j.Id == 
createdId).FirstOrDefault();
//// We have the data of person where Id == createdId, 
i.e. details of Rishabh Verma.
//// Lets update the person data all together just for 
demonstarting update functionality.
//// UPDATE BEGINS
person.Name = "Neha Shrivastava"; 
person.Gender = false;
person.DateOfBirth = new DateTime(2000, 6, 15);
person.Id = createdId; //// For update cases, we need 
this to be specified.

//// Update the person in context.
context.Persons.Update(person);
//// Save the updates.
context.SaveChanges();

//// DELETE the person object.
context.Remove(readData);
context.SaveChanges();
}

Console.WriteLine("All done. Please press Enter key to 
exit...");
Console.ReadLine();
}
}

With this, we have completed our sample app to get started with EF Core. I hope this simple example will set you up to start using EF Core with confidence and encourage you to start exploring it further. The detailed features of EF Core can be learned from the official Microsoft documentation available at https://docs.microsoft.com/en-us/ef/core/.

If you're interested in learning more, head over to this book, .NET Core 2.0 By Example, by Rishabh Verma and Neha Shrivastava.

How to build a chatbot with Microsoft Bot framework

Working with Entity Client and Entity SQL

Get to know ASP.NET Core Web API [Tutorial]