Building an E-Commerce API with WitDatabase and Entity Framework Core

Entity Framework Core is the go-to ORM for .NET developers. It lets you think in objects instead of SQL, handles relationships automatically, and tracks changes so you don't have to. When combined with WitDatabase, you get all the power of EF Core with the simplicity of an embedded database.

In the previous post, we built a REST API using ADO.NET — writing SQL by hand, managing parameters, mapping results to objects. It works great when you need precise control. But for most applications, EF Core offers a faster path from idea to working code.

This sample takes it further. Instead of just Users and Products, we're building a proper e-commerce API with:

• Users — Customer accounts
• Products — Catalog items with prices and stock
• Orders — Purchase records with status tracking
• Order Items — Line items linking orders to products

The real power shows in relationships. An Order belongs to a User. An Order contains multiple OrderItems. Each OrderItem references a Product. EF Core handles all of this with navigation properties and LINQ queries — no JOIN statements to write.

Let's see how it comes together.

Prerequisites & Setup

You'll need the .NET 9.0 or 10.0 SDK installed. The sample runs on Windows, Linux, or macOS.

# Clone and run
git clone https://github.com/dmitrat/WitDatabase.git
cd WitDatabase/Samples/OutWit.Database.Samples.WebApiEF
dotnet run

Open http://localhost:5183 to access Swagger UI. You'll see three controllers: Users, Products, and Orders — each with full CRUD endpoints plus some extras like statistics and search.

The database file (webapi_ef_sample.witdb) is created automatically with seed data: a few users, some products, and sample orders to explore.

Project Architecture

The project follows a standard ASP.NET Core Web API structure:

[[Svg Src="./witdatabase-webapief-project-structure.svg" Alt="witdatabase-webapief-project-structure"]]

The key difference from the ADO.NET sample is the DbContext. Instead of writing SQL and mapping results manually, we define entity classes and let EF Core generate the queries. The service layer becomes thinner — mostly LINQ expressions and calls to SaveChangesAsync().

Entity Relationships

[[Svg Src="./witdatabase-entity-relationships.svg" Alt="witdatabase-entity-relationships"]]

A User can have many Orders. Each Order contains multiple OrderItems. Each OrderItem references one Product. This is a classic e-commerce schema, and EF Core makes it easy to navigate in both directions.

DbContext Configuration

Everything starts with the DbContext — the bridge between your C# classes and the database.

Registration in Program.cs

WitDatabase integrates with EF Core through the UseWitDb() extension method:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(connectionString));

That's it. One line. The connection string works just like the ADO.NET sample — you can specify the file path, encryption, cache settings, and more.

The DbContext Class

The AppDbContext defines which entities are tracked and how they map to tables:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options) { }

    public DbSet<User> Users => Set<User>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent configuration for each entity...
    }
}

Each DbSet<T> represents a table. When you query context.Users, EF Core generates the appropriate SELECT statement. When you call SaveChangesAsync(), it generates INSERT, UPDATE, or DELETE statements for any tracked changes.

Fluent Configuration

The OnModelCreating method lets you configure indexes, constraints, and relationships:

modelBuilder.Entity<User>(entity =>
{
    entity.HasKey(e => e.Id);
    entity.HasIndex(e => e.Email).IsUnique();
    entity.Property(e => e.Name).IsRequired().HasMaxLength(100);
});

This creates a unique index on Email and sets constraints on the Name column — all without writing CREATE TABLE statements.

Entity Models & Relationships

The domain model is where EF Core really shines. You define C# classes with navigation properties, and EF Core figures out the foreign keys and joins.

The Entities

User — a customer who can place orders:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime? CreatedAt { get; set; }
    
    // Navigation property — one user has many orders
    public List<Order> Orders { get; set; } = new();
}

Product — an item in the catalog:

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

Order — a purchase record:

public class Order
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public decimal TotalAmount { get; set; }
    public DateTime? OrderDate { get; set; }
    public OrderStatus Status { get; set; }
    
    // Navigation to parent
    public User User { get; set; }
    
    // Navigation to children
    public List<OrderItem> Items { get; set; } = new();
}

