Entity Framework Core Tutorial: Getting Started

Posted on Sep 3, 2024 in Computers

1. Install Entity Framework

Install-Package Microsoft.EntityFrameworkCore.Sqlite

Install-Package Microsoft.EntityFrameworkCore.Tools –Pre

Tip

In a real application, you would put each class in a separate file and put the connection string in the App.Config file and read it out using ConfigurationManager. For the sake of simplicity, we are putting everything in a single code file for this tutorial.

2. Create Your Model

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

namespace EFGetStarted.UWP
{
    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Filename=Blogging.db");
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }
}

3. Use Migrations to Create a Database

Add-Migration MyFirstMigration

Since we want the database to be created on the device that the app runs on, we will add some code to apply any pending migrations to the local database on application startup. The first time that the app runs, this will take care of creating the local database for us.

Add this in App.xaml

public App()
{
    this.InitializeComponent();
    this.Suspending += OnSuspending;

    using (var db = new BloggingContext())
    {
        db.Database.Migrate();
    }
}

Tip

If you make future changes to your model, you can use the Add-Migration command to scaffold a new migration to apply the corresponding changes to the database. Any pending migrations will be applied to the local database on each device when the application starts.

EF uses a __EFMigrationsHistory table in the database to keep track of which migrations have already been applied to the database.

4. Get List of Rows

using (var db = new BloggingContext())
{
    Blogs.ItemsSource = db.Blogs.ToList();
}

5. Insert Data

using (var db = new BloggingContext())
{
    var blog = new Blog { Url = NewBlogUrl.Text };
    db.Blogs.Add(blog);
    db.SaveChanges();

    Blogs.ItemsSource = db.Blogs.ToList();
}

6. Including & Excluding Types

Include

public DbSet<Blog> Blogs { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>();
}

Exclude

[NotMapped]

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Ignore<Blog>();
}

Relationships

• Dependent entity: This is the entity that contains the foreign key property(s). Sometimes referred to as the ‘child‘ of the relationship.

• Principal entity: This is the entity that contains the primary/alternate key property(s). Sometimes referred to as the ‘parent‘ of the relationship.

• Foreign key: The property(s) in the dependent entity that is used to store the values of the principal key property that the entity is related to.

• Principal key: The property(s) that uniquely identifies the principal entity. This may be the primary key or an alternate key.

• Navigation property: A property defined on the principal and/or dependent entity that contains a reference(s) to the related entity(s).

• Collection navigation property: A navigation property that contains references to many related entities.

• Reference navigation property: A navigation property that holds a reference to a single related entity.

• Inverse navigation property: When discussing a particular navigation property, this term refers to the navigation property on the other end of the relationship.

Example

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

The following code listing shows a one-to-many relationship between Blog and Post

• Post is the dependent entity

• Blog is the principal entity

• Post.BlogId is the foreign key

• Blog.BlogId is the principal key (in this case it is a primary key rather than an alternate key)

• Post.Blog is a reference navigation property

• Blog.Posts is a collection navigation property

• Post.Blog is the inverse navigation property of Blog.Posts (and vice versa)

It is recommended to have a foreign key property defined in the dependent entity class.

More Detail: https://docs.microsoft.com/en-us/ef/core/modeling/relationships

Computed Columns

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .Property(p => p.DisplayName)
        .HasComputedColumnSql("[LastName] + ', ' + [FirstName]");
}

Sequences

class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasSequence<int>("OrderNumbers", schema: "shared")
            .StartsAt(1000)
            .IncrementsBy(5);

        modelBuilder.Entity<Order>()
            .Property(o => o.OrderNo)
            .HasDefaultValueSql("NEXT VALUE FOR shared.OrderNumbers");
    }
}

public class Order
{
    public int OrderId { get; set; }
    public int OrderNo { get; set; }
    public string Url { get; set; }
}

Loading All Data

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.ToList();
}

Loading a Single Entity

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);
}

Filtering

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Where(b => b.Url.Contains("dotnet"))
        .ToList();
}

Adding Data

using (var db = new BloggingContext())
{
    var blog = new Blog { Url = "http://sample.com" };
    db.Blogs.Add(blog);
    db.SaveChanges();

    Console.WriteLine(blog.BlogId + ": " +  blog.Url);
}

Updating Data

using (var db = new BloggingContext())
{
    var blog = db.Blogs.First();
    blog.Url = "http://sample.com/blog";
    db.SaveChanges();
}

Deleting Data

using (var db = new BloggingContext())
{
    var blog = db.Blogs.First();
    db.Blogs.Remove(blog);
    db.SaveChanges();
}

You can combine multiple Add/Update/Remove operations into a single call to SaveChanges

using (var db = new BloggingContext())
{
    db.Blogs.Add(new Blog { Url = "http://sample.com/blog_one" });
    db.Blogs.Add(new Blog { Url = "http://sample.com/blog_two" });

    var firstBlog = db.Blogs.First();
    firstBlog.Url = "";

    var lastBlog = db.Blogs.Last();
    db.Blogs.Remove(lastBlog);

    db.SaveChanges();
}

Raw SQL Queries

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();

var user = "johndoe";

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

Composing with LINQ

var searchTerm = ".NET";

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.SearchBlogs {0}", searchTerm)
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();