Getting Started with Entity Framework Core

Tags: EFCore, dotConnect, Oracle, MSSQLServer, dotnetcore

Goals

Having a little experience with previous versions of the Entity Framework, I wanted to learn Entity Framework Core. We use a mixture of Oracle and MS SQL Server at work, and I wanted to understand the differences of getting started with each. So in this article I will show how to:

Setup a sample MS SQL Server instance in Docker
Create a new .NET C# project
Define the model
Define the DbContext
Define the configuration
Define some simple queries
Outline the differences between the application calling MS SQL Server and the application calling Oracle

Source Code

Here is the source code for the two applications we will be building in this article:

Microsoft SQL Server

Let’s get started with MS SQL Server. Step one is to install Docker.

Once you have Docker installed, verify that you are in Windows container mode by right clicking on the docker whale icon in the task bar. Then verify that the menu option says Swith to Linux Containers. If not then it should say Swith to Windows Containers and you will need to click it.

Docker Icon

Now open an administrative command line and run the following command to pull down the SQL server container.

docker pull microsoft/mssql-server-windows-express

Run the following command. Be sure to replace the SA password with one that meets the SQL Server Password Policy.

docker run -d -p 1433:1433 -e sa_password=<YOUR_PWD> -e ACCEPT_EULA=Y -v c:/data/:c:/data/ microsoft/mssql-server-windows-express

Use the docker ps command to get the container ID that you want to connect to and use it to replace the parameter placeholder **** in the commands below. You can get a lot more information including your container’s IP address by running **docker inspect **. Run the following command to connect to the SQL Server. Be sure to replace the container id.

docker exec -it <DOCKER_CONTAINER_ID> sqlcmd -S. -Usa

Run the following commands to create our database and user.

CREATE DATABASE Person
ON PRIMARY
( NAME = N'Person', FILENAME = N'C:\Data\Person1.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB );
GO
CREATE LOGIN PersonUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
USE Person;
GO
CREATE USER PersonUser FOR LOGIN PersonUser
WITH DEFAULT_SCHEMA = Person;
GO
EXEC sp_addrolemember 'db_owner', 'PersonUser';
GO

Run the following commands to create our PERSON table and insert some rows. It has 4 columns: ID and NAME are obvious, but the SCID column represents a person’s unique Id at our company and the REPORTS_TO_SCID represents to whom that person reports.

CREATE TABLE PERSON(
    ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    NAME  VARCHAR(60)   NOT NULL,
    SCID VARCHAR(10)    NOT NULL    UNIQUE,
    REPORTS_TO_SCID VARCHAR(10) NOT NULL,
);
GO
INSERT INTO [dbo].[PERSON]
           ([NAME]
           ,[SCID]
           ,[REPORTS_TO_SCID])
     VALUES
           ('Perry White'
           ,'00000001'
           ,'00000001')
GO
INSERT INTO [dbo].[PERSON]
           ([NAME]
           ,[SCID]
           ,[REPORTS_TO_SCID])
     VALUES
           ('Lois Lane'
           ,'00000002'
           ,'00000001')
GO
INSERT INTO [dbo].[PERSON]
           ([NAME]
           ,[SCID]
           ,[REPORTS_TO_SCID])
     VALUES
           ('Clark Kent'
           ,'00000003'
           ,'00000001')
GO
INSERT INTO [dbo].[PERSON]
           ([NAME]
           ,[SCID]
           ,[REPORTS_TO_SCID])
     VALUES
           ('Jimmy Olsen'
           ,'00000004'
           ,'00000001')
GO

We will now be creating our .NET Core Console application. I will be using the new dotnet Cli, but you can certainly use Visual Studio to create your project. If you do not have .NET Core installed you can download it here.

Run the following commands.

mkdir EntityFrameworkCoreSQL

cd EntityFrameworkCoreSQL

dotnet new console

dotnet restore

dotnet run

You should see Hello World displayed on the command prompt.

Now let’s create our Person model class. Create a new Person.cs file and add the following code.

using System;
using System.Collections.Generic;

namespace EntityFrameworkCoreSQL
{
    public class Person
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Scid { get; set; }
        public string ManagerScid { get; set; }
        public virtual Person Manager { get; set; }
        public virtual ICollection<Person> DirectReports { get; set; }

    }
}

Now let’s add the nuget packages we need for EntityFrameworkCore by running the following commands.

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Relational
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Now we are going to add our DbContext class which will contain all of our EntityFramework configuration as well as what we use to interact with the database. Add the following code to your Person.cs file. The context should be off in its own file, but I am going to keep things as simple as possible. Be sure to replace the with your container’s actual IP address. You can get the container’s IP address by running the command **docker inspect **.

 public class PersonContext : DbContext
    {
        public DbSet<Person> Persons { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=<CONTAINER_IPADDRESS>;Database=Person;User Id=PersonUser;Password=340$Uuxwp7Mcxo7Khy;");

        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Person>().ToTable("Person");
            modelBuilder.Entity<Person>(entity =>
            {
                entity.HasKey(b => b.Id);

                entity.Property(b => b.Name)
                .HasColumnName("NAME");

                entity.Property(b => b.Scid)
                    .HasColumnName("SCID");

                entity.Property(b => b.ManagerScid)
                .HasColumnName("REPORTS_TO_SCID");

                entity.HasOne(b => b.Manager)
                .WithMany(b => b.DirectReports)
                .HasForeignKey(e => e.ManagerScid)
                .HasPrincipalKey(b => b.Scid);
            });
        }
    }

