Vivek Kaushik

Fundamentals

1️⃣ Code-First Approach

💡 You define entity classes in C#, and EF Core generates the database schema.

Steps:

Create your POCO entities.

Define relationships via navigation properties or OnModelCreating.

Run migrations to generate/update the database.

Example:


public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}


dotnet ef migrations add InitialCreate
dotnet ef database update

Pros:

Keeps schema in sync with code

Supports version control for DB schema

2️⃣ Migrations

💡 Tracks schema changes over time.

Example:


dotnet ef migrations add AddEmailToUser
dotnet ef database update


public partial class AddEmailToUser : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>("Email", "Users", nullable: true);
    }
}

3️⃣ Stored Procedures in EF Core

💡 Call existing stored procedures from EF Core or map to entity types.

Example:


var users = context.Users
    .FromSqlRaw("EXEC GetActiveUsers")
    .ToList();

4️⃣ Functions in EF Core

💡 Use SQL functions via HasDbFunction.

Example:


modelBuilder.HasDbFunction(() => MyFunctions.CalculateAge(default));

5️⃣ Repository Pattern

💡 Encapsulates EF Core DbContext for testability and separation of concerns.


public interface IUserRepository
{
    Task<User> GetByIdAsync(int id);
    Task AddAsync(User user);
}

public class UserRepository : IUserRepository
{
    private readonly AppDbContext _context;
    public UserRepository(AppDbContext context) => _context = context;

    public async Task<User> GetByIdAsync(int id) =>
        await _context.Users.FindAsync(id);

    public async Task AddAsync(User user) =>
        await _context.Users.AddAsync(user);
}

6️⃣ Unit of Work Pattern

💡 Coordinates multiple repositories under a single transaction.


public interface IUnitOfWork
{
    IUserRepository Users { get; }
    Task<int> CompleteAsync();
}
public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
    public IUserRepository Users { get; }
    public UnitOfWork(AppDbContext context, IUserRepository users)
    {
        _context = context;
        Users = users;
    }
    public async Task<int> CompleteAsync() => await _context.SaveChangesAsync();
}

7️⃣ Execute vs SaveChanges

ExecuteSqlRaw: Runs raw SQL immediately (bypasses change tracking).

SaveChanges: Persists all tracked changes to DB.

8️⃣ Change Tracking

💡 EF Core tracks entity states: Added, Modified, Deleted, Unchanged.


var user = context.Users.Find(1);
user.Name = "New Name";
var state = context.Entry(user).State; // Modified

9️⃣ DbContext Configuration


services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("Default")));

Use OnModelCreating for schema configs.

Enable/disable lazy loading.

🔟 Event Sourcing (with EF Core)

💡 Instead of just storing current state, store events that led to it.


public record OrderPlaced(int OrderId, DateTime PlacedAt);
public record ItemAdded(int OrderId, string Item);

Rebuild state by replaying events.

1️⃣1️⃣ Entity Type Configuration Classes

💡 Clean separation of entity mapping logic.


public class UserConfig : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasKey(u => u.Id);
        builder.Property(u => u.Name).IsRequired().HasMaxLength(50);
    }
}


protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new UserConfig());
}

What are the main components of EF Core?

The main components of EF Core include:

DbContext: A way to configure and use EF Core, acting as a bridge between your domain or entity classes and the database.

DbSet: Represents a collection of entities of a specific type that you can query and save.

Model: Represents the mapping between your .NET classes and the database schema.

Querying: Allows data to be retrieved from the database using LINQ.

Change Tracking: Keeps track of changes made to the entities so that it can update the database accordingly.

Migrations: Provides a way to update the database schema to match the data model of your application.

Lazy vs Eager vs Explicit Loading

