Working with databases using Entity Framework Core (migrations, querying, saving)

Working with Databases in ASP.NET Core using Entity Framework Core

This tutorial explores how to interact with databases in ASP.NET Core applications using Entity Framework Core (EF Core). We will cover migrations, querying data, and saving changes.

Setting up Entity Framework Core

First, you need to install the necessary NuGet packages. Open your Package Manager Console and run:

This code defines the ApplicationDbContext, which inherits from DbContext. It represents your database context. DbSet Blogs and DbSet Posts represent the tables in your database.

Replace SqlServer with your desired database provider (e.g., Microsoft.EntityFrameworkCore.Sqlite, Microsoft.EntityFrameworkCore.Cosmos etc.).

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

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

Defining Entity Models

These code snippets define two entity models: Blog and Post. These classes represent the structure of your database tables.

Each property represents a column in the table. The Blog model has a one-to-many relationship with the Post model, meaning a blog can have multiple posts.

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; }
}

Configuring the Database Connection

This code configures the database connection in your Startup.cs file (or Program.cs in .NET 6+). It uses the UseSqlServer method to specify that you're using SQL Server and provides the connection string from your appsettings.json file.

Remember to replace "DefaultConnection" with the name of your connection string in appsettings.json.

Example appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\mssqllocaldb;Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

Creating Migrations

Migrations allow you to evolve your database schema over time. To create a migration, run the following command in the Package Manager Console:

This command creates a new migration named InitialCreate. It will generate code that creates the tables defined in your DbContext.

Add-Migration InitialCreate

Applying Migrations

To apply the migration to your database, run the following command in the Package Manager Console:

This command executes the migration and updates your database schema.

Update-Database

Querying Data

This code shows how to query data from the database using EF Core.

• context.Blogs.ToList() retrieves all blogs from the database.
• context.Blogs.Find(1) retrieves a blog with an ID of 1.
• context.Blogs.Where(b => b.Url.Contains("example")).ToList() retrieves all blogs where the URL contains the string "example".

using (var context = new ApplicationDbContext())
{
    // Get all blogs
    var blogs = context.Blogs.ToList();

    // Get a blog by ID
    var blog = context.Blogs.Find(1);

    // Get blogs with a specific URL
    var blogsWithUrl = context.Blogs.Where(b => b.Url.Contains("example")).ToList();
}

Saving Data

This code demonstrates how to save changes to the database using EF Core.

• context.Blogs.Add(blog) adds a new blog to the database.
• context.SaveChanges() saves all changes to the database.
• To update an existing entity, you first retrieve it from the database, then modify its properties, and finally call SaveChanges().
• To delete an entity, you first retrieve it from the database, then call context.Blogs.Remove(blogToDelete), and finally call SaveChanges().

using (var context = new ApplicationDbContext())
{
    // Create a new blog
    var blog = new Blog { Url = "http://example.com" };
    context.Blogs.Add(blog);
    context.SaveChanges();

    // Update an existing blog
    var existingBlog = context.Blogs.Find(1);
    existingBlog.Url = "http://newurl.com";
    context.SaveChanges();

    // Delete a blog
    var blogToDelete = context.Blogs.Find(2);
    context.Blogs.Remove(blogToDelete);
    context.SaveChanges();
}

Concepts Behind the Snippet

This snippet showcases the core principles of ORM (Object-Relational Mapping). EF Core acts as a bridge between your object-oriented code and the relational database. It translates your C# objects into database tables and vice-versa, simplifying data access and manipulation.

Real-Life Use Case Section

Imagine building an e-commerce application. You can use EF Core to manage products, categories, customers, orders, and other entities. You can query for products based on category, price, or availability, and easily save new products or update existing ones.

Best Practices

• Use Dependency Injection: Inject your DbContext into your controllers or services for better testability and maintainability.
• Asynchronous Operations: Use asynchronous methods (e.g., ToListAsync(), SaveChangesAsync()) to avoid blocking the main thread, especially in web applications.
• Track Changes Efficiently: Only load the entities you need to modify to reduce memory consumption.

Interview Tip

Be prepared to explain the benefits of using an ORM like EF Core, such as increased productivity, improved code maintainability, and reduced boilerplate code. Also, be ready to discuss potential performance considerations and how to optimize queries.

When to Use Them

EF Core is a good choice when you need to interact with a relational database in a .NET application. It's particularly useful for applications with complex data models and when you want to avoid writing raw SQL queries.

Memory Footprint

The memory footprint of EF Core depends on the size of your data model and the amount of data you're querying and saving. Loading large amounts of data into memory can impact performance. Consider using techniques like pagination and filtering to reduce the amount of data loaded at once.

Alternatives

Alternatives to EF Core include Dapper (a micro-ORM), ADO.NET (for direct database access), and other ORMs like NHibernate.

Pros

• Increased Productivity: Simplifies database interaction.
• Code Maintainability: Improves code organization and readability.
• Database Agnostic: Supports multiple database providers.

Cons

• Performance Overhead: Can be slower than raw SQL for complex queries.
• Learning Curve: Requires understanding of ORM concepts and EF Core API.
• Potential for Lazy Loading Issues: Can lead to unexpected database queries if not used carefully.