Entity Insert, Select, Update, Delete (CRUD)

Mastering data operations with Entity Framework Core

CRUD (Create, Read, Update, Delete) is the fundamental set of operations you’ll perform against your database.
Entity Framework Core (EF Core) gives you a high-level, strongly-typed API to perform CRUD with C# objects while handling SQL generation, change tracking, and transactions for you.

This guide covers best-practice patterns, code examples, performance tips, and common pitfalls — everything you need to operate safely and efficiently in production.

🔹 Core concepts you must know first

• DbContext: the session that tracks entities and orchestrates database I/O (SaveChanges() / SaveChangesAsync()).
• Change Tracker: EF’s mechanism to detect inserts, updates, deletes by tracking entity states (Added, Modified, Deleted, Unchanged, Detached).
• Tracking vs No-Tracking: Tracked queries attach entities to the context (useful for updates). AsNoTracking() returns read-only objects with better read performance.
• Eager / Lazy / Explicit Loading: How navigation properties get loaded (Include(), lazy loading with virtual and proxies, or Load()).
• Concurrency: Optimistic concurrency with concurrency tokens (e.g., rowversion) prevents lost updates.
• Transactions: SaveChanges() uses an implicit transaction; use explicit transactions (BeginTransaction()) for multi-step operations across contexts or multiple databases.
• Async APIs: Prefer async methods in real apps (SaveChangesAsync(), ToListAsync(), etc.) to avoid blocking threads.

🔹 CREATE (Insert)

Simple insert (single entity)

using var context = new AppDbContext();

var product = new Product
{
    Name = "Wireless Mouse",
    Price = 25.99m,
    CreatedAt = DateTime.UtcNow
};

// tracks entity as Added
context.Products.Add(product);

// writes to DB; product.Id populated if identity
await context.SaveChangesAsync();

Add multiple entities

context.Products.AddRange(listOfProducts);
await context.SaveChangesAsync();

Attach vs Add

• Add() marks entity as Added — EF will INSERT it.
• Attach() marks entity as Unchanged — use when entity already exists in DB and you don’t want EF to insert/update it immediately.

Insert when you have a detached object (e.g., coming from API)

If the DTO lacks an Id:

var product = new ProductDto { Name = "...", Price = 9.99M };
var entity = mapper.Map<Product>(product);
context.Products.Add(entity);
await context.SaveChangesAsync();

Bulk insert options

• EF Core supports batching, but for very large inserts consider third-party libraries (e.g., EFCore.BulkExtensions) or database-specific bulk copy APIs.

🔹 READ (Select)

Basic LINQ select

var allProducts = await context.Products.ToListAsync();

Filtering & projection

var cheapNames = await context.Products
    .Where(p => p.Price < 50)
    .OrderBy(p => p.Name)
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();

Get by primary key

• Find() first checks the change tracker then database (synchronous).
• FindAsync() is async variant.

var product = await context.Products.FindAsync(id);

Single / First semantics

• Single() / SingleAsync() throws if more than one match.
• First() / FirstAsync() returns first match or throws if none.
• FirstOrDefault() / SingleOrDefault() return null/default when not found.

Eager loading navigation properties

var order = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

No-tracking queries for read-only workloads

var reports = await context.Products
    .AsNoTracking()
    .Where(...)
    .ToListAsync();

AsNoTracking() reduces memory and speeds up queries for reporting or UIs that don’t need change tracking.

Pagination pattern

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

Projection to DTOs

Project into DTOs to reduce data transfer and avoid tracking heavy entities:

var dtos = await context.Products
    .Where(p => p.InStock)
    .Select(p => new ProductDto { Id = p.Id, Name = p.Name, Price = p.Price })
    .ToListAsync();

🔹 UPDATE

Tracked-update (recommended when entity is loaded from same context)

var product = await context.Products.FindAsync(id);
if (product == null) return NotFound();

product.Price = 19.99m;
product.UpdatedAt = DateTime.UtcNow;

await context.SaveChangesAsync(); // EF detects Modified properties

Update detached entity (API scenario)

If entity is sent from client (detached), update:

var dto = ...; // from request
var entity = new Product { Id = dto.Id, Name = dto.Name, Price = dto.Price };

// Attach and mark modified
context.Products.Attach(entity);
context.Entry(entity).State = EntityState.Modified;

await context.SaveChangesAsync();

Caution: This marks all properties as modified — may overwrite concurrent changes. Prefer patching or property-level updates.

Update only specific properties (safer)

var entity = new Product { Id = dto.Id };
context.Products.Attach(entity);
context.Entry(entity).Property(e => e.Price).CurrentValue = dto.Price;
context.Entry(entity).Property(e => e.Price).IsModified = true;

await context.SaveChangesAsync();

Concurrency control (optimistic concurrency)

Add a concurrency token (e.g., byte[] RowVersion with [Timestamp] or Fluent API). On update:

try
{
    product.Name = "New";
    await context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Handle concurrency conflict: reload, merge, prompt user, retry, etc.
}

Partial updates with SQL (When needed)

Use ExecuteSqlRawAsync() for targeted updates to avoid fetching:

await context.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Price = {0}, UpdatedAt = {1} WHERE Id = {2}",
    newPrice, DateTime.UtcNow, id);

Note: Raw SQL bypasses change tracking and should be used carefully.

