CRUD Operations with EF Core and SQLite in ASP.NET Core
A Beginner-Friendly Guide to Building APIs with ASP.NET Core
Building database-driven applications is one of the most common tasks in modern software development. Thankfully, .NET developers have a powerful ORM called Entity Framework Core (EF Core) that makes database operations clean, simple, and efficient.
In this article, we’ll build a complete CRUD API using:
ASP.NET Core Web API
Entity Framework Core
SQLite Database
By the end, you’ll know how to:
Configure EF Core
Connect SQLite
Create models
Run migrations
Perform CRUD operations
Let’s get started.
What is EF Core?
Entity Framework Core (EF Core) is Microsoft’s modern Object-Relational Mapper (ORM) for .NET.
Instead of writing raw SQL queries, EF Core allows developers to work with databases using C# classes and LINQ queries.
EF Core supports multiple databases including:
SQL Server
SQLite
PostgreSQL
MySQL
Oracle
In this tutorial, we’ll use SQLite because it’s lightweight and beginner-friendly.
What is CRUD?
CRUD represents the four basic database operations:
OperationDescriptionCreateInsert new dataReadFetch dataUpdateModify existing dataDeleteRemove data
Most applications rely heavily on these operations.
Why SQLite?
SQLite is a lightweight, file-based database.
Benefits include:
No database server required
Easy setup
Fast for development
Great for learning and prototyping
Portable single database file
SQLite works perfectly with EF Core.
Step 1: Create a New ASP.NET Core Web API
Open terminal and run:
dotnet new webapi -n EFCoreCrudDemo
cd EFCoreCrudDemo
This creates a new ASP.NET Core Web API project.
Step 2: Install EF Core SQLite Packages
Install the required NuGet packages.
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Tools
These packages provide:
EF Core support
SQLite provider
Migration tools
Step 3: Create the Product Model
Create a folder named Models.
Inside it, create Product.cs.
namespace EFCoreCrudDemo.Models;
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public decimal Price { get; set; }
public int Stock { get; set; }
}
This model represents the database table.
EF Core automatically maps this class to a table called Products.
Step 4: Create the DbContext
Create a folder named Data.
Inside it, create AppDbContext.cs.
using Microsoft.EntityFrameworkCore;
using EFCoreCrudDemo.Models;
namespace EFCoreCrudDemo.Data;
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options)
: base(options)
{
}
public DbSet<Product> Products => Set<Product>();
}
What is DbContext?
DbContext is the bridge between your application and the database.
It manages:
Database connections
Queries
Entity tracking
Save operations
Step 5: Configure SQLite Connection
Open appsettings.json.
Add the connection string.
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=products.db"
}
}
SQLite stores all data inside a single .db file.
Step 6: Register DbContext in Program.cs
Open Program.cs.
Add these namespaces:
using EFCoreCrudDemo.Data;
using Microsoft.EntityFrameworkCore;
Now register the DbContext.
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlite(
builder.Configuration.GetConnectionString("DefaultConnection")));
This tells ASP.NET Core to use SQLite with EF Core.
Step 7: Create Migration
Run the following commands.
dotnet ef migrations add InitialCreate
Now update the database.
dotnet ef database update
EF Core will:
Create the SQLite database
Generate the Products table
Apply migration changes
You’ll see a file named:
products.db
That is your SQLite database.
Step 8: Create Products Controller
Create a folder named Controllers.
Inside it, create ProductsController.cs.
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using EFCoreCrudDemo.Data;
using EFCoreCrudDemo.Models;
namespace EFCoreCrudDemo.Controllers;
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly AppDbContext _context;
public ProductsController(AppDbContext context)
{
_context = context;
}
// CREATE
[HttpPost]
public async Task<IActionResult> Create(Product product)
{
_context.Products.Add(product);
await _context.SaveChangesAsync();
return Ok(product);
}
// READ ALL
[HttpGet]
public async Task<IActionResult> GetAll()
{
var products = await _context.Products.ToListAsync();
return Ok(products);
}
// READ BY ID
[HttpGet("{id}")]
public async Task<IActionResult> GetById(int id)
{
var product = await _context.Products.FindAsync(id);
if (product == null)
return NotFound();
return Ok(product);
}
// UPDATE
[HttpPut("{id}")]
public async Task<IActionResult> Update(int id, Product updatedProduct)
{
var product = await _context.Products.FindAsync(id);
if (product == null)
return NotFound();
product.Name = updatedProduct.Name;
product.Price = updatedProduct.Price;
product.Stock = updatedProduct.Stock;
await _context.SaveChangesAsync();
return Ok(product);
}
// DELETE
[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{
var product = await _context.Products.FindAsync(id);
if (product == null)
return NotFound();
_context.Products.Remove(product);
await _context.SaveChangesAsync();
return Ok("Product deleted successfully");
}
}
Understanding CRUD Operations
1. CREATE Operation
The POST endpoint inserts a new product.
POST /api/products
Sample JSON:
{
"name": "Laptop",
"price": 75000,
"stock": 10
}
EF Core tracks the entity and inserts it into SQLite.
2. READ Operation
Fetch all products.
GET /api/products
Fetch a single product.
GET /api/products/1
EF Core converts LINQ queries into SQL automatically.
3. UPDATE Operation
Update existing data.
PUT /api/products/1
Sample JSON:
{
"name": "Gaming Laptop",
"price": 90000,
"stock": 5
}
EF Core tracks changes and updates the database.
4. DELETE Operation
Delete a product.
DELETE /api/products/1
The selected record is removed from SQLite.
Step 9: Run the Application
Start the application.
dotnet run
Open Swagger UI:
https://localhost:xxxx/swagger
You can now test all CRUD endpoints visually.
How EF Core Works Internally
EF Core provides:
Change Tracking
EF Core automatically tracks entity changes.
Example:
product.Price = 5000;
EF Core detects the modification and generates the correct SQL UPDATE statement.
LINQ Support
You can query data using LINQ.
var expensiveProducts = await _context.Products
.Where(p => p.Price > 5000)
.ToListAsync();
EF Core translates this into SQL automatically.
Benefits of EF Core
Here are some major advantages:
Less SQL code
Strongly typed queries
Faster development
Migration support
Cross-platform compatibility
Easy database switching
Clean architecture support
Common Beginner Mistakes
Forgetting SaveChangesAsync()
Without it, changes won’t persist.
await _context.SaveChangesAsync();
Not Running Migrations
After model changes, always create migrations.
dotnet ef migrations add UpdateProduct
dotnet ef database update
Using Synchronous Methods
Prefer async methods for better scalability.
Good:
await _context.Products.ToListAsync();
Avoid:
_context.Products.ToList();
Best Practices
For production applications:
Use DTOs
Add validation
Implement Repository Pattern
Use dependency injection
Handle exceptions properly
Add logging
Use pagination for large datasets
Final Thoughts
Entity Framework Core makes database development much easier in modern .NET applications.
Using EF Core with SQLite is an excellent way to learn:
Database fundamentals
ASP.NET Core APIs
ORM concepts
Real-world CRUD operations
Once you understand the basics, you can move to advanced topics like:
Relationships
Fluent API
Authentication
Repository Pattern
CQRS
Performance optimization
EF Core is one of the most valuable skills for .NET developers today.
Happy Coding!
👉 Full working code available at:
🔗https://sourcecode.kanaiyakatarmal.com/CRUDOperationswithEFCore
I hope you found this guide helpful and informative.
Thanks for reading!
If you enjoyed this article, feel free to share it and follow me for more practical, developer-friendly content like this.