We need to add the using statement for Entity Framework, so add the following line at the top of the Person.cs file.

using Microsoft.EntityFrameworkCore;

Let’s break down what’s going on in our PersonContext. Inside the OnConfiguring method we are setting up our connection string. In the OnModelCreating method we are using the Fluent Api syntax to override Entity Framework’s default behaviour (conventions) including the mapping of entities and their properties to a relational database. Configuration can be applied in two ways, through decorating classes and properties with attributes, or by using the Fluent Api. Entity().ToTable(“Person”) is mapping model Person to table Person. This is not neccessary in this case, but if our table and model were not named the same it would be required. Entity.HasKey(b => b.Id) is specifing that Id is a key on our table. HasColumnName(“Name”) is mapping the Name property of Person to the proper column. Again in this case it is not needed since the two are named the same, but property ManagerScid requires the mapping to REPORTS_TO_SCID since they are named differently. The last configuration defines the references for the virtual properties Manager and DirectReports. HasOne(b => b.Manager) indicates that there is a one-to-one relationship between a Person and a Manager. WithMany(b => b.DirectReports) indicates that there is a one-to-many relationship between Person and DirectReports. HasForeignKey( e => e.ManagerScid).HasPrincipalKey(b => b.Scid) defines the foreign key relationship from ManagerScid to Scid.

We have completed all the setup required, so now let’s add some code to our Program.cs Main method to instantiate a PeronContext and query our database. Add the following code to the Program.cs file.

using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;

namespace EntityFrameworkCoreSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new PersonContext())
            {
                Console.WriteLine("### Query 1");
                var persons = db.Persons.Include(person => person.Manager);
                foreach (var person in persons)
                {
                    Console.WriteLine($"Scid: {person.Scid} Name: {person.Name}  Manager: {person.Manager?.Name}");
                }
                Console.WriteLine("### Query 2");
                var personsList = db.Persons.Select(p => new Person() { Name = p.Name, Manager = new Person()
                {
                    Name = p.Manager.Name
                }});
                foreach (var person in personsList)
                {
                    Console.WriteLine($"Scid: {person.Scid} Name: {person.Name}  Manager: {person.Manager?.Name}");
                }
                Console.WriteLine("### Query 3");
                var directReports = db.Persons.Where(p => p.Scid == "00000001").Select(p => new Person() { Name = p.Name, DirectReports = p.DirectReports}).FirstOrDefault();
                foreach (var person in directReports.DirectReports)
                {
                    Console.WriteLine($"Manager: {directReports.Name} EmployeeName: {person.Name}");
                }
            }
        }
    }
}

Now if we build and run the application, you should see the output of our 3 queries. It should look similar to this.

### Query 1
Scid: 00000001 Name: Perry White Manager: Perry White
Scid: 00000002 Name: Lois Lane Manager: Perry White
Scid: 00000003 Name: Clark Kent Manager: Perry White
Scid: 00000004 Name: Jimmy Olsen Manager: Perry White
### Query 2
Scid: Name: Perry White Manager: Perry White
Scid: Name: Lois Lane Manager: Perry White
Scid: Name: Clark Kent Manager: Perry White
Scid: Name: Jimmy Olsen Manager: Perry White
### Query 3
Manager: Perry White EmployeeName: Perry White
Manager: Perry White EmployeeName: Lois Lane
Manager: Perry White EmployeeName: Clark Kent
Manager: Perry White EmployeeName: Jimmy Olsen

Let’s break down what’s going on here. First thing we do is create an instance of our PersonContext in a using block. We then create our first query. One thing to note here is that we are explicitly including the navigation property Manager because Entity Framework Core currently does not support lazy loading so we have to be explicit about what we want to include. Here is the documentation on loading related data. In the second query we are specifing exactly what fields we want returned, which includes the Manager’s name so we can drop the Include call on the query. In the third query we are getting the direct reports for Perry White by calling the Where method and specifiing Perry’s Scid. Again we are explicitly specifing the fields we want returned so we don’t have to use the Include method to get the DirectReports data.

So we have successfully completed the following using MS SQL Server.

Setup a sample MS SQL Server instance in Docker
Created a new dotnet project
Defined the model
Defined the DbContext
Defined the configuration
Defined some simple queries

Now let’s quickly highlight the differences in doing the same thing with an Oracle database.

Oracle

I am not going to go into creating the Oracle instance, but rather focus on the differences in the .NET code. First thing to note is that Oracle ODP.NET does not currently support Entity Framework Core. Fortunately Devart has a great ADO.NET Provider for Oracle called dotConnect for Oracle. It is a paid product, but well worth the investment if you need to work with Oracle databases. They typically support new .NET features while they are still in development. For example they supported Entity Framework Core in early April 2016 while the 1.0 version of Core wasn’t released until June 27, 2016.