🔹 DELETE

Deleting an entity loaded in the same context

var product = await context.Products.FindAsync(id);
if (product != null)
{
    context.Products.Remove(product);
    await context.SaveChangesAsync();
}

Delete by key without fetching full entity

var product = new Product { Id = id };
context.Products.Attach(product);
context.Products.Remove(product);
await context.SaveChangesAsync();

Batch deletes

EF Core doesn’t provide a built-in batch delete that translates to single DELETE ... WHERE ... — it fetches entries then removes them. For large deletes, use:

• Raw SQL: await context.Database.ExecuteSqlRawAsync("DELETE FROM Products WHERE ...");
• Third-party extensions (EFCore.BulkExtensions) for batch deletes.

Cascade deletes

Configure cascade delete with Fluent API or Data Annotations. EF will delete dependent entities when principal is removed if cascade is configured. Be careful with accidental largescale cascades.

🔹 Change Tracking & Entity States

Use context.Entry(entity).State to inspect or set state:

• EntityState.Added → will insert
• EntityState.Modified → will update
• EntityState.Deleted → will delete
• EntityState.Unchanged → no operation
• EntityState.Detached → not tracked

Query example:

var state = context.Entry(product).State;

Use change tracker for diagnostics:

var changes = context.ChangeTracker.Entries()
    .Where(e => e.State != EntityState.Unchanged)
    .ToList();

🔹 Transactions & Atomicity

SaveChanges() wraps changes in a transaction by default. For multi-step operations across multiple contexts or external resources, use explicit transactions:

using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // multiple changes
    await context.SaveChangesAsync();

    // maybe call another context or external service

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

For distributed transactions or multiple resource managers, consider outbox patterns or external transaction managers.

🔹 Concurrency patterns

• Optimistic concurrency (recommended): add a concurrency token (e.g., [Timestamp] byte[] RowVersion). On conflict EF throws DbUpdateConcurrencyException.
• Pessimistic concurrency (locking): use database locks / SELECT ... FOR UPDATE — not directly supported by EF, typically requires raw SQL.

Handling DbUpdateConcurrencyException:

catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var clientValues = (Product)entry.Entity;

    var databaseEntry = entry.GetDatabaseValues();
    if (databaseEntry == null)
    {
        // deleted by another user
    }
    else
    {
        var dbValues = (Product)databaseEntry.ToObject();
        // decide whether to overwrite or merge; set entry.OriginalValues to dbValues to retry
    }
}

🔹 Error handling and common exceptions

• DbUpdateException: General database update error (e.g., FK violation).
• DbUpdateConcurrencyException: Concurrency conflict.
• SqlException (provider-specific): e.g., unique constraint violation, timeouts.

Best practice: catch specific EF exceptions, log SQL (via LogTo), and provide meaningful user messages.

🔹 Performance tips

• Use AsNoTracking() for read-only queries.
• Project to DTOs to reduce selected columns (avoid .Include() if you only need a few fields).
• Avoid N+1 queries: use Include() or explicit batch queries.
• Use pagination (Skip/Take) and ordering for UI lists.
• Let the DB do aggregates (Sum, Count, Average) instead of loading and calculating in memory.
• Use indices in your DB for frequently queried columns (outside EF).
• For very large bulk operations, use specialized bulk libraries or DB-specific bulk APIs.

🔹 Raw SQL and Stored Procedures

• Query entities from SQL:

var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50)
    .ToList();

• Execute non-query SQL:

await context.Database.ExecuteSqlRawAsync("UPDATE Products SET Price = Price * 0.9 WHERE CategoryId = {0}", categoryId);

Caveat: FromSqlRaw() can be used to project into entities but must match the entity schema. It can be used with AsNoTracking().

🔹 Security considerations

• Avoid raw SQL concatenation — use parameterized methods (FromSqlRaw with placeholders or FromSqlInterpolated).
• Sanitize inputs used in dynamic SQL.
• Prefer LINQ where possible — EF parameterizes queries and prevents SQL injection.

🔹 Summary (Quick Reference)

✅ Best Practices

• Prefer tracked reads when you plan to update the entity; use AsNoTracking for read-only queries.
• Always use async APIs in web apps.
• Project into DTOs for UI and API outputs to reduce payload and decouple schema.
• Use migrations and version control for schema evolution.
• Avoid full-entity updates from detached objects — patch only changed properties where possible.
• Handle concurrency conflicts explicitly and inform users when a conflict occurs.
• Test exceptions and edge cases (FK violation, constraint errors) and map them to user-friendly messages or retry logic.
• Log SQL and EF events in development/staging to inspect real queries and spot inefficiencies.

🧪 Challenge Task

Implement a small service that performs the following for a Product entity with a RowVersion concurrency token:

1. Insert three products.
2. Read a product (no-tracking) and display it.
3. Read the same product tracked, update price and save.
4. Simulate a concurrency conflict by updating the product directly with raw SQL (change Price and RowVersion), then attempt to update through EF and handle DbUpdateConcurrencyException by loading DB values and merging.

Provide code that demonstrates each step and prints friendly messages about what happened.

Mastering insert, select, update, and delete with EF Core means not only knowing the API but understanding tracking, transactions, concurrency, and performance trade-offs. Follow the patterns above and you’ll write data access code that’s robust, efficient, and safe for production.