Feature	Eager Loading	Lazy Loading	Explicit Loading
Loading Behavior	Loads related entities along with the main entity in a single query.	Loads related entities on demand when accessed.	Loads related entities on demand, but in a separate query from the main entity.
Method Used	Utilizes the `Include` method to specify which related entities to load.	Automatically loads related entities when they are accessed.	Uses the `Load` method to explicitly load related entities after the main entity has been loaded.
Query Execution	Executes a single SQL query with joins to fetch all necessary data.	Executes separate SQL queries for each related entity when accessed.	Executes separate SQL queries for the main entity and related entities.
Use Case	Best when you know you will need all related entities at the time of loading the main entity.	Ideal for scenarios where related data may not always be needed.	Useful when you want to selectively load related entities based on certain conditions or user actions.
Performance	Reduces the number of database round-trips, improving performance when accessing multiple related entities.	Can lead to the N+1 problem, resulting in many queries if not managed carefully.	Provides more control over loading, which can be beneficial in scenarios with large datasets or when related entities are not always needed.
Complexity	Simpler to implement as it requires less code to load related data.	Simpler in terms of code but can lead to hidden performance issues.	Requires explicit calls to load related data, which can add complexity to the code.
Disposal Issues	No issues since all data is loaded in one go.	Can cause issues if the `DbContext` is disposed before accessing related entities.	No such issues since related entities are loaded after the main entity is already retrieved.
Examples	Suppose you have `Author` and `Book` entities where an author can have multiple books. You can use eager loading to fetch both authors and their books in one query: `csharp var authorsWithBooks = await context.Authors.Include(a => a.Books).ToListAsync();` This retrieves all authors and their associated books in a single database call.	Using the same `Author` and `Book` entities, if lazy loading is enabled, you can retrieve authors without loading their books initially: `csharp var authors = await context.Authors.ToListAsync(); foreach (var author in authors) { var books = author.Books; // Books are loaded here on demand }` The books for each author are only fetched when accessed, potentially leading to multiple queries if there are many authors.	Continuing with the `Author` and `Book` entities, you can explicitly load books for a specific author after retrieving the authors: `csharp var authors = await context.Authors.ToListAsync(); foreach (var author in authors) { context.Entry(author).Collection(a => a.Books).Load(); // Explicitly loads the books for each author }` This approach allows you to control when the related data is loaded without the overhead of lazy loading.

Relationships in EF Core

One-to-One: A primary key in one entity is also a foreign key in another entity. This can be configured using the HasOne and WithOne methods in the Fluent API.

One-to-Many: A single entity on one side is related to multiple entities on the other side. This is represented by a collection navigation property on the one side and a foreign key on the many side. The Fluent API is configured using HasMany and WithOne.

Many-to-Many: Entities on both sides can relate to multiple entities on the other side. EF Core handles many-to-many relationships by implicitly creating a join table (though you can explicitly define it if needed).

DbContext

A DbContext in EF Core represents a session with the database, allowing you to query and save instances of your entities. It is a combination of the Unit Of Work and Repository patterns and provides APIs to perform CRUD operations, manage transactions, and track changes to objects.

Entities

Entities in EF Core represent the data that you want to map to the database tables. They are CLR (Common Language Runtime) classes that are mapped to database tables. An entity includes properties that map to the columns of a database table.

LINQ

LINQ (Language Integrated Query) is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages using a syntax reminiscent of SQL but integrated into the programming language.

Unit of Work and Repository Pattern

Unit of Work in EF Core

Built-in Unit of Work: EF Core's DbContext acts as a Unit of Work. It tracks changes to entities and manages transactions. When you call SaveChanges(), it saves all changes made to the entities tracked by the context in a single transaction, ensuring data integrity.

Transaction Management: The Unit of Work pattern helps manage transactions, allowing you to commit multiple operations only if all succeed. EF Core handles this inherently through the DbContext.

Repository Pattern in EF Core

DbSet as Repository: EF Core provides DbSet<T> as a built-in repository for each entity type. This allows you to perform CRUD operations directly on the DbSet, which abstracts the data access layer.

Abstraction Layer: While EF Core provides these built-in functionalities, some developers choose to implement their own repository pattern to create an abstraction layer over EF Core. This can help in unit testing and decoupling the application from the data access layer, but it may also lead to unnecessary complexity if not needed

What are Entity States in EF Core? Describe them.

Entity States in EF Core represent the state of an entity with respect to its tracking by the DbContext. The possible states are:

Added: The entity is new and should be inserted into the database on SaveChanges().

Unchanged: The entity has not been modified since it was retrieved from the database.

Modified: The entity has been modified, and the changes should be saved in the database.

Deleted: The entity has been marked for deletion from the database.

Detached: The entity is not being tracked by the DbContext.

How can you improve query performance in EF Core?

Improving query performance in EF Core can be achieved by:

Using AsNoTracking() for read-only queries to reduce overhead.

Selecting only the necessary columns instead of retrieving entire entities.

Avoid N+1 queries by eagerly loading related data when necessary.

Using raw SQL queries for complex queries for better efficiency.

Enabling query caching where appropriate.

How can you optimize performance in EF Core?

Performance in EF Core can be optimised through various strategies:

Eager Loading: Use the Include method to preload related data within the same query to avoid the N+1 query problem.

Projection: Use Select to load only the necessary data fields rather than entire entities.

Batching: Take advantage of EF Core’s ability to batch operations to reduce round trips to the database.

AsNoTracking: Use AsNoTracking for read-only operations to improve query performance.

Indexing: Ensure proper indexing in your database to speed up query execution.

