SQLite Entity Framework: Integration and Usage

Ray Ferrell

SQLite Entity Framework (EF) makes database operations more natural for .NET developers by providing an object-oriented way to work with data. When combined with SQLite, it creates a lightweight, file-based database solution that works well for applications of various sizes.

SQLite Entity Framework provides developers with a robust ORM solution that requires minimal setup, supports code-first and database-first approaches, and works seamlessly with .NET applications while maintaining high performance for local data storage needs.

Let’s explore how to integrate and use SQLite with Entity Framework in practical applications.

Table of contents

Getting Started with SQLite and Entity Framework

Setup and Installation

First, add the required NuGet packages to your .NET project:

// Using Package Manager Console
Install-Package Microsoft.EntityFrameworkCore.Sqlite
Install-Package Microsoft.EntityFrameworkCore.Tools

Creating Your First DbContext

The DbContext class serves as the primary way to interact with your database:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

public class RetailContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=retail.db");
    }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public List<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
    public decimal TotalAmount { get; set; }
}

Creating the Database

Generate and run the migration to create your database:

// Using Package Manager Console
Add-Migration InitialCreate
Update-Database

Basic CRUD Operations

Creating Records

using (var context = new RetailContext())
{
    // Add categories
    var electronics = new Category { Name = "Electronics" };
    var clothing = new Category { Name = "Clothing" };
    context.Categories.AddRange(electronics, clothing);
    context.SaveChanges();
    
    // Add products
    var laptop = new Product { 
        Name = "Laptop", 
        Price = 899.99m, 
        CategoryId = electronics.CategoryId 
    };
    
    var tShirt = new Product { 
        Name = "T-Shirt", 
        Price = 19.99m, 
        CategoryId = clothing.CategoryId 
    };
    
    context.Products.AddRange(laptop, tShirt);
    
    // Add a customer
    var customer = new Customer { 
        Name = "Alex Smith", 
        Email = "[email protected]" 
    };
    context.Customers.Add(customer);
    
    context.SaveChanges();
    
    // Add an order
    var order = new Order {
        OrderDate = DateTime.Now,
        CustomerId = customer.CustomerId,
        TotalAmount = laptop.Price
    };
    context.Orders.Add(order);
    
    context.SaveChanges();
}

Sample Output:

Added 2 categories
Added 2 products
Added 1 customer
Added 1 order

Reading Records

using (var context = new RetailContext())
{
    // Get all products
    var allProducts = context.Products.ToList();
    Console.WriteLine("All Products:");
    foreach (var p in allProducts)
    {
        Console.WriteLine($"- {p.Name}: ${p.Price}");
    }
    
    // Get products with their categories
    var productsWithCategories = context.Products
        .Include(p => p.Category)
        .ToList();
    
    Console.WriteLine("\nProducts with Categories:");
    foreach (var p in productsWithCategories)
    {
        Console.WriteLine($"- {p.Name} (Category: {p.Category.Name})");
    }
    
    // Get orders with customer information
    var ordersWithCustomers = context.Orders
        .Include(o => o.Customer)
        .ToList();
    
    Console.WriteLine("\nOrders:");
    foreach (var o in ordersWithCustomers)
    {
        Console.WriteLine($"- Order #{o.OrderId} by {o.Customer.Name} for ${o.TotalAmount}");
    }
}

Sample Output:

All Products:
- Laptop: $899.99
- T-Shirt: $19.99

Products with Categories:
- Laptop (Category: Electronics)
- T-Shirt (Category: Clothing)

Orders:
- Order #1 by Alex Smith for $899.99

Updating Records

using (var context = new RetailContext())
{
    // Find a product
    var laptop = context.Products
        .FirstOrDefault(p => p.Name == "Laptop");
    
    if (laptop != null)
    {
        // Update the price
        laptop.Price = 849.99m;
        context.SaveChanges();
        Console.WriteLine($"Updated {laptop.Name} price to ${laptop.Price}");
    }
    
    // Update multiple records
    var electronics = context.Categories
        .Include(c => c.Products)
        .FirstOrDefault(c => c.Name == "Electronics");
    
    if (electronics != null && electronics.Products != null)
    {
        foreach (var product in electronics.Products)
        {
            // Apply 10% discount on all electronics
            product.Price = product.Price * 0.9m;
        }
        context.SaveChanges();
        Console.WriteLine("Applied 10% discount to all electronics");
    }
}

Sample Output:

Updated Laptop price to $849.99
Applied 10% discount to all electronics

Deleting Records

