How to C#: Building a .NET 9 API with SQL Server and Entity Framework Core

In this tutorial, we will create a new project with a database using Entity Framework Core in .NET 9. For this example, we will build a simple blog API using SQL Server. Entity Framework Core is a powerful ORM that allows you to create and manage databases using C#.

There are different packages available depending on the database provider you need. For this tutorial, we will use SQL Server, which requires the package Microsoft.EntityFrameworkCore.SqlServer.

Prerequisites

• Visual Studio 2022 (latest update)
• .NET version 9
• NuGet: Microsoft.EntityFrameworkCore.SqlServer
• NuGet: Microsoft.EntityFrameworkCore.Tools

Creating a New Project

1. Open Visual Studio and create a new solution.
2. Add a new project using the ASP.NET Core Web API template.
3. Name the project EntityFrameworkCoreDemo.

Adding the Required Packages

To add Entity Framework Core, follow these steps:

1. Right-click on your project and select Manage NuGet Packages.
2. Navigate to the Browse tab and search for Microsoft.EntityFrameworkCore.SqlServer. Select the latest stable version and install it.
3. Install Microsoft.EntityFrameworkCore.Tools similarly.

Creating the Database

Defining the Model

1. Create a new folder called Models.
2. Inside the Models folder, create a new file called Post.cs and define the following model:

using System;
using System.ComponentModel.DataAnnotations;

public class Post
{
    [Key]
    public Guid Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

Creating the Database Context

1. Create another file named BloggingContext.cs inside the Models folder:

using Microsoft.EntityFrameworkCore;
using System;

public class BloggingContext : DbContext
{
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        var connectionString = Environment.GetEnvironmentVariable("BLOGGING_DB_CONNECTION")
            ?? throw new InvalidOperationException("Database connection string is not set.");
        options.UseSqlServer(connectionString);
    }
}

Applying Migrations

Before applying migrations, ensure error handling and rollback strategies are in place to maintain database integrity. Run the following commands in the Package Manager Console:

Add-Migration InitialCreate
Update-Database

If an error occurs, use Remove-Migration to undo changes before they are applied.
Before applying migrations, ensure error handling and rollback strategies are in place to maintain database integrity. Run the following commands in the Package Manager Console:

Add-Migration InitialCreate
Update-Database

If an error occurs, use Remove-Migration to undo changes before they are applied. Model

1. Create a new folder called Models.
2. Inside the Models folder, create a new file called Post.cs and define the following model:

using System;
using System.ComponentModel.DataAnnotations;

public class Post
{
    [Key]
    public Guid Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

1. Create another file named BloggingContext.cs inside the Models folder:

using Microsoft.EntityFrameworkCore;
using System;

public class BloggingContext : DbContext
{
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        var connectionString = Environment.GetEnvironmentVariable("BLOGGING_DB_CONNECTION")
            ?? throw new InvalidOperationException("Database connection string is not set.");
        options.UseSqlServer(connectionString);
    }
}

Configuring the Connection String

Instead of hardcoding the connection string, we store it as an environment variable. For production environments, consider using Azure Key Vault, AWS Secrets Manager, or a similar secrets management service.

1. Set the environment variable in your development environment:

   $env:BLOGGING_DB_CONNECTION = "Server=localhost;Database=BloggingDB;User Id=sa;Password=yourpassword;TrustServerCertificate=True;"

1. In production, set the environment variable securely using the hosting server’s configuration system.
   Instead of hardcoding the connection string, we store it as an environment variable. For production environments, consider using Azure Key Vault, AWS Secrets Manager, or a similar secrets management service.

2. Set the environment variable in your development environment:

   $env:BLOGGING_DB_CONNECTION = "Server=localhost;Database=BloggingDB;User Id=sa;Password=yourpassword;TrustServerCertificate=True;"

1. In production, set the environment variable securely using the hosting server’s configuration system.
   Instead of hardcoding the connection string, we store it as an environment variable.
2. Set the environment variable in your development environment by using the environment variables or using a .env file:

   $env:BLOGGING_DB_CONNECTION = "Server=localhost;Database=BloggingDB;User Id=sa;Password=yourpassword;TrustServerCertificate=True;"

Or create a .env file in the root of your project.

!!! MAKE SURE TO INCLUDE THE .ENV INTO YOUR .GITIGNORE. NEVER PUSH THIS TO SOURCE CONTROL/GIT. !!!

1. In production, set the environment variable on the hosting server.

Creating the Database

Run the following commands in the Package Manager Console:

Add-Migration InitialCreate
Update-Database

Configuring the Application

Open Program.cs and modify the ConfigureServices section to register BloggingContext. Additionally, configure logging and monitoring for better observability.

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<BloggingContext>();
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Configure logging
builder.Logging.ClearProviders();
builder.Logging.AddConsole();

var app = builder.Build();

app.UseSwagger();
app.UseSwaggerUI();
app.UseAuthorization();
app.MapControllers();
app.Run();

Open Program.cs and modify the ConfigureServices section to register BloggingContext:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<BloggingContext>();
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

app.UseSwagger();
app.UseSwaggerUI();
app.UseAuthorization();
app.MapControllers();
app.Run();

Creating a Controller

1. Right-click the Controllers folder and select Add -> Controller.
2. Choose API Controller with actions, using Entity Framework.
3. Select Post as the model and BloggingContext as the data context.
4. Name the controller PostController.cs.

Running the Application

1. Start the application by pressing F5.
2. Open the browser and navigate to https://localhost:/swagger.
3. Test your API using Swagger.

Conclusion

You have successfully created a blog API using Entity Framework Core with .NET 9 and SQL Server. The database connection is now managed via an environment variable, making it more secure and flexible for different environments.

To optimize performance in production, consider:

• Enabling caching mechanisms to reduce database load.
• Optimizing SQL queries and indexing frequently accessed columns.
• Implementing monitoring tools like Application Insights or Prometheus.
• Using connection pooling to manage multiple database requests efficiently.
• Handling database migrations carefully to prevent downtime during deployments.
  You have successfully created a blog API using Entity Framework Core with .NET 9 and SQL Server. The database connection is now managed via an environment variable, making it more secure and flexible for different environments.

To optimize performance in production, consider:

• Enabling caching mechanisms to reduce database load.
• Optimizing SQL queries and indexing frequently accessed columns.
• Implementing monitoring tools like Application Insights or Prometheus.

– Using connection pooling to manage multiple database requests efficiently.

You have successfully created a blog API using Entity Framework Core with .NET 9 and SQL Server. The database connection is now managed via an environment variable, making it more secure and flexible for different environments.