05 - Entity Framework Intro

EF Core

Entity Framework (EF) Core is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework (EF 6) data access technology.

EF Core can serve as an object-relational mapper (O/RM), which:

• Enables .NET developers to work with a database using .NET objects.
• Eliminates the need for most of the data-access code that typically needs to be written.

EF Core supports many database engines: MS SQL, MariaDb, Postgresql, SQLite,...

Tooling

Install

dotnet tool install --global dotnet-ef

or update

dotnet tool update --global dotnet-ef

Add nuget packages as needed:

• Microsoft.EntityFrameworkCore.Design
• Microsoft.EntityFrameworkCore.Sqlite
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

Model

    public class Entry
    {
        public int Id { get; set; }
        
        
        [MaxLength(128)]
        public string FirstName  { get; set; }
        [MaxLength(128)]
        public string LastName { get; set; }

        public ICollection<Contact> Contacts { get; set; }

    }

    public class Contact
    {
        // Primary Key
        public int Id { get; set; }
        
        [MaxLength(128)]
        public string ContactValue { get; set; }

        // make Foreign Key visible
        public int EntryId { get; set; }
        public Entry Entry { get; set; }
        
        // FK
        public int ContactTypeId { get; set; }
        public ContactType ContactType { get; set; }
    }

    public class ContactType
    {
        public int Id { get; set; }
        
        [MaxLength(32)]
        public string ContactTypeValue { get; set; } // skype, phone, etc...
        
        public ICollection<Contact> Contacts { get; set; }
    }

Context

The context object allows querying and saving data.

    public class ApplicationDbContext : DbContext
    {
        public DbSet<Contact> Contacts { get; set; }
        public DbSet<ContactType> ContactTypes { get; set; }
        public DbSet<Entry> Entries { get; set; }
        
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        // not recommended - do not hardcode DB conf!
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=MyDatabase;Trusted_Connection=True;");
        }
    }

When using SQLite – specify full path to db location.

optionsBuilder.UseSqlite("Data Source=/fullpath/mydb.db");

Basics

Steps to follow

• Write your models (POCO-s)
• Every class used in EF needs primary key (PK)
• By convention, PK-s are named <ClassName>Id or Id
• Declare context class, derived from DbContext
• Inside context class add DbSet<YourPoco>
• If you don’t include all pocos in DbSets, EF will autodiscover them and create tables for them
• Don’t rely on EF automatic creation of properties, relations, or any other configuration! Check your DB structure, does it match your model 1:1?

Initial migration

EF Core will not automatically create for you db creation code

From command line in solution folder

Create new migration

dotnet ef migrations add InitialCreate --project DAL --startup-project ConsoleApp 

Update DB

dotnet ef database update --project DAL --startup-project ConsoleApp 

Delete DB

dotnet ef database drop --project DAL --startup-project ConsoleApp 

Migrations folder is created, with necessary classes for DB creation and Model structure

Verify DB Structure

• Check DB structure you got!
• It should match your model 1:1
• Look out for automatically created fields or tables
• Use VS View->Server Explorer or MS SQL Server Management Studio
  • Or any other tool suitable for your DB engine
• EF Core supports several DB engines (most have multiple connector providers)

Use your model/context

    static void Main(string[] args)
    {
        using (var db = new ApplicationDbContext())
        {
            db.Entries.Add(new Entry() { FirstName="Andres", LastName = "Käver" });
            var count = db.SaveChanges();
            Console.WriteLine("{0} records saved to database", count);

            Console.WriteLine();
            Console.WriteLine("All entries in database:");
            foreach (var entry in db.Entries)
            {
                Console.WriteLine($" - {entry.FirstName} {entry.LastName}");
            }
        }
    }

    // replace <@UNI_ID_HERE@> with your UNI-ID.
    private static string ConnectionString =
        "Server=barrel.itcollege.ee;User Id=student;Password=Student.Pass.1;Database=student_<@UNI_ID_HERE@>;MultipleActiveResultSets=true";

    public static void Main(string[] args)
    {
        
        var contextOptions = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseSqlServer(ConnectionString)
            .EnableDetailedErrors()
            .EnableSensitiveDataLogging()
            .Options;

        using var context = new ApplicationDbContext(contextOptions);
        // use your db context
    }

Food for thought

Intermediate-level knowledge or higher of the underlying database server is essential to architect, debug, profile, and migrate data in high performance production apps. For example, knowledge of primary and foreign keys, constraints, indexes, normalization, DML (Data Manipulation Language) and DDL (Data Definition Language) statements, data types, profiling, etc.