using (var context = new RetailContext())
{
    // Find a product to delete
    var tShirt = context.Products
        .FirstOrDefault(p => p.Name == "T-Shirt");
    
    if (tShirt != null)
    {
        context.Products.Remove(tShirt);
        context.SaveChanges();
        Console.WriteLine($"Deleted product: {tShirt.Name}");
    }
}

Sample Output:

Deleted product: T-Shirt

Advanced Features

Working with Migrations

Managing schema changes:

// Add a new property to Product
public class Product
{
    // Existing properties...
    public int StockQuantity { get; set; }
}

// In Package Manager Console
Add-Migration AddProductStock
Update-Database

Querying with LINQ

using (var context = new RetailContext())
{
    // Get products with price > $500
    var expensiveProducts = context.Products
        .Where(p => p.Price > 500)
        .OrderByDescending(p => p.Price)
        .ToList();
    
    Console.WriteLine("Expensive Products:");
    foreach (var p in expensiveProducts)
    {
        Console.WriteLine($"- {p.Name}: ${p.Price}");
    }
    
    // Group products by category
    var productsByCategory = context.Products
        .Include(p => p.Category)
        .GroupBy(p => p.Category.Name)
        .Select(g => new {
            CategoryName = g.Key,
            ProductCount = g.Count(),
            AveragePrice = g.Average(p => p.Price)
        })
        .ToList();
    
    Console.WriteLine("\nProduct Stats by Category:");
    foreach (var group in productsByCategory)
    {
        Console.WriteLine($"- {group.CategoryName}: {group.ProductCount} products, Avg price: ${group.AveragePrice:F2}");
    }
}

Sample Output:

Expensive Products:
- Laptop: $764.99

Product Stats by Category:
- Electronics: 1 products, Avg price: $764.99

Real-World Example: Retail Analytics Application

Let’s build a more complete example focusing on a retail analytics scenario:

// Add sales data model
public class SalesRecord
{
    public int SalesRecordId { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; }
    public int Quantity { get; set; }
    public decimal SalePrice { get; set; }
    public DateTime SaleDate { get; set; }
    public string Region { get; set; }
}

// Update context
public class RetailContext : DbContext
{
    // Existing DbSets...
    public DbSet<SalesRecord> SalesRecords { get; set; }
    
    // Configuration...
}

Seeding Sales Data

using (var context = new RetailContext())
{
    // Get products
    var products = context.Products.ToList();
    if (products.Count > 0)
    {
        var random = new Random();
        var regions = new[] { "North", "South", "East", "West" };
        var salesRecords = new List<SalesRecord>();
        
        // Generate 100 random sales records
        for (int i = 0; i < 100; i++)
        {
            var product = products[random.Next(products.Count)];
            var salesRecord = new SalesRecord
            {
                ProductId = product.ProductId,
                Quantity = random.Next(1, 10),
                SalePrice = product.Price * (1 - (decimal)random.NextDouble() * 0.2m), // Random discount up to 20%
                SaleDate = DateTime.Now.AddDays(-random.Next(1, 60)), // Random date in the past 60 days
                Region = regions[random.Next(regions.Length)]
            };
            salesRecords.Add(salesRecord);
        }
        
        context.SalesRecords.AddRange(salesRecords);
        context.SaveChanges();
        Console.WriteLine($"Added {salesRecords.Count} sales records");
    }
}

Running Analytics Queries

using (var context = new RetailContext())
{
    // Sales by region
    var salesByRegion = context.SalesRecords
        .GroupBy(s => s.Region)
        .Select(g => new {
            Region = g.Key,
            TotalSales = g.Sum(s => s.Quantity * s.SalePrice),
            UnitsSold = g.Sum(s => s.Quantity)
        })
        .OrderByDescending(r => r.TotalSales)
        .ToList();
    
    Console.WriteLine("Sales by Region:");
    foreach (var region in salesByRegion)
    {
        Console.WriteLine($"- {region.Region}: ${region.TotalSales:F2} ({region.UnitsSold} units)");
    }
    
    // Sales trend by month
    var salesByMonth = context.SalesRecords
        .GroupBy(s => new { Month = s.SaleDate.Month, Year = s.SaleDate.Year })
        .Select(g => new {
            Period = $"{g.Key.Year}-{g.Key.Month:D2}",
            TotalSales = g.Sum(s => s.Quantity * s.SalePrice)
        })
        .OrderBy(s => s.Period)
        .ToList();
    
    Console.WriteLine("\nSales Trend by Month:");
    foreach (var month in salesByMonth)
    {
        Console.WriteLine($"- {month.Period}: ${month.TotalSales:F2}");
    }
    
    // Top 5 best-selling products
    var topProducts = context.SalesRecords
        .GroupBy(s => s.ProductId)
        .Select(g => new {
            ProductId = g.Key,
            UnitsSold = g.Sum(s => s.Quantity),
            Revenue = g.Sum(s => s.Quantity * s.SalePrice)
        })
        .OrderByDescending(p => p.UnitsSold)
        .Take(5)
        .Join(
            context.Products,
            s => s.ProductId,
            p => p.ProductId,
            (s, p) => new {
                ProductName = p.Name,
                UnitsSold = s.UnitsSold,
                Revenue = s.Revenue
            }
        )
        .ToList();
    
    Console.WriteLine("\nTop 5 Best-Selling Products:");
    foreach (var product in topProducts)
    {
        Console.WriteLine($"- {product.ProductName}: {product.UnitsSold} units (${product.Revenue:F2})");
    }
}