Pooling: Use DbContextPooling to reduce the overhead of instantiating DbContext instances.

N+1 query problem

The N+1 query problem is a performance issue that can occur when an application needs to retrieve related data from a database. It happens when the application makes an initial query to fetch a collection of objects, and then an additional query for each object to fetch its related data. This results in N+1 queries, where N is the number of objects returned by the initial query.Here's a simple example to illustrate the problem:


# Initial query to fetch all books
books = Book.objects.all()

# Iterate over the books and fetch their authors
for book in books:
    author = book.author
    # Do something with the book and author

In this example, if there are 10 books, the application will execute 11 queries:

1 query to fetch all books

10 queries (1 for each book) to fetch the author for each book

This is inefficient because the application could have fetched the authors along with the books in a single query using a JOIN. The N+1 query problem becomes more pronounced as the number of objects and their related data increases.

What is the DbContext Pooling in EF Core, and when should it be used?

DbContext pooling is a performance optimization feature that reuses instances of DbContext classes instead of creating new ones for each request. This reduces the overhead of initialization and disposal of DbContext instances, improving application performance. It should be used in scenarios where the application handles many short-lived DbContext instances, such as web applications.

Queries vs Mutations

Feature	Queries in EF Core	Mutations in EF Core
Definition	Operations that retrieve data from the database.	Operations that change the state of the data in the database (insert, update, delete).
Execution	Uses LINQ to build queries that are translated to SQL.	Uses methods to modify the `DbContext` and then commits changes with `SaveChanges()`.
DbSet	Queries are performed against `DbSet<T>` properties.	Entities are added, updated, or removed from `DbSet<T>`.
Deferred Execution	Queries are executed when results are enumerated (e.g., `ToList()`).	Changes are tracked until `SaveChanges()` is called to commit them.
Filtering	Results can be filtered using the `Where` method.	Not applicable, but entities can be located for updates/deletes using methods like `Find()`.
Eager Loading	Supports eager loading of related entities using `Include()`.	Not applicable; mutations focus on modifying existing data.
Adding Entities	Not applicable; queries focus on retrieving data.	Use `Add` or `AddRange` methods to insert new entities.
Updating Entities	Not applicable; queries focus on retrieving data.	Retrieve an entity, modify its properties, and call `SaveChanges()`.
Deleting Entities	Not applicable; queries focus on retrieving data.	Use `Remove` method to delete entities and call `SaveChanges()`.
Batch Operations	Not applicable; queries focus on retrieving data.	Allows batch operations to add, update, or delete multiple entities in one call to `SaveChanges()`.

SaveChanges() vs Execute()

Feature	SaveChanges / SaveChangesAsync	ExecuteAsync (ExecuteUpdate / ExecuteDelete)
Purpose	Saves changes made to tracked entities in the context to the database.	Executes a command to update or delete entities without tracking.
Tracking	Utilizes EF Core's change tracking to determine which entities have been modified.	Bypasses change tracking, allowing for bulk operations without loading entities into memory.
Method of Execution	Can be called synchronously (`SaveChanges()`) or asynchronously (`SaveChangesAsync()`).	Introduced in EF Core 7.0, executed using `ExecuteUpdate()` or `ExecuteDelete()` methods.
Performance	Can be less efficient for bulk operations due to tracking overhead and multiple database calls.	More efficient for bulk updates or deletes, as it directly translates to SQL commands without tracking.
Concurrency Control	Provides automatic concurrency control using concurrency tokens.	Does not provide built-in concurrency control, as it operates outside of the change tracker.
Use Case	Ideal for scenarios where individual entity changes need to be tracked and saved.	Best suited for bulk operations where tracking is unnecessary, such as deleting or updating multiple records at once.
Example	`context.SaveChanges(); // or context.SaveChangesAsync();`	`context.Blogs.Where(b => b.Rating < 3).ExecuteDelete();`

How do you implement soft delete functionality in EF Core?

Soft delete can be implemented in EF Core by adding a flag (e.g., IsDeleted) to entities to mark them as deleted. You can then use Global Query Filters to filter out entities marked as deleted from all queries automatically. When deleting an entity, instead of removing it, set the flag to true and update the entity.

C# Property Initialization in EF Core

💡 In EF Core, property initialization affects default values, migration scripts, and how EF sets values during materialization.

1️⃣ Auto-Property Initializer (Best for Simple Defaults)

💡 Use when you want a default value for new entities created in code, but you’re okay with EF not enforcing it at the database level (unless you also configure it in OnModelCreating).


public class User
{
    public int Id { get; set; }
    public string Role { get; set; } = "Guest"; // Default for new entities in memory
}

✔ Quick setup