OrderItem — a line item in an order:

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    
    // Navigation properties
    public Order Order { get; set; }
    public Product Product { get; set; }
}

Configuring Relationships

In OnModelCreating, we define how entities relate:

modelBuilder.Entity<Order>(entity =>
{
    entity.HasOne(e => e.User)
          .WithMany(u => u.Orders)
          .HasForeignKey(e => e.UserId);
});

This tells EF Core: "An Order has one User (via UserId), and a User has many Orders (via the Orders collection)." The framework handles the rest — creating foreign key constraints, generating JOIN queries, and maintaining referential integrity.

The beauty is that you can navigate in both directions. From a User, you can access their Orders. From an Order, you can access the User. No manual JOINs required.

LINQ Queries

LINQ is the heart of EF Core. Instead of writing SQL strings, you compose queries using C# methods. The compiler checks your syntax, IntelliSense helps you discover options, and refactoring tools update queries when you rename properties.

Basic Queries

Fetching all products, sorted by name:

var products = await context.Products
    .OrderBy(p => p.Name)
    .ToListAsync();

Finding a single entity by primary key:

var user = await context.Users.FindAsync(userId);

Filtering and Sorting

The Where method filters results:

var expensiveProducts = await context.Products
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.Price)
    .ToListAsync();

You can chain multiple conditions:

var results = await context.Products
    .Where(p => p.Stock > 0)
    .Where(p => p.Name.Contains(searchTerm))
    .ToListAsync();

Pagination

For large datasets, use Skip and Take:

