Setting Up SQL Server and Entity Framework Core Locally (Guide)

In modern web development, SQL Server and Entity Framework Core (EF Core) are widely used for building robust, scalable, and efficient applications. EF Core is an Object-Relational Mapper (ORM) that simplifies database interactions by allowing developers to work with database objects using C# code instead of raw SQL queries.

This guide provides a step-by-step approach to setting up SQL Server, connecting it with an ASP.NET Core project, and using Entity Framework Core to manage database interactions.

---

1. Install SQL Server and SQL Server Management Studio (SSMS)

Before using SQL Server with .NET Core, you need to install the following tools:

• SQL Server – A relational database management system for storing and managing data.
• SQL Server Management Studio (SSMS) – A graphical tool for managing SQL Server databases.

Step 1: Download and Install SQL Server

1. Go to the official Microsoft SQL Server Download Page:
   SQL Server Download
2. Choose the Developer Edition (free for development and testing).
3. Run the installer and choose "Basic" installation for a quick setup.
4. Follow the on-screen instructions to complete the installation.
5. Once installed, configure authentication mode (Windows Authentication is recommended).

Step 2: Download and Install SSMS

1. Go to the SSMS download page:
   SSMS Download
2. Download and install SSMS by running the installer.
3. Launch SSMS and connect to the local SQL Server instance using Windows Authentication.

---

2. Create a New Database and Connect with .NET Core

Step 1: Create a New Database Using SSMS

1. Open SSMS and connect to your SQL Server instance.
2. Right-click on "Databases" → Click "New Database".
3. Enter a database name (e.g., MyAppDb) and click "OK".

Step 2: Set Up Connection String in ASP.NET Core

1. Open your .NET Core project in Visual Studio or VS Code.

2. Navigate to appsettings.json and add the connection string:

   {
     "ConnectionStrings": {
       "DefaultConnection": "Server=.;Database=MyAppDb;Trusted_Connection=True;MultipleActiveResultSets=true"
     }
   }
   

   • Server=. refers to the local SQL Server instance.
   • Database=MyAppDb specifies the database name.
   • Trusted_Connection=True uses Windows Authentication.

3. Open the Program.cs file and modify the ConfigureServices method to register the database context:

   using Microsoft.EntityFrameworkCore;
   using Microsoft.Extensions.DependencyInjection;
   using MyApp.Data;
   
   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.AddDbContext<ApplicationDbContext>(options =>
       options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
   
   var app = builder.Build();
   app.Run();
   

---

3. Install Entity Framework Core and Run Migrations

Step 1: Install EF Core NuGet Packages

Run the following commands in the Terminal/Command Prompt inside your project directory:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

Step 2: Create a Database Context

Create a new DbContext class under a Data folder:

using Microsoft.EntityFrameworkCore;

namespace MyApp.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public DbSet<Product> Products { get; set; }
    }
}

Step 3: Create a Model (Entity Class)

Create a new Product.cs file inside the Models folder:

namespace MyApp.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

Step 4: Add and Apply Migrations

Run the following commands in the terminal:

dotnet ef migrations add InitialCreate
dotnet ef database update

• dotnet ef migrations add InitialCreate generates the migration script.
• dotnet ef database update applies the migration and creates the database table.

---

4. Perform CRUD Operations Using LINQ Queries

Step 1: Create a Repository Service for CRUD Operations

Create a new class ProductService.cs inside the Services folder:

using System.Collections.Generic;
using System.Linq;
using MyApp.Data;
using MyApp.Models;

namespace MyApp.Services
{
    public class ProductService
    {
        private readonly ApplicationDbContext _context;

        public ProductService(ApplicationDbContext context)
        {
            _context = context;
        }

        // Create
        public void AddProduct(Product product)
        {
            _context.Products.Add(product);
            _context.SaveChanges();
        }

        // Read
        public List<Product> GetAllProducts()
        {
            return _context.Products.ToList();
        }

        public Product GetProductById(int id)
        {
            return _context.Products.FirstOrDefault(p => p.Id == id);
        }

        // Update
        public void UpdateProduct(Product product)
        {
            _context.Products.Update(product);
            _context.SaveChanges();
        }

        // Delete
        public void DeleteProduct(int id)
        {
            var product = _context.Products.Find(id);
            if (product != null)
            {
                _context.Products.Remove(product);
                _context.SaveChanges();
            }
        }
    }
}

Step 2: Register ProductService in Program.cs

builder.Services.AddScoped<ProductService>();

Step 3: Create an API Controller

Create a new controller file ProductController.cs:

using Microsoft.AspNetCore.Mvc;
using MyApp.Models;
using MyApp.Services;

namespace MyApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductController : ControllerBase
    {
        private readonly ProductService _productService;

        public ProductController(ProductService productService)
        {
            _productService = productService;
        }

        [HttpGet]
        public IActionResult GetProducts()
        {
            return Ok(_productService.GetAllProducts());
        }

        [HttpGet("{id}")]
        public IActionResult GetProduct(int id)
        {
            var product = _productService.GetProductById(id);
            if (product == null) return NotFound();
            return Ok(product);
        }

        [HttpPost]
        public IActionResult AddProduct(Product product)
        {
            _productService.AddProduct(product);
            return CreatedAtAction(nameof(GetProduct), new { id = product.Id }, product);
        }

        [HttpPut("{id}")]
        public IActionResult UpdateProduct(int id, Product product)
        {
            if (id != product.Id) return BadRequest();
            _productService.UpdateProduct(product);
            return NoContent();
        }

        [HttpDelete("{id}")]
        public IActionResult DeleteProduct(int id)
        {
            _productService.DeleteProduct(id);
            return NoContent();
        }
    }
}

---

5. Run and Test API Using Postman or Swagger UI

Step 1: Enable Swagger in Program.cs

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

Modify the app pipeline to use Swagger:

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

Step 2: Run the API and Open Swagger

1. Start the application:

   dotnet run
   

2. Open Swagger UI in the browser at https://localhost:5001/swagger.

3. Use Postman or Swagger UI to test the API endpoints.

---

Conclusion

In this guide, we covered:
✅ Installing and setting up SQL Server & SSMS
✅ Creating a new database and connecting it with .NET Core
✅ Installing and configuring Entity Framework Core
✅ Running migrations and setting up the database context
✅ Performing CRUD operations using LINQ and Web API
✅ Testing APIs using Postman and Swagger UI

With this setup, you now have a solid foundation to build and scale .NET Core applications using SQL Server and EF Core. 🚀