⚠ EF won’t automatically create DEFAULT 'Guest' in SQL unless configured in the model:


modelBuilder.Entity<User>()
    .Property(u => u.Role)
    .HasDefaultValue("Guest");

2️⃣ Default Initialization (Implicit Defaults)

💡 EF just uses the C# type defaults (null, 0, etc.).

If you don’t set it, it’s up to the DB or your code to populate it.


public class Product
{
    public int Id { get; set; }
    public decimal Price { get; set; } // 0 by default
}

✔ No extra code

⚠ Can cause null/zero issues if you rely solely on it.

3️⃣ Constructor Initialization (Logic Before Save)

💡 Great for setting generated IDs, timestamps, or other values before EF inserts the record.


public class Order
{
    public int Id { get; set; }
    public string OrderNumber { get; set; }
    public DateTime CreatedAt { get; set; }

    public Order()
    {
        OrderNumber = $"ORD-{Guid.NewGuid().ToString().Substring(0, 8)}";
        CreatedAt = DateTime.UtcNow;
    }
}

✔ Works before EF SaveChanges()

⚠ EF bypasses constructors when materializing from the DB unless you use parameterized constructors.

4️⃣ Getter Initialization (Expression-bodied Properties)

💡 EF Core will not map computed-only properties unless you mark them with [NotMapped] or configure them.

Good for read-only projections.


public class Circle
{
    public int Id { get; set; }
    public double Radius { get; set; }

    [NotMapped] // EF will ignore this in DB mapping
    public double Area => Math.PI * Radius * Radius;
}

✔ Always correct

⚠ Not persisted in DB unless you use a computed column (HasComputedColumnSql).

5️⃣ `init` Accessor (Immutable Entities)

💡 Great for value objects and immutable patterns in EF Core 5+.

Prevents property mutation after initialization.


public class Person
{
    public int Id { get; set; }
    public string Name { get; init; }
    public DateTime BirthDate { get; init; }
}

✔ Safer domain models

⚠ EF needs to set these via constructor or property binding — sometimes requires [Keyless] configs for value objects.

📌 EF Core Specific Best Practices

Scenario	Recommended Init Type
Default display text in UI only	Auto-property initializer
DB-enforced default value	Auto-property initializer + `.HasDefaultValue()` in `OnModelCreating`
Calculated runtime values	Getter initialization (`[NotMapped]` or computed column)
Domain events, timestamps	Constructor initialization
Immutable records	`init` accessor

Example: All Together in EF Core


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

    // Simple in-memory default
    public string Status { get; set; } = "Draft";

    // Auto timestamp in constructor
    public DateTime CreatedAt { get; set; }

    // Computed column (DB generated)
    public int WordCount { get; private set; }

    // Immutable after initialization
    public string Author { get; init; }

    public BlogPost()
    {
        CreatedAt = DateTime.UtcNow;
    }
}

// In OnModelCreating
modelBuilder.Entity<BlogPost>()
    .Property(p => p.Status).HasDefaultValue("Draft");

modelBuilder.Entity<BlogPost>()
    .Property(p => p.WordCount)
    .HasComputedColumnSql("LEN(Content) - LEN(REPLACE(Content, ' ', '')) + 1");

EF/LINQ Interview Question

Fundamentals

1️⃣ Code-First Approach

2️⃣ Migrations

3️⃣ Stored Procedures in EF Core

4️⃣ Functions in EF Core

5️⃣ Repository Pattern

6️⃣ Unit of Work Pattern

7️⃣ Execute vs SaveChanges

8️⃣ Change Tracking

9️⃣ DbContext Configuration

🔟 Event Sourcing (with EF Core)

1️⃣1️⃣ Entity Type Configuration Classes

What are the main components of EF Core?

Lazy vs Eager vs Explicit Loading

Relationships in EF Core

DbContext

Entities

LINQ

Unit of Work and Repository Pattern

What are Entity States in EF Core? Describe them.

How can you improve query performance in EF Core?

How can you optimize performance in EF Core?

N+1 query problem

What is the DbContext Pooling in EF Core, and when should it be used?

Queries vs Mutations

SaveChanges() vs Execute()

How do you implement soft delete functionality in EF Core?

C# Property Initialization in EF Core

1️⃣ Auto-Property Initializer (Best for Simple Defaults)

2️⃣ Default Initialization (Implicit Defaults)

3️⃣ Constructor Initialization (Logic Before Save)

4️⃣ Getter Initialization (Expression-bodied Properties)

5️⃣ init Accessor (Immutable Entities)

📌 EF Core Specific Best Practices

Example: All Together in EF Core

5️⃣ `init` Accessor (Immutable Entities)