Luckily they provide a free 30 day trial which you can download and use to complete this walk through. So go do that now. dotConnect for Oracle currently does not support .NET Core applications, so we will be creating a standard .NET Framework console application in Visual Studio.

Now let’s create our Person model class. Create a new Person.cs file and add the following code.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace EntityFrameworkCoreOracle
{
    public class Person
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Scid { get; set; }
        public string ManagerScid { get; set; }
        public virtual Person Manager { get; set; }
        public virtual ICollection<Person> DirectReports { get; set; }

    }
}

Nothing has changed here. It is the same class definition as with our MS SQL Server implementation. Now let’s add our DbContext class. Add the following code to your Person.cs file. The context should be off in its own file, but I am going to keep things as simple as possible. Be sure to replace the fields in the connection string with the appropiate values.

public class PersonContext : DbContext
    {
        public DbSet<Person> Persons { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseOracle(@"Data Source=<DATA_SOURCE>;User ID=<USER_ID>;Password=<PASSWORD>;");

        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("PersonSchema");
            modelBuilder.Entity<Person>().ToTable("Person");
            modelBuilder.Entity<Person>(entity =>
            {
                entity.HasKey(b => b.Id);

                entity.Property(b => b.Name)
                .HasColumnName("NAME");

                entity.Property(b => b.Scid)
                    .HasColumnName("SCID");

                entity.Property(b => b.ManagerScid)
                .HasColumnName("REPORTS_TO_SCID");

                entity.HasOne(b => b.Manager)
                .WithMany(b => b.DirectReports)
                .HasForeignKey(e => e.ManagerScid)
                .HasPrincipalKey(b => b.Scid);
            });
        }
    }

There are two differences here. The first one being in the OnConfiguring method. We now use the method UseOracle rather than UseSqlServer; passing the method our connection string. The second change is in the OnModelCreating method. We specify the default schema using the HasDefaultSchema method; passing it the appropriate schema. You will also need to add the appropiate references for dotConnect shown below and the Microsoft.EntityFrameworkCore nuget package.

Devart.Data.dll
Devart.Data.Oracle.dll
Devart.Data.Oracle.EFCore.dll

Now let’s add some code to our Program.cs Main method to instantiate a PeronContext and query our database. Add the following code to the Program.cs file.

using Devart.Data.Oracle.Entity.Configuration;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;

namespace EntityFrameworkCoreOracle
{
    class Program
    {
        static void Main(string[] args)
        {
            var config = OracleEntityProviderConfig.Instance;
            config.Workarounds.DisableQuoting = true;

            using (var db = new PersonContext())
            {
                Console.WriteLine("### Query 1");
                var persons = db.Persons.Include(person => person.Manager);
                foreach (var person in persons)
                {
                    Console.WriteLine($"Scid: {person.Scid} Name: {person.Name}  Manager: {person.Manager?.Name}");
                }
                Console.WriteLine("### Query 2");
                var personsList = db.Persons.Select(p => new Person()
                {
                    Name = p.Name,
                    Manager = new Person()
                    {
                        Name = p.Manager.Name
                    }
                });
                foreach (var person in personsList)
                {
                    Console.WriteLine($"Scid: {person.Scid} Name: {person.Name}  Manager: {person.Manager?.Name}");
                }
                Console.WriteLine("### Query 3");
                var directReports = db.Persons.Where(p => p.Scid == "00000001").Select(p => new Person() { Name = p.Name, DirectReports = p.DirectReports }).FirstOrDefault();
                foreach (var person in directReports.DirectReports)
                {
                    Console.WriteLine($"Manager: {directReports.Name} EmployeeName: {person.Name}");
                }
            }
        }
    }
}

The only change from our MS SQL Server example is the addition of these two lines of code. Entity Framework by default adds quotes around your object names such as tables. dotConnect provides a number of work arounds such as the ability to disable quoting which we are doing here.

var config = OracleEntityProviderConfig.Instance;
config.Workarounds.DisableQuoting = true;

You should now be able to build and run the code and see the same output as we saw earlier given that you have the same data in your database. I was suprised at the few number of changes that were required to get the same sample working with Oracle. I hope that this has been helpful, and if you have any questions please feel free to leave a comment.

References

Joshua

March 3, 2017

Uncategorized

4 responses to “Getting Started with Entity Framework Core”

Adding Diagnostics in Entity Framework Core | Joshua Chini says:

March 8, 2017 at 3:06 pm

[…] my last article I talked about getting started with Entity Framework Core. In this article I want to cover the […]

LikeLike

Getting Started Building the Person Service | Joshua Chini says:

March 19, 2017 at 11:55 am

[…] Core – Getting Started and […]

LikeLike

Joe Blow says:

January 5, 2018 at 7:47 pm

Why do you have “LinqPad” as a tag, but nowhere in the article do you discuss using LinqPad?

LikeLike

- Joshua says:
  
  March 16, 2018 at 12:59 am
  
  I developed the code for that article in LinqPad, but you are right I don’t mention LinqPad anywhere in the article so I have updated the tags. Thank you.
  
  LikeLike

Joshua Chini