var page = await context.Products
    .OrderBy(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Eager Loading with Include

By default, navigation properties are not loaded. If you want to fetch an Order along with its User and Items, use Include:

var order = await context.Orders
    .Include(o => o.User)
    .Include(o => o.Items)
    .FirstOrDefaultAsync(o => o.Id == orderId);

For nested relationships (Items → Product), chain ThenInclude:

var order = await context.Orders
    .Include(o => o.User)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

This generates a single query with JOINs — efficient and automatic. Compare this to the ADO.NET approach where you'd write the JOIN yourself, execute it, and manually map the nested objects.

Projections

When you only need specific fields, use Select to create a projection:

var summary = await context.Orders
    .Where(o => o.Status == OrderStatus.Completed)
    .Select(o => new
    {
        o.Id,
        o.TotalAmount,
        CustomerName = o.User.Name,
        ItemCount = o.Items.Count
    })
    .ToListAsync();

EF Core generates a query that only fetches the columns you need — no wasted data transfer.

Service Layer with Change Tracking

The service layer in EF Core applications is remarkably simple compared to ADO.NET. Change tracking does most of the heavy lifting.

How Change Tracking Works

When you fetch an entity from the database, EF Core remembers its original values. If you modify properties, the framework detects the changes and generates the appropriate UPDATE statement when you call SaveChangesAsync().

// Fetch the product — EF Core starts tracking it
var product = await context.Products.FindAsync(productId);

// Modify properties — EF Core notices the changes
product.Price = newPrice;
product.Stock = newStock;

// Save — EF Core generates: UPDATE Products SET Price=@p0, Stock=@p1 WHERE Id=@id
await context.SaveChangesAsync();

No need to build UPDATE statements or track which fields changed. The framework handles it.

Creating Entities

Adding new entities is equally straightforward:

var product = new Product
{
    Name = name,
    Description = description,
    Price = price,
    Stock = stock
};

context.Products.Add(product);
await context.SaveChangesAsync();

// product.Id is now populated with the auto-generated value
return product;

Deleting Entities

var product = await context.Products.FindAsync(productId);
if (product == null)
    return false;

context.Products.Remove(product);
await context.SaveChangesAsync();
return true;

The Complete Service Method

Here's what a typical update method looks like in the ProductService:

public async Task<Product?> UpdateAsync(int id, string name, string? description, 
                                         decimal price, int stock)
{
    var product = await m_context.Products.FindAsync(id);
    if (product == null)
        return null;

    product.Name = name;
    product.Description = description;
    product.Price = price;
    product.Stock = stock;

    await m_context.SaveChangesAsync();
    return product;
}

Compare this to the ADO.NET version which required building a command, adding parameters, executing, and handling the RETURNING clause. The EF Core version is half the code and easier to read.

Orders API: Working with Relationships

The Orders controller demonstrates the real power of EF Core — handling complex relationships with minimal code.

Creating an Order with Items

When a customer places an order, we need to:

1. Validate the user exists
2. Validate each product exists and has sufficient stock
3. Calculate line item prices and total
4. Create the order with all items
5. Update product stock levels

public async Task<Order?> CreateOrderAsync(int userId, List<OrderItemRequest> items)
{
    // Verify user exists
    var user = await m_context.Users.FindAsync(userId);
    if (user == null)
        return null;

    var order = new Order
    {
        UserId = userId,
        OrderDate = DateTime.UtcNow,
        Status = OrderStatus.Pending,
        Items = new List<OrderItem>()
    };

    decimal totalAmount = 0;

    foreach (var item in items)
    {
        var product = await m_context.Products.FindAsync(item.ProductId);
        if (product == null || product.Stock < item.Quantity)
            continue;  // Skip invalid items

        // Create order item with current price
        order.Items.Add(new OrderItem
        {
            ProductId = product.Id,
            Quantity = item.Quantity,
            UnitPrice = product.Price
        });

        totalAmount += product.Price * item.Quantity;
        product.Stock -= item.Quantity;  // Update stock
    }

    order.TotalAmount = totalAmount;
    m_context.Orders.Add(order);
    await m_context.SaveChangesAsync();

    return order;
}

Notice how we:

• Modify product.Stock directly — change tracking handles the UPDATE
• Add items to order.Items — EF Core links them automatically via the foreign key
• Call SaveChangesAsync() once — all changes (order, items, stock updates) are committed together

Fetching an Order with Details

To display an order, we need the user info, all items, and the product details for each item:

public async Task<Order?> GetByIdWithDetailsAsync(int orderId)
{
    return await m_context.Orders
        .Include(o => o.User)
        .Include(o => o.Items)
            .ThenInclude(i => i.Product)
        .FirstOrDefaultAsync(o => o.Id == orderId);
}

One query fetches everything. The result is a fully populated object graph — no N+1 query problems.

Getting a User's Order History

public async Task<List<Order>> GetUserOrdersAsync(int userId)
{
    return await m_context.Orders
        .Where(o => o.UserId == userId)
        .OrderByDescending(o => o.OrderDate)
        .Include(o => o.Items)
        .ToListAsync();
}

Updating Order Status

public async Task<Order?> UpdateStatusAsync(int orderId, OrderStatus newStatus)
{
    var order = await m_context.Orders.FindAsync(orderId);
    if (order == null)
        return null;

    order.Status = newStatus;
    await m_context.SaveChangesAsync();
    return order;
}

The pattern is always the same: fetch, modify, save. EF Core tracks what changed and generates minimal SQL.

Statistics & Aggregations

LINQ makes aggregation queries readable and type-safe. No string concatenation, no magic column names.

Product Statistics

public async Task<ProductStatistics> GetStatisticsAsync()
{
    return new ProductStatistics
    {
        TotalProducts = await m_context.Products.CountAsync(),
        TotalStock = await m_context.Products.SumAsync(p => p.Stock),
        AveragePrice = await m_context.Products.AverageAsync(p => p.Price),
        MinPrice = await m_context.Products.MinAsync(p => p.Price),
        MaxPrice = await m_context.Products.MaxAsync(p => p.Price)
    };
}

Order Statistics with GroupBy

Counting orders by status:

var byStatus = await m_context.Orders
    .GroupBy(o => o.Status)
    .Select(g => new 
    { 
        Status = g.Key, 
        Count = g.Count(),
        TotalRevenue = g.Sum(o => o.TotalAmount)
    })
    .ToListAsync();

Finding Low Stock Products

public async Task<List<Product>> GetLowStockAsync(int threshold = 10)
{
    return await m_context.Products
        .Where(p => p.Stock < threshold)
        .OrderBy(p => p.Stock)
        .ToListAsync();
}

Top Customers by Order Value

var topCustomers = await m_context.Users
    .Select(u => new
    {
        u.Id,
        u.Name,
        TotalSpent = u.Orders.Sum(o => o.TotalAmount),
        OrderCount = u.Orders.Count
    })
    .OrderByDescending(x => x.TotalSpent)
    .Take(10)
    .ToListAsync();

Notice how we navigate through the Orders collection directly in the LINQ query. EF Core translates this to SQL with JOINs and GROUP BY — you never see the complexity.

ADO.NET vs EF Core: Side-by-Side

Let's compare the same operation — updating a product — in both approaches.

ADO.NET (from WebApi sample)

public async Task<Product?> UpdateAsync(long id, string name, string? description,
                                         decimal price, int stock)
{
    using var command = _connection.CreateCommand();
    command.CommandText = """
        UPDATE Products 
        SET Name = @name, Description = @desc, Price = @price, Stock = @stock
        WHERE Id = @id
        RETURNING Id, Name, Description, Price, Stock, CreatedAt
        """;
    command.Parameters.Add(new WitDbParameter("@id", id));
    command.Parameters.Add(new WitDbParameter("@name", name));
    command.Parameters.Add(new WitDbParameter("@desc", description ?? ""));
    command.Parameters.Add(new WitDbParameter("@price", price));
    command.Parameters.Add(new WitDbParameter("@stock", stock));

    using var reader = await command.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
        return new Product
        {
            Id = reader.GetInt64(0),
            Name = reader.GetString(1),
            Description = reader.IsDBNull(2) ? null : reader.GetString(2),
            Price = reader.GetDecimal(3),
            Stock = reader.GetInt32(4),
            CreatedAt = reader.GetDateTime(5)
        };
    }
    return null;
}

