ERP Database with EF Core

Introduction

ERP Database with EF Core is essential for ASP.NET Core MVC developers building ShopNest.Data — Toolliyo's 100-article enterprise learning platform covering products, orders, cart, payments, dashboard, and audit logs. Whether you target campus drives at TCS, Infosys, or Wipro, or build admin portals at product companies, this lesson delivers production-grade MVC depth.

In Indian delivery projects, teams lose sprints when juniors skip erp database with ef core fundamentals — N+1 queries, missing indexes, sync database calls, or untracked migration strategy. This article prevents that class of failure on ERP.

After this article you will

• Explain ERP Database with EF Core in plain English and in technical EF Core ORM terms
• Implement erp database with ef core in ShopNest.Data (ERP)
• Compare the wrong approach vs the production-ready enterprise approach
• Answer fresher and mid-level EF Core interview questions confidently
• Connect this lesson to Article 98 and the 100-article EF Core roadmap

Prerequisites

• Software: .NET 8 SDK, VS 2022 or VS Code, SQL Server Express / LocalDB
• Knowledge: C# basics
• Previous: Article 96 — CRM Database with EF Core
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

EF Core is a translator between C# objects and SQL Server — you speak C#; it writes SQL the database understands.

Level 2 — Technical

ERP Database with EF Core integrates with the Entity Framework Core data layer: configure DbContext in Program.cs, define entities and fluent configurations, use repositories/services for data access. On ShopNest.Data this powers ERP without coupling UI to database internals.

Level 3 — Architecture

[Browser] → [HTTPS/Kestrel] → [Middleware Pipeline]
  → [Routing] → [Controller Action] → [Service Layer]
  → [EF Core / Identity] → [Razor View Engine] → [HTML Response]

Project structure

ShopNest.Data/
├── Controllers/       ← HTTP request handlers
├── Models/            ← Domain entities + ViewModels
├── Views/             ← Razor .cshtml templates
├── Services/          ← Business logic (DI)
├── Data/              ← DbContext, migrations
├── Areas/Admin/       ← Admin module (Article 9+)
├── wwwroot/           ← CSS, JS, Bootstrap
└── Program.cs         ← DI + middleware pipeline

Hands-on — ShopNest.Data (ERP)

Step 1 — The wrong way

// ❌ BAD — fat controller, no ViewModel, sync DB call
public IActionResult Index()
{
    return _context.Products.Find(id); // sync, exposes entity, no auth
}

Step 2 — The right way

// ✅ CORRECT — ERP Database with EF Core on ShopNest (ERP)
public async Task GetDtoAsync(int id, CancellationToken ct)
{
    return await _context.Products.AsNoTracking()
        .Where(p => p.Id == id)
        .Select(p => new ProductDto { Id = p.Id, Name = p.Name })
        .FirstOrDefaultAsync(ct);
}

Step 3 — Apply ERP Database with EF Core

await _context.Products
    .Where(p => p.IsPublished)
    .OrderBy(p => p.Name)
    .ToListAsync();

dotnet ef migrations add InitialShopNest
dotnet ef database update
# Verify tables in SQL Server Management Studio

Database design

Product (Id, Name, Price, CategoryId)
Category (Id, Name)
Order (Id, CustomerId, OrderDate, Total)
OrderItem (OrderId, ProductId, Quantity, UnitPrice)

Use FK constraints, indexes on CategoryId and CustomerId, and avoid SELECT * in production LINQ queries.

Common errors & fixes

🔴 Mistake 1: Fat controllers with EF Core queries inline
✅ Fix: Move data access to services/repositories; keep controllers thin.

🔴 Mistake 2: Using synchronous .ToList() instead of ToListAsync() on hot paths
✅ Fix: Use async/await end-to-end; sync calls block thread pool under load.

🔴 Mistake 3: Loading entire tables with .ToList() without pagination or filters
✅ Fix: Use AsNoTracking(), projection to DTOs, and pagination for list endpoints.

🔴 Mistake 4: Hard-coding connection strings in controllers
✅ Fix: Use appsettings.json + User Secrets locally; Azure Key Vault in production.

Best practices

• 🟢 Use async/await end-to-end for database and I/O calls
• 🟢 Register DbContext as Scoped; avoid capturing it in singletons
• 🟡 Use AsNoTracking for read-only queries; never expose tracked entities across request boundaries
• 🟡 Use Fluent API for schema; validate business rules in services before SaveChangesAsync
• 🔴 Log structured data with Serilog — include OrderId, UserId, not passwords
• 🔴 Use HTTPS, secure cookies, and authorization policies in production

Interview questions

Fresher level

Q1: What is ERP Database with EF Core in ASP.NET Core MVC?
A: ERP Database with EF Core is a core MVC capability used in ShopNest.Data for ERP. Explain in one sentence, then describe controller/view/service placement.

Q2: How would you implement ERP Database with EF Core on a TCS-style delivery project?
A: DbContext as Scoped, async LINQ, repository pattern, migrations in CI/CD, and integration tests with InMemory or SQLite.

Q3: Code First vs Database First vs Raw SQL — when to use which?
A: Code First for greenfield apps; Database First (scaffold) for legacy DBs; Dapper/raw SQL for hot-path reads or reports.

Mid / senior level

Q4: Explain the EF Core query execution pipeline briefly.
A: LINQ → Expression Tree → SQL Generator → SQL Server → Data Reader → Entity Materialization → Change Tracker.

Q5: Common production mistake with this topic?
A: Skipping validation, exposing secrets in Git, or untested edge cases (null model, unauthorized user).

Q6: .NET 8/9 EF Core vs EF6 / ADO.NET?
A: Core is cross-platform, faster, cloud-ready; Framework is maintenance mode on Windows/IIS.

Coding round

Write a LINQ query: top 3 customers by total order value on ShopNest orders.

var top = await _context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.GrandTotal) })
    .OrderByDescending(x => x.Total).Take(3).ToListAsync();

Summary & next steps

• Article 97: ERP Database with EF Core
• Module: Module 10: Real-World Projects · Level: ADVANCED
• Applied to ShopNest.Data — ERP

Previous: CRM Database with EF Core
Next: Multi-Tenant SaaS Database with EF Core

Practice: Add one small feature using today's pattern — commit with feat(efcore): article-97.

FAQ