How I Used EF Core in a Real Inventory System

When people first learn Entity Framework Core, the examples are usually simple. You create a Product table, perform a few CRUD operations, and everything works nicely. Real business applications are rarely that simple.

A while ago, I worked on an inventory management system where inventory accuracy was one of the most important requirements. The application handled stock receiving, stock transfers, sales, and stock adjustments. Every transaction affected inventory levels, so even a small bug could create incorrect inventory balances.

This article shares some of the things I learned while using EF Core in that kind of system. The code samples shown are simplified examples created only for demonstration purposes and are not copied from any actual production project.

Understanding the Business Problem

An inventory system is more than storing products in a database. Every stock movement needs to be recorded correctly because the inventory quantity directly affects purchasing decisions, warehouse operations, and customer orders.

One mistake I often see is updating the current stock quantity directly without recording how it changed. It may work for a while, but eventually someone asks questions like:

“Why did this product lose five pieces yesterday?”

If the system only stores the current quantity, there is no reliable way to answer.

In one of my projects, we decided that every inventory change must create a transaction record. The current stock became a summary of all transactions instead of the only source of truth.

Takeaway: Design your inventory model around stock movements, not just current quantities.

Designing the Data Model

The database model does not need to be overly complicated, but it should represent how inventory actually works.

A simplified version looked something like this:

C#
public class Product
{
    public int Id { get; set; }

    public string Name { get; set; } = "";

    public int QuantityOnHand { get; set; }

    public ICollection<InventoryTransaction> Transactions { get; set; }
        = new List<InventoryTransaction>();
}

public class InventoryTransaction
{
    public int Id { get; set; }

    public int ProductId { get; set; }

    public int Quantity { get; set; }

    public string TransactionType { get; set; } = "";

    public DateTime CreatedAt { get; set; }

    public Product Product { get; set; } = null!;
}

EF Core made the relationship between these entities easy to configure. Instead of writing SQL joins manually every time, the navigation properties allowed us to load related data when needed.

At first, I thought keeping only the QuantityOnHand field would be enough. Later I realized transaction history became much more valuable during audits and troubleshooting.

Takeaway: Your entity model should reflect business processes, not only database tables.

Saving Inventory Transactions Safely

Whenever inventory changed, both the product quantity and the transaction history had to be updated together.

A simplified example looks like this:

C#
var product = await context.Products.FindAsync(productId);

product.QuantityOnHand += quantityReceived;

context.InventoryTransactions.Add(
    new InventoryTransaction
    {
        ProductId = product.Id,
        Quantity = quantityReceived,
        TransactionType = "Receiving",
        CreatedAt = DateTime.UtcNow
    });

await context.SaveChangesAsync();

The nice thing about EF Core is that both changes are saved in a single unit of work. If something fails before SaveChangesAsync(), nothing gets committed.

For inventory systems, this behavior is important because partial updates can easily create incorrect stock balances.

Takeaway: Keep related inventory updates inside the same database transaction whenever possible.

Performance Became Important

Everything worked well during development. Then the database started growing.

One screen displayed hundreds of products together with supplier information and recent inventory history. My first implementation simply loaded everything.

It worked, but it became noticeably slower.

I remember struggling with this at first because the LINQ query looked perfectly fine.

Eventually I realized I was loading much more data than the page actually needed.

Instead of loading complete entities, I projected only the required columns.

var products = await context.Products
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.QuantityOnHand
    })
    .ToListAsync();

This reduced the amount of data transferred from SQL Server and made the page respond much faster.

Another improvement was using AsNoTracking() for read-only pages.

C#
var products = await context.Products
    .AsNoTracking()
    .ToListAsync();

Since EF Core no longer tracked those entities, memory usage became lower and queries executed a little faster.

Takeaway: Only load the data you really need, especially for reporting and dashboard pages.

A Common Mistake I Learned to Avoid

One mistake I made early on was calling SaveChangesAsync() several times inside the same business operation.

For example:

C#
await context.SaveChangesAsync();

// more processing
await context.SaveChangesAsync();

This increases the chance of leaving the database in an inconsistent state if the second operation fails.

Later, I changed the implementation so the complete inventory operation was prepared first, then saved once.

It also made the code easier to understand because the business logic stayed together.

Takeaway: Treat one business operation as one database operation whenever it makes sense.

Why EF Core Worked Well

Some developers prefer writing SQL manually for everything. There are situations where that makes sense, especially for very complex reports.

For most business operations in our inventory system, EF Core provided a good balance between productivity and control.

LINQ queries were readable, migrations simplified schema changes, and change tracking reduced the amount of repetitive update code we had to write.

That does not mean EF Core removes the need to understand SQL. Quite the opposite.

One thing I learned while working on this feature was that developers who understand how SQL works usually write better EF Core queries. It becomes much easier to recognize inefficient joins, unnecessary includes, or queries that return far more data than expected.

Final Thoughts

Using EF Core in a real inventory system taught me that the framework itself is only part of the solution. Understanding the business domain is even more important.

Inventory applications require accuracy, consistency, and good performance. EF Core helps achieve those goals, but only if the entity model reflects the business correctly and database operations are designed carefully.

If I were starting another inventory project today, I would still choose EF Core. I would simply spend more time designing the inventory transaction model before writing the first line of code. That decision pays off much later when users start asking where their inventory went and the system can actually provide the answer.

Sources

Assi Arai
Assi Arai
Articles: 57

Related Posts

Trending now

How I Used EF Core in a Real Inventory System
WordPress Functions
How to Set Up a WordPress Website with Docker Compose
How to Use Microsoft.Extensions.Polly for Resilient HTTP Requests in .NET