EF Core (from WebApiEF sample)

public async Task<Product?> UpdateAsync(int id, string name, string? description,
                                         decimal price, int stock)
{
    var product = await m_context.Products.FindAsync(id);
    if (product == null)
        return null;

    product.Name = name;
    product.Description = description;
    product.Price = price;
    product.Stock = stock;

    await m_context.SaveChangesAsync();
    return product;
}

The Tradeoffs

When to Choose Each

Choose EF Core when:

• Rapid development matters more than micro-optimization
• You have standard CRUD operations
• You want compile-time safety
• You need relationships and navigation properties
• You value maintainability over raw performance

Choose ADO.NET when:

• You need maximum performance
• You have complex SQL that doesn't map to LINQ
• You're migrating existing SQL code
• You want full control over every query
• You're building a performance-critical hot path

The good news: You can use both in the same application. Use EF Core for most operations, drop down to raw SQL for the 5% that need it.

API Reference

Users Endpoints

Products Endpoints

Orders Endpoints

Testing with curl

# Create a user
curl -X POST http://localhost:5183/api/users \
  -H "Content-Type: application/json" \
  -d '{"name": "John Doe", "email": "john@example.com"}'

# Create an order
curl -X POST http://localhost:5183/api/orders \
  -H "Content-Type: application/json" \
  -d '{
    "userId": 1,
    "items": [
      {"productId": 1, "quantity": 2},
      {"productId": 2, "quantity": 1}
    ]
  }'

# Get order with details
curl http://localhost:5183/api/orders/1

Summary

In this tutorial, we built an e-commerce API using WitDatabase with Entity Framework Core:

The EF Core approach is ideal when you want to focus on business logic rather than SQL mechanics. The code is shorter, safer, and more maintainable.

Get the Code

git clone https://github.com/dmitrat/WitDatabase.git
cd WitDatabase/Samples/OutWit.Database.Samples.WebApiEF
dotnet run

Questions or feedback? Open an issue on GitHub!