Simplifying Database Access in C# with Entity Framework Core

We have finally reached the fifth article in our "Mastering C#" series, and it has been an incredibly enlightening journey. I hope you have gained as much from it as I have. In today's article, we will explore how to manage databases easily in C# using Entity Framework Core. This guide promises to be both simplified and highly informative, so stay tuned until the end.

Prerequisites

Before diving into this guide, ensure you have the following prerequisites:

• Basic Knowledge of C#:

  • Syntax and Structure:
    Familiarity with C# syntax, data types, control structures (if, for, while loops), and basic OOP concepts (classes, objects, inheritance).

  • Development Environment:
    Experience with an Integrated Development Environment (IDE) like Visual Studio.

• Understanding of .NET Framework/Core:

  • .NET Basics:
    Basic understanding of the .NET ecosystem, including .NET Framework and .NET Core.

  • Project Structure:
    Familiarity with creating and managing projects within the .NET ecosystem.

• Basic Knowledge of Databases:

  • Database Concepts:
    Understanding of relational databases, tables, columns, and rows.

  • SQL:
    Basic knowledge of SQL for querying databases (SELECT, INSERT, UPDATE, DELETE).

  • Database Management:
    Experience with a database management system (DBMS) like SQL Server, MySQL, or SQLite.

• Basic Understanding of ORM (Object-Relational Mapping):

  • Concepts of ORM:
    Awareness of what ORM is and how it bridges the gap between object-oriented programming and relational databases.

• Familiarity with LINQ (Language Integrated Query):

  • LINQ Basics:
    Basic understanding of how to use LINQ to query collections in C#. (This was discussed in the second article of our series.)

• Tools and Software:

  • Visual Studio:
    Installed and configured Visual Studio (preferably the latest version).

  • Database Management Tool:
    A tool like SQL Server Management Studio (SSMS) or a similar tool for your chosen database.

• Experience with ASP.NETCore:

  • Web Development:
    Basic knowledge of creating web applications using ASP.NET Core.

  • MVC Pattern:
    Understanding of the Model-View-Controller (MVC) pattern.

• Version Control Systems:

  • Git:
    Basic understanding of version control systems like Git for managing project code.

Table of Contents

• Introduction to Entity Framework Core

• Installation and Configuration

• Setting Up the DbContext

• Basic CRUD Operations

• Advanced Querying and Performance Tuning

• Common Pitfalls

• Resources for Further Learning

Introduction to Entity Framework Core

Entity Framework Core (EF Core) is an ORM (Object-Relational Mapping) that allows developers to work with databases using .NET objects. Instead of writing complex SQL queries, you can interact with your data using C# classes and methods.

Why is it Useful?

• Productivity:
  It speeds up development by reducing the need to write repetitive SQL code.

• Maintainability:
  It makes it easier to manage and evolve your data model.

• Abstraction:
  It provides a higher level of abstraction, making your codebase cleaner and more understandable.

Installation and Configuration

To get started with EF Core, you will need to install it in your project. Here are the steps below:

Create a New C# Project:

• Open Visual Studio and create a new Console App (.NET Core) project.

Install EF Core Packages:

• Open the NuGet Package Manager by right-clicking on your project in the Solution Explorer and selecting "Manage NuGet Packages."

• Search for Microsoft.EntityFrameworkCore and install it.

• Also, install the database provider package for your preferred database (e.g., Microsoft.EntityFrameworkCore.SqlServer for SQL Server).

Alternatively, use the Package Manager Console:

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

Setting Up the DbContext

The DbContext class is the main class that coordinates Entity Framework functionality for a given data model.

Create a DbContext Class:

• In your project, create a new class called AppDbContext.cs.

• Inherit from DbContext and configure it to include your entity classes (models).

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<Student> Students { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Your_Connection_String_Here");
    }
}

Defining Models:

Models in EF Core are simple C# classes that represent your database tables.

Create an Entity Class:

• In your project, create a new class called Student.cs.

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Basic CRUD Operations

CRUD stands for Create, Read, Update, and Delete. Below are the four basic operations you can perform on a database:

Creating Records:

Adding new entries to your database.

var newStudent = new Student { Name = "Emi James", Age = 20 };
dbContext.Students.Add(newStudent);
dbContext.SaveChanges();

Reading Records:

Retrieving data from the database.

var students = dbContext.Students.Where(s => s.Name == "Emi James").ToList();

Updating Records:

Modifying existing data.

var student = dbContext.Students.First(s => s.Name == "Emi James");
student.Age = 21;
dbContext.SaveChanges();

Deleting Records:

Removing data from the database.

var student = dbContext.Students.First(s => s.Name == "Emi James");
dbContext.Students.Remove(student);
dbContext.SaveChanges();

Advanced Querying and Performance Tuning

LINQ Queries:

LINQ (Language Integrated Query) allows you to write queries directly in C# to interact with your database. Here is a detailed LINQ learning resource

Eager Loading, Lazy Loading, and Explicit Loading:

• Eager Loading:
  Fetch related data along with your main data in a single query to improve performance.

• Lazy Loading:
  Load related data only when you access it, reducing the initial query size but potentially increasing the total number of queries.

• Explicit Loading:
  Manually load related data when needed, giving you control over what data is loaded and when.

Asynchronous Operations:

Use async and await keywords to make your database queries asynchronous, improving the responsiveness of your application.

Optimizing Performance:

• Query Optimization:
  Tips on writing efficient queries that minimize database load and improve speed.

• Indexing:
  Use indexes to speed up data retrieval.

• Caching:
  Temporarily store frequently accessed data in memory to reduce database calls.

Migrations and Updates:

• What are Migrations?
  Migrations help you manage changes to your database schema over time.

• How to Use Migrations:
  Learn to create, apply, and revert migrations to keep your database schema in sync with your application code.

Common Pitfalls

• N+1 Query Problem:
  Fetching related data in a way that results in multiple unnecessary queries.

• Improper Indexing:
  Not using indexes properly can lead to slow query performance.

• Ignoring Asynchronous Programming:
  Not using async/await can lead to unresponsive applications.

Resources for Further Learning

• Entity Framework Core Documentation: Microsoft Docs

• LINQ Tutorial: LINQ Documentation

• Asynchronous Programming with async and await: Async/Await Documentation

By understanding and applying these concepts, you will be able to write efficient and effective queries, manage your database schema changes, and optimize the performance of your Entity Framework Core applications.

I hope you found this guide helpful. Stay tuned for the next article in the "Mastering C#" series: "Dependency Injection in C#: A Deep Dive"

Happy coding!