Using Entity Framework Core with SQL Server for Robust Data Access

To begin, you will need to set up your development environment. Ensure you have the following prerequisites:

1. .NET SDK: Install the .NET SDK from Microsoft's official site.
2. SQL Server: You can use SQL Server Express or any other edition. Download it from Microsoft's SQL Server page.
3. Visual Studio or Visual Studio Code: Either IDE will work, but ensure you have the C# extension installed if using VS Code.

Step 1: Create a New Project

Start by creating a new console application:

dotnet new console -n EFCoreExample
cd EFCoreExample

Step 2: Add Entity Framework Core Packages

Next, add the necessary NuGet packages for EF Core and SQL Server. Run the following commands:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

Step 3: Define Your Data Model

Create a new class file named Product.cs in the project directory. This class will represent the data model:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Step 4: Create the Database Context

Next, create a new class named AppDbContext.cs that inherits from DbContext. This class will manage the connection to the database and define the DbSet for your model:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=YOUR_SERVER;Database=EFCoreExampleDb;Trusted_Connection=True;");
    }
}

Replace YOUR_SERVER with your SQL Server instance name.

Step 5: Create and Apply Migrations

Migrations are a way to keep your database schema in sync with your model. To create a migration, run the following command:

dotnet ef migrations add InitialCreate

This command generates a migration file in the Migrations folder. To apply the migration and create the database, run:

dotnet ef database update

Step 6: Perform CRUD Operations

Now that your database is set up, you can perform Create, Read, Update, and Delete (CRUD) operations. Update the Program.cs file with the following code:

using System;
using System.Linq;

class Program
{
    static void Main(string[] args)
    {
        using (var context = new AppDbContext())
        {
            // Create
            var product = new Product { Name = "Sample Product", Price = 9.99M };
            context.Products.Add(product);
            context.SaveChanges();

            // Read
            var products = context.Products.ToList();
            Console.WriteLine("Products in database:");
            foreach (var p in products)
            {
                Console.WriteLine($"Id: {p.Id}, Name: {p.Name}, Price: {p.Price}");
            }

            // Update
            product.Price = 19.99M;
            context.Products.Update(product);
            context.SaveChanges();

            // Delete
            context.Products.Remove(product);
            context.SaveChanges();
        }
    }
}

Step 7: Best Practices

When working with EF Core and SQL Server, consider the following best practices:

Conclusion

Entity Framework Core provides a powerful way to interact with SQL Server databases in .NET applications. By following the steps outlined in this tutorial and adhering to best practices, you can create robust data access layers that enhance your application's performance and maintainability.

Learn more with useful resources

• Entity Framework Core Documentation
• Getting Started with EF Core
• Best Practices for EF Core

This guide should equip you with the foundational knowledge to effectively use Entity Framework Core with SQL Server. Happy coding!