C# and Entity Framework

Entity Framework, or EF for short, provides a means for .NET based applications, written in C# and other .NET languages, to interact with relational databases. It was first released by Microsoft back in 2008 and is classed as an ORM, or Object Relational Mapper. An ORM allows for the simplification of mapping objects, found in programming languages that support Object-Oriented Programming (OOP), to the tables and columns of a relational database.

The latest version of Entity Framework, Entity Framework Core, works with a number of different databases, including SQL Server, SQLite, MySQL and PostgreSQL, both on-premises and in the cloud, through services such as Azure.

Workflow Choices

There are three different ways of working with Entity Framework:

• Database First – As the name suggests, this option involves the creation of the database first. Entity Framework then generates the C# classes, based on the database. This is the traditional approach, that has been used by developers for some time.
• Code First – Here, the C# classes are created first and then Entity Framework generates the database tables, based on these classes. This workflow is relatively new in comparison to database first.
• Model First – This workflow involves the creation of a model, known as a UML, or Unified Modelling Language, diagram, using the model designer in Visual Studio. This model is then used to generate both the database tables, as well as the C# classes.

Entity Framework Core and SQL Server

In order to use Entity Framework Core with an SQL Server database, two packages need to be included in the project, 'Microsoft.EntityFrameworkCore.SqlServer' and 'Microsoft.EntityFrameworkCore.Tools'. This can be done in a number of different ways, depending on what Integrated Development Environment (IDE) is being used. Visual Studio incorporates NuGet Package Manager, which allows for packages to be searched for and installed. It also has an integrated package manager console, where these packages can be added using the following commands.

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

For IDEs that don't have a built in package manager, PowerShell can be used. The following commands can be used to install the above mentioned packages. Before running these command it is necessary to navigate to the folder where the project resides.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

If Visual Studio is being used, together with a database first workflow, the Package Manager console can be utilised to generate the Entity Framework code, based on the database. The below example uses a database called 'Demo', which contains one table, called 'person', that was used in the previous C# and SQL Server examples. The following command will generate the code. Note that this is one command, a line break has been put in for display purposes only.

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer -Connection 
"Server=MSSQLSERVERDEMO; Database=Demo; User Id=DemoUN; Password=DemoPW;"

This command generates a DbContext class, as well as one class for each table in the database, in this case, just one for the 'person' table. A warning is produced regarding including sensitive information in the connection string and offers advice on what can be done. It should be noted that 'Person' has been automatically pluralised by the code generation process to be 'People' at certain points in the code.

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

#nullable disable

namespace DemoEFCoreSQLServer
{
    public partial class DemoContext : DbContext
    {
        public DemoContext()
        {
        }

        public DemoContext(DbContextOptions<DemoContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Person> People { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Server=MSSQLSERVERDEMO; " +
                    "Database=Demo; User Id=DemoUN; Password=DemoPW;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");

            modelBuilder.Entity<Person>(entity =>
            {
                entity.ToTable("person");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.Dob)
                    .HasColumnType("date")
                    .HasColumnName("dob");

                entity.Property(e => e.Firstname)
                    .IsRequired()
                    .HasMaxLength(50)
                    .IsUnicode(false)
                    .HasColumnName("firstname");

                entity.Property(e => e.Lastname)
                    .IsRequired()
                    .HasMaxLength(50)
                    .IsUnicode(false)
                    .HasColumnName("lastname");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasMaxLength(30)
                    .IsUnicode(false)
                    .HasColumnName("title");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

using System;
using System.Collections.Generic;

#nullable disable

namespace DemoEFCoreSQLServer
{
    public partial class Person
    {
        public int Id { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        public string Title { get; set; }
        public DateTime Dob { get; set; }
    }
}

Once this code has been generated, the data in the database can be queried and processed as desired. Below are examples for selecting, inserting, updating and deleting data with Entity Framework.

using System;
using System.Linq;

namespace DemoEFCoreSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new DemoContext())
            {
                var people = context.People.ToList();

                foreach (var person in people)
                {
                    Console.WriteLine(person.Id + ": " +
                        person.Lastname + ", " + person.Firstname + 
                        " (" + person.Title + ")");
                }
            }
        }
    }
}

1: Smith, Bob (Mr)
3: Bloggs, Fred (Mr)
4: White, Alan (Mr)
5: Bloggs, Fiona (Mrs)
6: Davis, Zoe (Miss)
7: Ingram, Tom (Mr)
8: Thomas, Karen (Mrs)
9: Yates, Samantha (Miss)

using System;

namespace DemoEFCoreSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            DemoContext context = new DemoContext();
            Person person = new Person
            {
                Firstname = "Sally",
                Lastname = "Simpson",
                Title = "Mrs",
                Dob = new DateTime(1987, 3, 21)
            };

            context.People.Add(person);
            context.SaveChanges();
        }
    }
}

using System;

namespace DemoEFCoreSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            DemoContext context = new DemoContext();
            Person person = context.People.Find(6);

            person.Lastname = "Ingram";
            person.Title = "Mrs";

            context.SaveChanges();
        }
    }
}

using System;

namespace DemoEFCoreSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            DemoContext context = new DemoContext();

            context.People.Remove(context.People.Find(4));

            context.SaveChanges();
        }
    }
}

Further Resources

• Entity Framework documentation.
• Entity Framework 6.
  • Database First.
  • Code First.
  • Model First.
• Entity Framework Core.
  • Database First.
  • Code First.