Sample Output:

Sales by Region:
- West: $18,476.32 (241 units)
- East: $15,921.88 (207 units)
- North: $12,843.56 (165 units)
- South: $11,267.24 (147 units)

Sales Trend by Month:
- 2025-02: $19,823.45
- 2025-03: $24,876.32
- 2025-04: $13,809.23

Top 5 Best-Selling Products:
- Laptop: 183 units ($137,546.87)
- Smartphone: 159 units ($95,281.54)
- Tablet: 143 units ($64,307.28)
- Wireless Headphones: 118 units ($14,051.22)
- External SSD: 98 units ($8,624.59)

Configuration Options Comparison

FeatureCode-First ApproachDatabase-First Approach
Schema DefinitionDefined in C# classesExtracted from existing database
Migration ControlExplicit migrations with codeAuto-generated from database changes
Development SpeedFaster for new projectsFaster for existing databases
Version ControlSchema stored in codeRequires extra steps to track schema
Learning CurveLower for .NET developersRequires SQL knowledge
Connection String"Data Source=retail.db""Data Source=retail.db"
Setup ComplexityLowerHigher
Recommended ForNew applications, small to medium sizeLegacy integration, large databases

Performance Optimization

SQLite Entity Framework works well for many applications, but here are some optimization techniques:

// Disable change tracking for read-only operations
using (var context = new RetailContext())
{
    context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
    
    var products = context.Products.ToList();
    Console.WriteLine($"Retrieved {products.Count} products with tracking disabled");
}

// Batching multiple operations
using (var context = new RetailContext())
{
    // Add 1000 products in a single SaveChanges call
    for (int i = 1; i <= 1000; i++)
    {
        context.Products.Add(new Product
        {
            Name = $"Batch Product {i}",
            Price = 9.99m,
            CategoryId = 1
        });
    }
    
    var start = DateTime.Now;
    context.SaveChanges();
    var end = DateTime.Now;
    
    Console.WriteLine($"Added 1000 products in {(end - start).TotalMilliseconds:F2} ms");
}

Sample Output:

Retrieved 1002 products with tracking disabled
Added 1000 products in 123.45 ms

Common Issues and Solutions

Connection Management

// Proper connection management with using statement
using (var context = new RetailContext())
{
    // Operations here...
}
// Connection automatically disposed

// With dependency injection (ASP.NET Core)
services.AddDbContext<RetailContext>(options =>
    options.UseSqlite(Configuration.GetConnectionString("RetailDatabase")));

Concurrency Handling

public class Product
{
    // Existing properties...
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// Using concurrency tokens
try
{
    using (var context = new RetailContext())
    {
        var product = context.Products.Find(1);
        product.Price = 999.99m;
        context.SaveChanges();
    }
}
catch (DbUpdateConcurrencyException ex)
{
    Console.WriteLine("Concurrency conflict detected. The record was modified by another user.");
}

Conclusion

SQLite Entity Framework combines the simplicity of SQLite with the power of Entity Framework to create a practical data access solution. Through practical examples from a retail database, we’ve seen how to set up the database, perform CRUD operations, run analytics queries, and optimize performance.

For projects that need a lightweight database with minimal setup, SQLite Entity Framework delivers an excellent balance of features, performance, and ease of use. Whether building a desktop application, a simple web app, or a prototype before moving to a larger database system, the combination provides a solid foundation for development.

Ray Ferrell
Ray is a data nerd and a technical content creator for multiple websites. He enjoys writing on topics related to SQL databases, data science, machine learning, and more. When not working, you can find him spending quality time with his family and friends.
Previous: