EF Core interview questions for experienced

EF Core interview questions for experienced

1. What are the main differences between Entity Framework (EF) and Entity Framework Core (EF Core)?

EF Core is a complete rewrite of EF. Unlike EF, EF Core supports cross-platform development, improved performance, LINQ query translation, and has a more modular architecture. EF Core does not support all features that EF 6 does (like lazy loading and some database providers).

2. How does EF Core handle migrations, and what are some common pitfalls?

EF Core uses migrations to update the database schema based on model changes. Common pitfalls include not applying migrations correctly, misalignment between model and database state, or missing/overwriting migrations during collaboration in a team environment.

3. Explain the difference between Add and Attach methods in EF Core.

Add method is used to add a new entity to the context and mark it as Added, while Attach attaches an existing entity to the context with no changes. The Attach method is used when you know an entity already exists in the database, and you want EF Core to start tracking it.

4. What is the difference between FirstOrDefault and SingleOrDefault in EF Core?

FirstOrDefault: 

    Returns the first element of a sequence or a default value if the sequence is empty. It’s safe to use when you expect multiple results but only care about the first one. It won’t throw an error if there are multiple elements.

    So, Safe for multiple results, returns the first.

SingleOrDefault:

    Returns a single element or a default value if none exists. It throws an exception if there are multiple results. Use it when you expect at most one result.

    So, Strict for a single result, throws an error if there are multiple.

5. What is the significance of the AsNoTracking method in EF Core?

AsNoTracking is used when you don’t need to modify entities or track changes. It improves performance because EF Core doesn't have to track the changes to the entities, making queries faster.

6. What are shadow properties in EF Core?

Shadow properties are properties that are not defined in the entity class but are defined in the model. They are stored in the database and can be useful for storing things like audit columns (e.g., CreatedDate, ModifiedDate) without adding them explicitly to the entity class.

7. How would you implement a many-to-many relationship in EF Core?

 In EF Core 5 and later, EF Core can automatically handle many-to-many relationships without needing a separate join entity. For earlier versions, a join table (entity) is required, which is manually mapped with HasMany and WithMany methods.

8. Explain how you can optimize performance in EF Core.

Optimization can include:

• Using AsNoTracking when read-only queries are needed.
• Avoiding Include for large datasets.
• Efficiently handling large datasets with pagination or batching.
• Using raw SQL for complex queries.
• Proper indexing in the database.
• Monitoring and limiting the number of queries (N+1 problem).

9. What is the N+1 query problem, and how can you avoid it in EF Core?

The N+1 problem occurs when a query that loads a list of entities triggers additional queries for each related entity (e.g., when navigating navigation properties). It can be avoided by using Include or ThenInclude to load related entities in a single query.

10. What is the role of DbContext in EF Core?

DbContext is the main class used to interact with the database in EF Core. It manages entity objects, tracks changes, and handles querying and saving data. It serves as a bridge between the application and the database.

11. Explain the concept of lazy loading in EF Core. How can it be enabled?

• Answer: Lazy loading allows related data to be loaded automatically when accessed. In EF Core, it can be enabled by installing the Microsoft.EntityFrameworkCore.Proxies package and configuring it to use proxies. Lazy loading should be used cautiously to avoid unnecessary queries.

12. What are the different ways to execute raw SQL queries in EF Core?

• Answer: EF Core allows raw SQL execution through methods like FromSqlRaw, ExecuteSqlRaw, or ExecuteSqlInterpolated for queries and commands. You can use these for scenarios where LINQ is not efficient or feasible.

13. How would you handle concurrency in EF Core?

EF Core supports optimistic concurrency. You can implement it by adding a Timestamp or RowVersion property to your entity. EF Core will automatically check for conflicts during save operations and throw a DbUpdateConcurrencyException if a conflict occurs.

14. Can you explain the concept of "query splitting" in EF Core?

Query splitting in EF Core breaks down a query into multiple separate queries, each fetching a part of the data. This helps to avoid issues like fetching large, unnecessary data and improves performance by splitting the data into logical chunks.

15. How does EF Core handle database transactions?

EF Core uses database transactions to ensure atomic operations. By default, EF Core will wrap operations in a transaction, but you can manage it manually using DbContext.Database.BeginTransaction(), Commit(), and Rollback() for more complex scenarios.

16. What are value conversions in EF Core?

Value conversions are used to convert properties between different types for storage in the database. This is useful for scenarios like converting enums to strings, or storing JSON data in a single column.

17. How can you configure the EF Core model using Fluent API?

• What it tests: Experience with Fluent API configurations for complex models.
• Answer: The Fluent API allows configuring entities in more detail than data annotations. You can configure primary keys, relationships, indexes, constraints, and other configurations such as property mappings using methods like HasKey, HasMany, WithOne, Property, and more.

18. What is the difference between a DbSet<TEntity> and IQueryable<TEntity> in EF Core?

• What it tests: Understanding the different interfaces in EF Core for querying data.
• Answer: DbSet<TEntity> represents a collection of entities of a particular type in the context and allows CRUD operations. IQueryable<TEntity> is an interface for querying data and is typically used for constructing queries that can be executed against the database.

19. How can you handle multiple databases or schemas in a single EF Core context?

• What it tests: Knowledge of handling complex scenarios involving multiple databases.
• Answer: You can configure multiple DbContext instances, each pointing to a different database. Each DbContext can have its own configuration, including connection strings, providers, and schemas. Alternatively, you can configure a single DbContext to handle different schemas by using ToTable or HasDefaultSchema for specific entities.

20. What are the benefits of using the Owned Entity type in EF Core, and when should you use it?

• What it tests: Understanding of advanced EF Core model configurations.
• Answer: Owned entities allow you to create complex types that don't have their own identity and are tightly bound to a parent entity. They're useful when an entity should be treated as part of another entity (e.g., an Address owned by a User). They share the same lifecycle and can be queried as part of the parent entity without requiring a separate table or set.

21. Explain how to implement soft deletes in EF Core.

• What it tests: Knowledge of implementing custom logic in EF Core.
• Answer: Soft deletes are typically implemented by adding a Deleted or IsDeleted flag to the entity and overriding the SaveChanges method to automatically filter out soft-deleted entities in queries. You can add a global query filter to exclude soft-deleted records, e.g., modelBuilder.Entity<TEntity>().HasQueryFilter(e => !e.IsDeleted);.

22. How would you handle database connection pooling in EF Core?

• What it tests: Knowledge of optimizing database connection management.
• Answer: EF Core automatically uses connection pooling if the underlying database provider supports it. This is handled through the connection string. Connection pooling reduces the overhead of opening and closing connections repeatedly. In high-load applications, connection pooling can significantly improve performance.

23. How do you use ExecuteSqlRaw or ExecuteSqlInterpolated in EF Core, and when should you prefer them?

• What it tests: Understanding raw SQL execution in EF Core.
• Answer: ExecuteSqlRaw and ExecuteSqlInterpolated are used to execute raw SQL queries (for non-entity operations). ExecuteSqlRaw is used for unparameterized queries, while ExecuteSqlInterpolated allows for safe string interpolation with parameters, reducing SQL injection risks. They are useful for executing stored procedures, updating rows, or running non-query SQL commands.

24. What is the significance of ModelBuilder in EF Core?

• What it tests: Understanding the role of model configuration in EF Core.
• Answer: ModelBuilder is used to configure the schema of the database and map entities to tables, properties to columns, relationships between entities, and various other configurations. It is typically used in the OnModelCreating method to set up mappings and constraints.

25. What is the difference between DbContext.SaveChanges() and DbContext.SaveChangesAsync()?

• What it tests: Understanding the difference between synchronous and asynchronous operations in EF Core.
• Answer: SaveChanges() is a synchronous method that saves changes to the database, blocking the thread until the operation is complete. SaveChangesAsync() is an asynchronous version that does not block the calling thread, which is useful for scalable, non-blocking operations, especially in web applications.

26. What is the ChangeTracker in EF Core and how can you use it to track entity changes?

• What it tests: Understanding how EF Core tracks and manages changes in entities.
• Answer: The ChangeTracker is a component of DbContext that tracks changes made to the entities. It helps in determining whether an entity is new, modified, or deleted when calling SaveChanges(). You can inspect or manipulate the change tracking state by using DbContext.ChangeTracker.Entries().

27. What is the DbContextPooling feature in EF Core, and when should you use it?

• What it tests: Knowledge of connection and context management for scalability.
• Answer: DbContextPooling enables the reuse of DbContext instances from a pool rather than creating new ones each time. This can improve performance by reducing the overhead of creating and disposing of DbContext objects. It should be used in high-performance scenarios where frequent instantiation of DbContext is a bottleneck.

28. What is a Cascade Delete, and how do you configure it in EF Core?

• What it tests: Knowledge of handling related entity deletions.
• Answer: A Cascade Delete ensures that when a parent entity is deleted, all related child entities are also deleted. It can be configured using the OnDelete method in the Fluent API (e.g., modelBuilder.Entity<Order>().HasOne(o => o.Customer).WithMany(c => c.Orders).OnDelete(DeleteBehavior.Cascade);).

29. How do you handle data seeding in EF Core, and when is it useful?

• What it tests: Understanding of initializing data within EF Core applications.
• Answer: Data seeding is the process of populating the database with initial data when it is first created. It is done using the HasData method in OnModelCreating. It is useful for populating reference data, such as lookup tables, when setting up or migrating a database.

30. Explain what ValueGeneratedOnAdd and ValueGeneratedOnUpdate mean in EF Core.

• What it tests: Understanding of entity property value generation.
• Answer: ValueGeneratedOnAdd indicates that a value should be generated when an entity is added to the database (e.g., for identity columns or GUIDs). ValueGeneratedOnUpdate is used when a value is generated upon updating an existing entity, like a timestamp or version number.

31. What are some strategies to avoid the "Select N+1" problem in EF Core?

• What it tests: Knowledge of query performance optimizations.
• Answer: To avoid the "Select N+1" problem in EF Core:
  • Use Include() or ThenInclude() to eagerly load related entities in a single query.
  • Use .ToList() or .AsEnumerable() to execute the query earlier, preventing multiple queries from being generated.
  • Limit the number of entities and related data retrieved with filtering or pagination.

32. How can you integrate custom stored procedures with EF Core?

• What it tests: Knowledge of custom SQL integration in EF Core.
• Answer: You can map stored procedures to functions in EF Core by using DbSet.FromSqlRaw() or DbSet.FromSqlInterpolated(). You can also execute stored procedures directly with ExecuteSqlRaw() or ExecuteSqlInterpolated() for commands, or define functions with HasDbFunction() for mapped stored procedures.

33. What is the role of DbSet<TEntity>.Local property in EF Core?

• What it tests: Understanding entity tracking and caching behavior in EF Core.
• Answer: The Local property of DbSet<TEntity> provides access to the in-memory cache of entities that have been loaded by the DbContext. It can be used for querying entities that are already being tracked, without querying the database again. This is useful for checking changes before saving or working with data offline.

34. What is the ExecuteUpdate method in EF Core, and how can it be useful?

• What it tests: Knowledge of new EF Core features for efficient updates.
• Answer: The ExecuteUpdate method allows for performing batch updates in a more efficient way than iterating over entities and calling SaveChanges(). It sends a single SQL command to the database to update multiple rows, improving performance.

35. How can you implement versioning in EF Core to handle data changes across multiple users?

• What it tests: Understanding concurrency and version control in EF Core.
• Answer: Versioning can be implemented using a RowVersion or Timestamp property. This is a form of optimistic concurrency control where EF Core checks if the version of the entity has changed before saving it. If it has, a DbUpdateConcurrencyException is thrown.

36. What is the importance of DbContext disposal, and how do you manage it effectively?

• What it tests: Best practices in resource management and application performance.
• Answer: DbContext should be disposed of properly to release unmanaged resources like database connections. It's best to use dependency injection with scoped lifetimes in web applications, ensuring that a DbContext instance is created and disposed per request. Avoid long-lived DbContext instances to prevent memory leaks and inefficient queries.

Here are even more advanced EF Core interview questions, designed to assess deep knowledge and experience with various features, performance optimizations, and real-world scenarios in EF Core:

37. Explain the concept of Change Tracking in EF Core and how you can optimize it.

• What it tests: Understanding of EF Core's entity tracking mechanism.
• Answer: EF Core tracks changes to entities in memory by default, keeping track of whether properties are added, modified, or deleted. This can impact performance, especially in large datasets. Optimizations include using AsNoTracking for read-only queries, disabling change tracking for large bulk operations, or turning it off globally for specific scenarios.

38. How does EF Core handle identity resolution, and how can you prevent duplicate objects?

• What it tests: Knowledge of EF Core's internal object management.
• Answer: EF Core ensures that each entity is only tracked once per context (identity resolution) by checking the entity’s primary key. If an entity with the same primary key is loaded multiple times, EF Core resolves it to the same instance. To avoid duplicates, use the same DbContext instance within a unit of work and avoid querying the same entities multiple times within the same context.

39. What is TrackingBehavior in EF Core, and how do you use it?

• What it tests: Understanding EF Core’s behavior for tracking entities.
• Answer: TrackingBehavior controls how EF Core tracks changes to entities. It can be set globally (e.g., ChangeTracker.QueryTrackingBehavior) or for individual queries using AsTracking or AsNoTracking. The default is TrackAll, but for performance reasons, you can use NoTracking for read-only queries to reduce overhead.

40. What is the purpose of the HasQueryFilter method in EF Core, and when would you use it?

• What it tests: Knowledge of global query filters and use cases.
• Answer: The HasQueryFilter method allows you to define a global query filter for entities that applies to all queries. This can be useful for features like soft deletes (e.g., modelBuilder.Entity<MyEntity>().HasQueryFilter(e => !e.IsDeleted);), multi-tenant applications, or filtering sensitive data in a consistent manner.

41. How do you implement and configure composite keys in EF Core?

• What it tests: Understanding of complex primary key configurations.
• Answer: In EF Core, composite keys can be implemented using the Fluent API with the HasKey method. For example: modelBuilder.Entity<MyEntity>().HasKey(e => new { e.KeyPart1, e.KeyPart2 });. This is used when the primary key consists of multiple columns.

42. How does EF Core handle migrations when working in a team environment?

• What it tests: Collaboration and version control best practices in EF Core.
• Answer: In a team environment, migrations should be carefully managed to avoid conflicts. Common practices include creating separate branches for different features, generating and applying migrations individually, and regularly checking for conflicts. Developers should apply migrations in sequence (i.e., after pulling the latest changes) and resolve any merge issues manually if conflicts arise.

43. What are the implications of using DbContext in a multi-threaded environment?

• What it tests: Understanding thread safety in EF Core.
• Answer: DbContext is not thread-safe, so it should not be used across multiple threads. It is designed to be used within a single thread and a unit of work pattern. For multi-threaded scenarios, you should either use separate DbContext instances for each thread or use asynchronous operations to avoid blocking the main thread.

44. What is the significance of ValueConverter in EF Core, and when would you use it?

• What it tests: Knowledge of custom data types and conversion strategies.
• Answer: ValueConverter allows you to convert property values between different representations in the application and the database. It’s useful when you want to store complex types (like enums, or JSON objects) as simple database types (such as strings or integers). For example: modelBuilder.Entity<MyEntity>().Property(e => e.MyEnum).HasConversion<string>();.

45. What is the best approach to handling large-scale data imports/exports with EF Core?

• What it tests: Knowledge of handling bulk operations and performance.
• Answer: For large data imports/exports, EF Core’s change tracking can be a bottleneck. It's best to:
  • Use raw SQL or bulk libraries (e.g., EFCore.BulkExtensions) for bulk insert/update/delete operations.
  • Use AsNoTracking to improve read performance when importing/exporting large datasets.
  • Break large datasets into smaller batches to avoid memory overload and timeout issues.
  • Minimize the use of SaveChanges() and execute bulk operations directly with optimized SQL queries.

46. How can you implement soft deletes with related entities in EF Core?

• What it tests: Handling soft deletes with complex entity relationships.
• Answer: Soft deletes can be implemented by adding a IsDeleted boolean column and filtering on that column using global query filters. For related entities, you can set up cascading soft deletes or manually update the IsDeleted field for related entities. Example: modelBuilder.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);.

47. What are some performance best practices when using Include in EF Core?

• What it tests: Performance optimizations for eager loading.
• Answer: Include is useful for eager loading related entities, but it can impact performance when used excessively. Best practices include:
  • Limiting the number of Include statements to only what’s necessary.
  • Using ThenInclude for deep relationships and only loading the necessary navigation properties.
  • Breaking large, complex queries into smaller queries to reduce data load.
  • Using projection (Select) to load only the required fields.

48. How can you implement paging with EF Core, and what are some potential pitfalls?

• What it tests: Understanding paging and performance with large datasets.
• Answer: Paging can be implemented with Skip() and Take() methods. Example: dbContext.Orders.Skip(pageIndex * pageSize).Take(pageSize);. Pitfalls include:
  • Not using an OrderBy clause, which could lead to non-deterministic paging.
  • Loading too many records at once, which can result in performance degradation.
  • Using Skip() with large offsets in very large datasets may lead to inefficient query plans.

49. How does EF Core handle database transactions, and how would you manually control a transaction?

• What it tests: Understanding of transaction management in EF Core.
• Answer: EF Core automatically wraps operations in a transaction when calling SaveChanges(). You can manually control transactions using BeginTransaction(), Commit(), and Rollback() methods. Example:

  using (var transaction = dbContext.Database.BeginTransaction())
  {
      try
      {
          // Perform database operations
          dbContext.SaveChanges();
          transaction.Commit();
      }
      catch
      {
          transaction.Rollback();
          throw;
      }
  }
  

50. What are the implications of using DbContext in a long-running application (e.g., a background service or console app)?

• What it tests: Best practices for long-lived DbContext instances.
• Answer: DbContext is designed to be short-lived and used within a unit of work. In long-running applications, it’s important to manage the lifecycle of DbContext carefully to avoid memory leaks and unnecessary resource consumption. It’s best to create a new DbContext instance per operation (or per task) and ensure that it is disposed of after use. If the DbContext is used repeatedly in background services, ensure it’s scoped correctly and disposed properly to avoid connection leaks.

51. How do you configure relationships between entities in EF Core, especially many-to-many relationships?

• What it tests: Understanding complex relationships in EF Core.
• Answer: In EF Core 5 and later, many-to-many relationships are handled automatically without the need for an explicit join entity. However, for earlier versions, you need to create a join table entity with two foreign keys. Example:

  modelBuilder.Entity<Student>()
      .HasMany(s => s.Courses)
      .WithMany(c => c.Students)
      .UsingEntity<StudentCourse>(
          j => j.HasOne(sc => sc.Course).WithMany().HasForeignKey(sc => sc.CourseId),
          j => j.HasOne(sc => sc.Student).WithMany().HasForeignKey(sc => sc.StudentId)
      );
  

52. What are DbSet<TEntity>.Find and DbSet<TEntity>.SingleOrDefault used for, and when would you use each?

• What it tests: Understanding query methods in EF Core.
• Answer: Find is optimized for retrieving an entity by its primary key, and it will return null if not found. It’s faster because it uses the primary key index. SingleOrDefault is used for querying by any condition and expects a single result or null. Use Find when querying by primary key and SingleOrDefault when querying by other unique conditions.

53. What is the role of AsQueryable in EF Core, and how does it differ from ToList or ToArray?

• What it tests: Understanding of query execution strategies.
• Answer: AsQueryable allows you to keep a query in an IQueryable state, meaning the query is not executed until it is enumerated (e.g., by calling ToList, ToArray, FirstOrDefault). This allows for deferred execution and additional query transformations before execution. ToList or ToArray immediately executes the query and materializes the result into a collection.

54. How can you perform a batch delete or update in EF Core efficiently?

• What it tests: Knowledge of bulk operations and performance optimizations.
• Answer: EF Core does not natively support batch operations (delete or update) out of the box for efficiency. However, libraries like EFCore.BulkExtensions can be used to perform bulk operations efficiently. Another option is using raw SQL commands to execute batch delete or update statements directly against the database.

55. How would you implement hierarchical data structures (e.g., trees) in EF Core?

• What it tests: Knowledge of handling recursive relationships.
• Answer: Hierarchical data structures, like trees, can be represented in EF Core using one of the following strategies:
  • Self-referencing entities: Create a ParentId foreign key in the same entity to link each node to its parent (e.g., an Employee entity with a ManagerId).
  • Adjacency list pattern: Similar to self-referencing, where each row holds a reference to its parent (e.g., Category -> ParentCategory).
  • Closure table: Use a separate table to store relationships between nodes (e.g., CategoryHierarchy table with ParentCategoryId and ChildCategoryId).
  • Path enumeration: Store the entire path in each row (e.g., CategoryPath as a string or a list of IDs).

56. What are the implications of enabling lazy loading in EF Core, and how do you enable it?

• What it tests: Knowledge of lazy loading behavior and its impact on performance.
• Answer: Lazy loading in EF Core is a mechanism that loads related entities when accessed. It is enabled by setting up navigation properties as virtual and adding a proxy library (e.g., Microsoft.EntityFrameworkCore.Proxies). While it simplifies development, lazy loading can lead to the N+1 query problem, causing performance degradation when accessing multiple related entities. To optimize, use eager loading (Include) or explicit loading instead.

57. How can you prevent N+1 query issues when using Include in EF Core?

• What it tests: Optimization and understanding of query performance.
• Answer: The N+1 query issue arises when you have a query that loads a collection, and for each item in the collection, EF Core issues a separate query. To avoid this:
  • Use Include and ThenInclude to eagerly load related entities in a single query.
  • Use Select to project only the needed data and reduce the number of included properties.
  • For large collections, consider using explicit loading or filtering to reduce the amount of data retrieved.

58. What is the purpose of DbContext.Database.ExecuteSqlRaw in EF Core, and when should you use it?

• What it tests: Knowledge of raw SQL execution.
• Answer: ExecuteSqlRaw allows you to execute raw SQL queries directly against the database. It is typically used for executing non-query SQL commands such as INSERT, UPDATE, DELETE, or stored procedures. It should be used when EF Core’s LINQ methods are not sufficient or when you need to perform bulk operations.

59. How do you manage migrations in a production environment with multiple developers?

• What it tests: Experience with migrations in collaborative development.
• Answer: In a team environment, migrations should be managed carefully to avoid conflicts:
  • Developers should always pull the latest changes and run migrations frequently.
  • Use a consistent naming convention for migration files to avoid conflicts.
  • If conflicts arise, developers should resolve them manually by merging migration files or creating a new migration after resolving issues.
  • Prefer to generate migrations for specific features or changes and apply them incrementally.
  • Use a migration script (e.g., dotnet ef migrations script) to generate a SQL script that can be applied in production.

60. What are the potential problems with using DbContext in a stateless web application (e.g., ASP.NET Core)?

• What it tests: Understanding of managing DbContext in web applications.
• Answer: DbContext is designed to be short-lived and should be disposed of after each HTTP request. In a stateless application like ASP.NET Core, you should use dependency injection to inject a new DbContext for each request (scoped lifetime). Problems can arise if the DbContext is incorrectly cached or reused across requests, leading to issues with change tracking, memory leaks, and stale data.

61. How can you use raw SQL queries to insert, update, or delete records in EF Core?

• What it tests: Ability to execute custom SQL queries.
• Answer: EF Core allows you to execute raw SQL queries for insert, update, or delete operations. For non-query commands, use ExecuteSqlRaw or ExecuteSqlInterpolated:

  dbContext.Database.ExecuteSqlRaw("UPDATE Products SET Price = {0} WHERE CategoryId = {1}", newPrice, categoryId);
  

  For queries returning entities or collections, you can use FromSqlRaw:

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

62. How can you implement "caching" strategies with EF Core, and what are some pitfalls?

• What it tests: Understanding of caching and performance considerations.
• Answer: Caching in EF Core can be implemented using in-memory caching, distributed caching, or a custom caching solution:
  • First-level cache: EF Core automatically caches entities within the same DbContext instance.
  • Second-level cache: You can use external caching libraries like EFCore.SecondLevelCacheInterceptor to cache results across multiple DbContext instances.
  • Pitfalls: Caching can lead to stale data, so it's essential to handle cache invalidation carefully. Overuse of caching may lead to high memory consumption, especially with large datasets.

63. What are Owned Entities in EF Core, and when would you use them?

• What it tests: Understanding of owned entity types in EF Core.
• Answer: Owned entities are entities that don’t have their own identity and are owned by another entity. They are stored in the same table as the owner entity, and their lifecycle is tied to the owning entity. Owned entities are useful when you want to model a value object or a part of a complex entity (e.g., an Address owned by a User). In EF Core, you configure owned entities using the OwnsOne or OwnsMany methods in the OnModelCreating method.

64. How can you manage concurrency conflicts in EF Core?

• What it tests: Understanding concurrency control mechanisms in EF Core.
• Answer: EF Core supports optimistic concurrency control. You can manage concurrency by adding a RowVersion or Timestamp property to the entity. EF Core will automatically check the value of the RowVersion when saving an entity. If the value has changed since it was loaded, a DbUpdateConcurrencyException is thrown, and the developer can handle the conflict by informing the user, retrying the operation, or merging changes.

65. What is the difference between FirstOrDefault and SingleOrDefault in EF Core, and when would you use each?

• What it tests: Understanding query semantics and usage.
• Answer: FirstOrDefault returns the first entity that matches the condition, or null if no matching entity is found. SingleOrDefault returns a single entity or null, but it will throw an exception if there are multiple entities that match the condition. Use FirstOrDefault when you expect zero or one result, and SingleOrDefault when you expect exactly one or no result (e.g., querying by a unique identifier).

66. How can you configure a DbContext to use a specific schema in EF Core?

• What it tests: Knowledge of schema configurations in EF Core.
• Answer: You can specify the schema for an entity using the Fluent API in the OnModelCreating method:

  modelBuilder.Entity<MyEntity>().ToTable("MyEntity", "MySchema");
  

  This will map MyEntity to the table MyEntity in the MySchema schema.

67. What are the advantages and disadvantages of using a DbSet for querying related entities in EF Core?

• What it tests: Understanding DbSet operations and performance.
• Answer: DbSet allows you to query related entities using LINQ, but it can lead to performance issues if you don't control the amount of data being loaded. The disadvantage is the potential for excessive database queries, especially with large datasets or complex relationships. Best practices include using .Include() for eager loading, .AsNoTracking() for read-only queries, and .ToList() to trigger execution earlier in the query lifecycle.

Here are even more advanced EF Core interview questions, delving into intricate aspects of database interactions, performance optimization, and advanced use cases:

68. How do you implement "multi-tenancy" in EF Core?

• What it tests: Knowledge of managing multi-tenant applications.
• Answer: Multi-tenancy can be implemented in EF Core using a few strategies:
  • Schema-based tenancy: Each tenant has a separate schema in the same database.
  • Database-based tenancy: Each tenant has its own database, with each DbContext connected to a specific database.
  • Table-based tenancy: All tenants share the same schema and database, but a TenantId column is used to separate tenant-specific data. You can use HasQueryFilter to ensure that data for each tenant is filtered automatically.

69. What is a Model Snapshot in EF Core, and what role does it play in migrations?

• What it tests: Understanding EF Core migration mechanisms.
• Answer: The Model Snapshot is a file in the migrations folder that represents the state of the model at the time of the last migration. It serves as a point of reference for EF Core when comparing the current model with the model defined in the migration. When you add new migrations, EF Core compares the current model to the Model Snapshot and generates the necessary SQL.

70. Explain the difference between DbContext.SaveChanges and DbContext.SaveChangesAsync. When would you prefer one over the other?

• What it tests: Knowledge of asynchronous programming with EF Core.
• Answer: SaveChanges is synchronous, blocking the thread until the changes are committed to the database, while SaveChangesAsync is asynchronous and doesn't block the thread, allowing other tasks to be processed. You should use SaveChangesAsync in an application that requires non-blocking operations, such as web applications or APIs, to improve responsiveness and scalability.

71. How do you handle DbContext in a distributed or microservices architecture?

• What it tests: Understanding of distributed systems and managing EF Core in microservices.
• Answer: In a microservices architecture, each microservice should have its own DbContext and database. It allows services to be decoupled and independently scaled. For distributed transactions, you can use patterns like saga or eventual consistency to manage state across services. A popular solution for transaction management across services is the Outbox Pattern or using event-driven architecture with messaging systems like Kafka or RabbitMQ.

72. What are Shadow Properties in EF Core, and how do you use them?

• What it tests: Knowledge of EF Core features and flexibility.
• Answer: Shadow properties are properties that are not defined in the entity class but are still part of the model and mapped to the database. You can define them using the Fluent API:

  modelBuilder.Entity<MyEntity>().Property<string>("ShadowProperty");
  

  Shadow properties are useful for scenarios where you need to store extra data that is not part of your entity model but still needs to be persisted in the database, like auditing fields or extra metadata.

73. How would you handle database versioning and migrations in a continuous integration (CI) pipeline with EF Core?

• What it tests: Experience in DevOps and database migrations.
• Answer: To handle migrations in CI:
  • Ensure that migrations are generated and applied automatically: Integrate migration generation (dotnet ef migrations add) and application (dotnet ef database update) into the CI pipeline.
  • Use a consistent environment: Migrations should be applied to a test/staging environment before deployment to production. This can be done using a separate database for each environment.
  • Pre- and post-deployment checks: Include checks to verify that migrations were applied successfully and database schemas are in sync.
  • Versioning: Keep track of all migrations in version control and avoid directly modifying them once committed.

74. What is the difference between IQueryable and IEnumerable in the context of EF Core, and when should you use each?

• What it tests: Understanding the query execution flow in EF Core.
• Answer: IQueryable is used for deferred execution and represents a query that can be further modified before execution. It is designed for queries that will be executed against the database. IEnumerable represents a collection of objects that have been loaded into memory, so it is typically used after the query has been executed (usually in-memory). Use IQueryable for querying data from the database and IEnumerable when working with in-memory collections.

75. Explain how to perform complex projections in EF Core using Select and Anonymous Types.

• What it tests: Understanding of data transformation and projection.
• Answer: Projections allow you to transform data during querying. In EF Core, you can use Select to project the data into anonymous types or custom DTOs. For example:

  var result = dbContext.Orders
      .Where(o => o.Status == "Shipped")
      .Select(o => new 
      {
          o.OrderId,
          o.Customer.Name,
          TotalAmount = o.Items.Sum(i => i.Price)
      }).ToList();
  

  This reduces the amount of data being loaded into memory and optimizes performance by retrieving only the necessary fields.

76. How do you handle database schema changes with EF Core in production?

• What it tests: Experience with managing production databases and schema evolution.
• Answer: Managing schema changes in production with EF Core involves:
  • Incremental migrations: Apply schema changes incrementally and test in staging environments before applying to production.
  • Version control: Ensure migrations are versioned and stored in the source code repository.
  • Backup: Always back up the production database before applying any migration.
  • Data migration scripts: If migrations involve data changes (e.g., splitting columns or updating values), consider writing custom SQL scripts to ensure data consistency.
  • Rolling deployment: Apply migrations during off-peak hours or in a rolling deployment fashion to minimize downtime.

77. What are the differences between EF Core 6 and earlier versions like EF Core 5?

• What it tests: Awareness of EF Core version differences and new features.
• Answer: EF Core 6 introduces several important features and improvements over EF Core 5:
  • Many-to-many relationships: EF Core 6 improved many-to-many relationship handling without requiring a join entity.
  • Bulk insert improvements: Performance improvements for bulk operations.
  • Improved performance: EF Core 6 includes multiple performance optimizations, particularly around query translation and materialization.
  • Temporal tables: EF Core 6 introduces support for temporal tables, enabling the tracking of historical data.
  • Additional SQL Server features: Expanded support for SQL Server-specific features like SQL Server SEQUENCE.

78. What are TransactionScopes in EF Core, and when would you use them?

• What it tests: Knowledge of advanced transaction management.
• Answer: TransactionScope provides a way to manage transactions across multiple database contexts or even multiple resource types (e.g., databases, message queues). It ensures that multiple operations are committed or rolled back together in a distributed system. You would use it in scenarios where you need to manage a transaction across multiple resources or databases in a consistent manner. However, TransactionScope can lead to issues with EF Core, especially in async scenarios, due to default behavior that may escalate transactions to the distributed transaction level, which can have performance implications.

79. What is EFCore.BulkExtensions, and how does it improve performance in EF Core?

• What it tests: Familiarity with third-party tools and performance improvements.
• Answer: EFCore.BulkExtensions is a third-party library that provides bulk operations (insert, update, delete) for EF Core. It significantly improves performance by bypassing the change tracker and executing SQL commands directly for bulk operations. This is particularly useful when dealing with large datasets, as it reduces the overhead of tracking and multiple SaveChanges() calls.

80. How do you implement data validation with EF Core, especially for complex validation scenarios?

• What it tests: Understanding of validation strategies in EF Core.
• Answer: Data validation in EF Core can be handled in several ways:
  • Data Annotations: Use attributes like [Required], [MaxLength], etc., on model properties for simple validation.
  • Fluent API: Use modelBuilder in the OnModelCreating method for more complex validation rules (e.g., complex relationships, custom constraints).
  • Custom Validation Logic: Implement IValidatableObject for entities requiring complex validation rules, or use domain-driven design principles with services that perform validation logic.

81. Explain the HasConversion method in EF Core and when you might use it.

• What it tests: Knowledge of custom data type conversions.
• Answer: HasConversion is used to specify how a property should be stored in the database. It's commonly used to map complex types to simpler database types (e.g., enums to integers, DateTime to strings). For example, to store an enum as an integer in the database:

  modelBuilder.Entity<MyEntity>()
      .Property(e => e.Status)
      .HasConversion<int>();
  

Here are even more advanced EF Core interview questions, focusing on deeper aspects of EF Core, real-world problem solving, advanced use cases, and performance optimization strategies:

82. What is the role of ValueConverters in EF Core, and how do you use them?

• What it tests: Understanding of custom data conversion in EF Core.
• Answer: ValueConverters in EF Core are used to define how an entity property is converted when it is stored in or retrieved from the database. You can use them to transform data types (e.g., convert a complex type to a primitive type or vice versa) before EF Core interacts with the database. They are configured with the HasConversion method or when defining a model. For example, to store an Enum as a string:

  modelBuilder.Entity<MyEntity>()
      .Property(e => e.MyEnum)
      .HasConversion(
          v => v.ToString(),
          v => (MyEnum)Enum.Parse(typeof(MyEnum), v)
      );
  

83. What is the impact of AsNoTracking in EF Core, and when should you use it?

• What it tests: Understanding query performance and optimization.
• Answer: AsNoTracking is used to instruct EF Core not to track changes to the entities that are returned from the query. This improves performance by skipping change tracking, making it ideal for read-only operations. It is especially useful when you don’t need to update or modify the retrieved entities (e.g., displaying data in a read-only view). It reduces memory usage and increases query performance by not storing the entities in the change tracker.

84. How do you handle lazy loading in EF Core, and what are its limitations?

• What it tests: Knowledge of lazy loading behavior and its pros/cons.
• Answer: Lazy loading in EF Core is achieved by using the Microsoft.EntityFrameworkCore.Proxies package and making navigation properties virtual. EF Core will automatically load related entities when accessed. However, it has limitations:
  • N+1 query problem: Lazy loading may result in multiple database queries (one for each related entity), leading to performance issues.
  • Control over queries: It may introduce unintended database hits that developers don't control explicitly. To mitigate, use eager loading (Include) or explicit loading for more control over when related data is loaded.

85. How do you handle complex types or value objects in EF Core?

• What it tests: Experience with domain-driven design (DDD) and modeling complex types.
• Answer: EF Core supports modeling complex types (value objects) using owned entities. A complex type is a part of an entity and doesn’t have its own identity, often representing domain objects like an address, phone number, or money. Complex types are configured using the OwnsOne or OwnsMany methods in OnModelCreating:

  modelBuilder.Entity<Order>()
      .OwnsOne(o => o.Address);
  

  This would map the Address class as an owned type, stored within the Order table.

86. How do you configure a composite primary key in EF Core?

• What it tests: Understanding of composite keys and modeling strategies.
• Answer: In EF Core, a composite primary key can be configured using the HasKey method in the OnModelCreating method. This is commonly used in many-to-many relationships or to represent entities where the combination of multiple fields is unique:

  modelBuilder.Entity<MyEntity>()
      .HasKey(e => new { e.KeyPart1, e.KeyPart2 });
  

87. What are Global Query Filters in EF Core, and how do you implement them?

• What it tests: Knowledge of filtering strategies for global consistency.
• Answer: Global query filters allow you to apply common filters across all queries for a given entity type. They can be used for scenarios such as soft deletes (filtering out deleted entities) or multi-tenancy (filtering by tenant ID). You implement them in OnModelCreating like this:

  modelBuilder.Entity<MyEntity>()
      .HasQueryFilter(e => e.IsActive == true);
  

  This filter will automatically apply to all queries for MyEntity, ensuring only active entities are returned.

88. How do you configure a one-to-one relationship in EF Core?

• What it tests: Understanding of entity relationships.
• Answer: A one-to-one relationship in EF Core is configured using the HasOne and WithOne methods. The key part is ensuring that one of the entities has a foreign key to the other. For example:

  modelBuilder.Entity<Person>()
      .HasOne(p => p.Address)
      .WithOne(a => a.Person)
      .HasForeignKey<Address>(a => a.PersonId);
  

89. How does EF Core handle database migrations with multiple environments (e.g., development, staging, production)?

• What it tests: Experience managing migrations in different environments.
• Answer: EF Core allows you to create and apply migrations per environment. Typically, the migrations are generated in development environments and then applied to staging/production. Some strategies include:
  • Separate databases for each environment: Each environment (dev, staging, production) uses its own database.
  • Environment-specific connection strings: Using different connection strings in each environment to target the correct database.
  • Automating migrations: Running dotnet ef database update automatically during deployment, with manual checks in production.
  • Using SQL scripts: Sometimes, you may need to generate a migration script and manually apply it in production for safety.

90. How do you manage database concurrency and prevent conflicts in EF Core?

• What it tests: Knowledge of concurrency control mechanisms.
• Answer: EF Core supports optimistic concurrency control. You can implement concurrency by adding a RowVersion (timestamp) field to the entity. EF Core will automatically check this version during SaveChanges and throw a DbUpdateConcurrencyException if the version has changed since the entity was loaded:

  public class MyEntity
  {
      public int Id { get; set; }
      public byte[] RowVersion { get; set; } // This is the concurrency token
  }
  

  On encountering a concurrency conflict, you can handle it by informing the user, merging changes, or retrying the operation.

91. What is the Attach method in EF Core, and when would you use it?

• What it tests: Understanding of DbContext state management.
• Answer: The Attach method in EF Core is used to mark an entity as "unmodified" in the DbContext. It is useful when you want to track an entity that was fetched outside of the DbContext (e.g., from another session or from a detached state) but do not want EF Core to treat it as a new entity or require it to be inserted:

  dbContext.Attach(entity);
  dbContext.SaveChanges();
  

92. What is the ChangeTracker in EF Core, and how can it help with performance optimization?

• What it tests: Understanding of EF Core’s internal mechanisms and performance optimization.
• Answer: The ChangeTracker in EF Core tracks the state of entities (e.g., Added, Modified, Deleted, or Unchanged). It allows EF Core to track changes to entities and persist them when SaveChanges is called. However, maintaining this tracking has performance costs. To optimize:
  • Use AsNoTracking for read-only queries to avoid change tracking.
  • Detach entities when they are no longer needed to free up memory.
  • Use explicit loading rather than lazy loading to reduce unnecessary queries.

93. How do you perform custom validation in EF Core?

• What it tests: Knowledge of advanced validation techniques.
• Answer: EF Core supports custom validation through the IValidatableObject interface or custom validation logic using the Fluent API. For example, implementing IValidatableObject:

  public class MyEntity : IValidatableObject
  {
      public string Property { get; set; }
  
      public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
      {
          if (string.IsNullOrEmpty(Property))
          {
              yield return new ValidationResult("Property cannot be empty.");
          }
      }
  }
  

  Alternatively, you can use the Fluent API to enforce constraints or conditions:

  modelBuilder.Entity<MyEntity>()
      .Property(e => e.Property)
      .HasCheckConstraint("CK_MyEntity_Property", "Property IS NOT NULL");
  

94. How does EF Core handle database migrations when you change an entity’s structure (e.g., add/remove properties)?

• What it tests: Understanding of the EF Core migration process.
• Answer: When you change an entity's structure (e.g., add/remove properties), EF Core generates a migration that reflects the schema changes. For example, adding a property will generate a migration that adds a column to the database, and removing a property will generate a migration that drops the corresponding column. Migrations can be customized if needed to handle more complex changes. You can review and modify the generated SQL script to ensure it matches your intentions before applying it to the database.

95. How would you optimize large queries in EF Core to prevent performance issues?

• What it tests: Query optimization and performance best practices.
• Answer: To optimize large queries in EF Core:
  • Eager Loading: Use Include or ThenInclude to reduce the number of queries.
  • Projection: Use Select to return only the required columns, avoiding the fetching of unnecessary data.
  • No Tracking: Use AsNoTracking for read-only queries to avoid change tracking overhead.
  • Pagination: Use .Skip() and .Take() to break down large result sets into smaller chunks.
  • Indexes: Ensure that frequently queried columns are indexed in the database.

96. What is the ExecuteSqlRaw method in EF Core, and how is it used?

• What it tests: Knowledge of raw SQL execution.
• Answer: ExecuteSqlRaw allows you to execute raw SQL queries directly against the database, bypassing EF Core’s LINQ parsing and change tracking. It is used for operations that do not return entities, like INSERT, UPDATE, DELETE, or stored procedures:

  dbContext.Database.ExecuteSqlRaw("UPDATE Products SET Price = {0} WHERE Id = {1}", newPrice, productId); 

Here are even more advanced EF Core interview questions that delve into various intricate aspects, including performance tuning, data consistency, concurrency control, and working with real-world complex scenarios:

97. How does EF Core handle caching, and how can you implement caching in EF Core to improve performance?

• What it tests: Understanding of caching strategies and performance optimization.
• Answer: EF Core does not have built-in caching, but you can implement caching using third-party libraries or custom solutions. One common approach is to use second-level caching:
  • MemoryCache: Store query results or entities in memory for a short duration and return cached results instead of hitting the database.
  • Distributed Caching: Use distributed caches like Redis or NCache when working with distributed systems or multiple application instances.
  • You can also implement your own caching layer using query result caching or context-level caching. Be mindful of invalidation strategies when data changes.

98. How do you implement a soft delete in EF Core?

• What it tests: Familiarity with non-destructive delete strategies.
• Answer: A soft delete involves marking an entity as deleted without actually removing it from the database. You can implement it using a boolean IsDeleted property or a DeletedAt timestamp. EF Core supports this pattern using global query filters to exclude soft-deleted entities from queries:

  public class MyEntity
  {
      public int Id { get; set; }
      public bool IsDeleted { get; set; }
  }
  
  modelBuilder.Entity<MyEntity>()
      .HasQueryFilter(e => !e.IsDeleted);
  

  When soft-deleting an entity, you can set IsDeleted to true or set the DeletedAt date, and the global query filter will ensure that soft-deleted entities are not returned in queries.

99. Explain how EF Core’s Find method works and when you should use it.

• What it tests: Understanding of how EF Core retrieves entities by primary key.
• Answer: The Find method in EF Core is a shortcut for retrieving an entity by its primary key. It first checks the DbContext's change tracker for an existing entity and returns it if found, avoiding a database query. If the entity is not tracked, it issues a query to the database. Find is typically faster for retrieving an entity by its primary key compared to other methods like FirstOrDefault, since it first checks the cache (change tracker) before querying the database.

  var product = dbContext.Products.Find(productId);
  

  Use Find when you need to retrieve an entity based on its primary key and want to avoid unnecessary queries.

100. How do you implement multi-level lazy loading in EF Core, and what are its potential pitfalls?

• What it tests: Understanding lazy loading behavior and potential performance issues.
• Answer: Multi-level lazy loading in EF Core allows related entities to be loaded automatically when accessed. However, it can lead to the N+1 query problem, where multiple queries are issued for each related entity. For example, loading a list of orders and then lazy-loading each order’s items will result in a query for each order.
  • To enable multi-level lazy loading, install the Microsoft.EntityFrameworkCore.Proxies package and make navigation properties virtual:

    public virtual ICollection<Order> Orders { get; set; }
    

  • While lazy loading is convenient, it can lead to performance degradation, especially with deep relationships. It's generally better to use eager loading or explicit loading when dealing with multiple related entities.
  • Use AsNoTracking with lazy loading to avoid change tracking for read-only queries.

101. What is the difference between Include and ThenInclude in EF Core, and how do you use them for eager loading?

• What it tests: Knowledge of eager loading and query composition in EF Core.
• Answer: Include and ThenInclude are used in EF Core to eagerly load related entities:
  • Include loads the related entities at the first level of the relationship.
  • ThenInclude is used to include further nested relationships. It's used when you need to load multiple levels of related data. Example:

  var orders = dbContext.Orders
      .Include(o => o.Customer)
      .ThenInclude(c => c.Address)
      .ToList();
  

  This query will eagerly load Orders, Customer, and Address in a single query, reducing the number of database round trips.

102. What is a DbSet<TEntity> in EF Core, and how does it relate to entity operations?

• What it tests: Basic understanding of EF Core’s DbSet and its role.
• Answer: A DbSet<TEntity> represents a collection of all entities of type TEntity in the context. It is the primary way to interact with entities in the database, and EF Core uses it to perform CRUD operations. The DbSet provides methods like Add, Remove, Update, Find, FirstOrDefault, ToList, etc., for querying and modifying the data. The DbContext manages the lifecycle of DbSet objects and tracks the changes to entities.

103. What is the difference between DbContext.SaveChanges() and DbContext.SaveChangesAsync()?

• What it tests: Understanding of synchronous vs asynchronous operations.
• Answer: SaveChanges is a synchronous method that saves all changes made to the context to the database and blocks the calling thread until the operation is completed. SaveChangesAsync is an asynchronous method that performs the same operation but does not block the calling thread, making it more efficient in applications that require responsiveness, like web APIs or UI applications. Asynchronous operations are preferred when working with I/O-bound tasks, as they improve scalability and responsiveness.

104. How do you manage the lifecycle of a DbContext in an ASP.NET Core application?

• What it tests: Understanding of DbContext's lifespan and dependency injection in ASP.NET Core.
• Answer: In an ASP.NET Core application, the lifecycle of a DbContext is managed via dependency injection (DI). Typically, the DbContext is registered as scoped, meaning a new instance is created for each request but shared within the request. This ensures that the DbContext is not shared across requests, preventing concurrency issues. The DI container automatically handles DbContext instantiation and disposal.

  services.AddDbContext<MyDbContext>(options =>
      options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
  

  It’s important to ensure that DbContext is scoped, as using singleton or transient lifetimes for DbContext can lead to issues with connection pooling and tracking entities.

105. What is a Tracking Query in EF Core, and how does it differ from a No-Tracking Query?

• What it tests: Knowledge of query execution and performance optimization.
• Answer:
  • A tracking query is one where EF Core tracks changes to the entities returned by the query. This means that the DbContext keeps track of the entities' state, and any modifications will be persisted in the database when SaveChanges() is called.
  • A no-tracking query is one where EF Core does not track changes to the entities. This improves performance by reducing memory usage and is useful when entities are not modified and are only used for read operations. You can use AsNoTracking() to make a query no-tracking:

    var products = dbContext.Products.AsNoTracking().ToList();
    

106. What is Table Splitting in EF Core, and how would you use it?

• What it tests: Understanding of advanced mapping scenarios.
• Answer: Table splitting is a technique where multiple entities are mapped to the same table in the database. It’s useful when you have related entities that share some properties and you want to optimize storage or performance by storing them in a single table. EF Core supports table splitting using the ToTable method:

  modelBuilder.Entity<Order>()
      .ToTable("Orders");
  
  modelBuilder.Entity<OrderDetail>()
      .ToTable("Orders"); // Share the same table as Order
  

107. How do you handle database migrations for an evolving schema in a microservices architecture?

• What it tests: Understanding of managing database changes in a microservices setup.
• Answer: In a microservices architecture, each service typically has its own database, and migrations should be handled independently for each microservice. To manage migrations effectively:
  • Automate migrations in each service’s CI/CD pipeline to apply them to the correct environment (e.g., staging/production).
  • Backward compatibility: Ensure that migrations are backward compatible by adding new columns or tables without removing old ones. This allows services to continue to function even when other services are still running with an older schema.
  • Versioned API: Use API versioning to accommodate changes in database schemas.
  • Database migration scripts: In some cases, especially with shared databases, you may use raw SQL migration scripts to handle schema changes more carefully and avoid breaking changes across services.

108. What is the difference between Lazy Loading, Eager Loading, and Explicit Loading in EF Core?

• What it tests: Knowledge of different strategies for loading related data.
• Answer:
  • Lazy Loading: Related data is loaded automatically when a navigation property is accessed for the first time. It can lead to the N+1 query problem, where one query per related entity is executed.
  • Eager Loading: All related entities are loaded upfront using the Include method, typically in one query. It’s more efficient than lazy loading in scenarios with multiple related entities.
  • Explicit Loading: Related data is explicitly loaded after the entity has been retrieved. It allows for more fine-grained control over when related data is loaded and can be done using the Load method.

109. How do you configure a stored procedure in EF Core, and how do you execute it?

• What it tests: Understanding of executing stored procedures with EF Core.
• Answer: EF Core allows executing stored procedures using FromSqlRaw for queries or ExecuteSqlRaw for commands. You can configure EF Core to map the result of a stored procedure to entities, and you can pass parameters

for stored procedures: csharp var orders = dbContext.Orders .FromSqlRaw("EXEC GetOrdersByCustomer {0}", customerId) .ToList();

110. What are the potential issues with Database First approach in EF Core?

• What it tests: Awareness of limitations and trade-offs in database-first development.
• Answer: While EF Core supports both Code First and Database First, there are some potential issues with Database First:
  • Harder to keep schema and model in sync: Changes to the database schema might not easily reflect in the entity models.
  • Complexity in handling migrations: When the schema evolves, migrations might not be as smooth since the models are generated from the database.
  • Lack of flexibility in model design: The design of the database might not always follow best practices for object-oriented design, requiring significant adjustments in the code.

111. What are Shadow Properties in EF Core, and how do you use them?

• What it tests: Understanding of advanced mapping features in EF Core.
• Answer: Shadow properties are properties that exist in the model but are not defined in the entity class. They are useful for situations where you need to store additional information in the database without changing the entity class itself. You can define them using the modelBuilder in the OnModelCreating method:

  modelBuilder.Entity<Product>()
      .Property<DateTime>("CreatedAt")
      .HasDefaultValueSql("GETDATE()");
  

  Shadow properties are not accessible directly from the entity class but can be accessed via the Entry method:

  var createdAt = dbContext.Entry(product).Property("CreatedAt").CurrentValue;
  

112. What is the purpose of Fluent API in EF Core, and when would you use it over data annotations?

• What it tests: Familiarity with configuration methods in EF Core.
• Answer: The Fluent API is a more flexible and powerful way to configure the model in EF Core. You use it in the OnModelCreating method to configure the model, relationships, constraints, and indexes. It is preferred over data annotations when you need more control, such as configuring complex relationships or when the configuration logic must be separated from the entity class. The Fluent API is also preferred when working with non-POCO entities or when you need to configure mappings that cannot be achieved with data annotations.

113. What are the potential problems with using a DbContext in a singleton or static context?

• What it tests: Understanding of the lifecycle and thread safety of DbContext.
• Answer: A DbContext should never be used as a singleton or static because it is not thread-safe and is intended to be used for a single unit of work. The DbContext maintains an internal cache (change tracker), and using a singleton or static DbContext would lead to concurrency issues, inconsistent data, and potentially memory leaks. In a typical web application, the DbContext should be scoped to the lifecycle of an HTTP request (i.e., registered as scoped in DI).

114. How do you implement concurrency control in EF Core?

• What it tests: Understanding of handling data conflicts in multi-user environments.

• Answer: EF Core supports two types of concurrency control: optimistic and pessimistic concurrency.

  • Optimistic Concurrency: Involves adding a concurrency token (usually a Timestamp or RowVersion field) to the entity. When a record is updated, EF Core compares the current value with the original value. If they don’t match, a concurrency exception is thrown.

  public class Product
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public byte[] RowVersion { get; set; }
  }
  

  • In the model configuration:

  modelBuilder.Entity<Product>()
      .Property(p => p.RowVersion)
      .IsRowVersion();
  

  • Pessimistic Concurrency: This involves locking the row during the transaction to prevent other users from modifying it at the same time. EF Core doesn't provide built-in support for pessimistic concurrency, but you can use raw SQL queries with locking hints, such as FOR UPDATE.

115. What is the significance of Value Conversion in EF Core, and when would you use it?

• What it tests: Knowledge of data transformations in EF Core.
• Answer: Value conversion in EF Core is used to convert data between the database and application model. You can use it to handle non-standard types or formats in the database that need to be converted to .NET types and vice versa. This is useful for scenarios like storing Enum values as integers or converting DateTime values to UTC for consistent storage:

  modelBuilder.Entity<Product>()
      .Property(p => p.Status)
      .HasConversion(
          v => v.ToString(),
          v => (StatusEnum)Enum.Parse(typeof(StatusEnum), v));
  

116. What are Interceptors in EF Core, and how do you use them?

• What it tests: Understanding of request/response interception for custom behaviors.
• Answer: Interceptors allow you to intercept and modify the behavior of EF Core operations, such as SQL queries, save operations, or transactions. They can be used for logging, performance monitoring, or implementing custom behaviors, such as modifying queries or responses before they are sent to the database or after they are received. Example of logging with an interceptor:

  services.AddDbContext<MyDbContext>(options =>
      options.UseSqlServer(connectionString)
         .AddInterceptors(new MyLoggingInterceptor()));
  

  You can implement IInterceptor to capture and modify SQL queries:

  public class MyLoggingInterceptor : DbCommandInterceptor
  {
      public override InterceptionResult<DbDataReader> ReaderExecuting(
          DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
      {
          Console.WriteLine(command.CommandText);
          return base.ReaderExecuting(command, eventData, result);
      }
  }
  

117. How do you handle bulk operations in EF Core (e.g., bulk insert, update, delete)?

• What it tests: Knowledge of handling large data sets and performance optimization.
• Answer: EF Core does not have built-in support for bulk operations, but you can use third-party libraries like EFCore.BulkExtensions to perform bulk insert, update, and delete operations. These libraries optimize database interaction to improve performance when dealing with large amounts of data:

  dbContext.BulkInsert(entities);
  dbContext.BulkUpdate(entities);
  dbContext.BulkDelete(entities);
  

  For smaller operations, you can achieve similar results by batching changes and calling SaveChanges() periodically.

118. What is the role of the Change Tracker in EF Core, and how do you use it?

• What it tests: Understanding of how EF Core tracks entity state and changes.
• Answer: The Change Tracker is responsible for tracking changes to entities in the DbContext and determining which entities need to be saved to the database. It tracks entity states such as Added, Modified, Deleted, and Unchanged. You can use it to check the state of entities:

  var entry = dbContext.Entry(entity);
  var state = entry.State; // Check if Added, Modified, Deleted, or Unchanged
  

  You can also use the ChangeTracker to explicitly modify states (for example, to mark an entity as Modified):

  dbContext.Entry(entity).State = EntityState.Modified;
  

119. What is the difference between Database.Generated and Database.Seed in EF Core?

• What it tests: Knowledge of seeding data in EF Core.
• Answer:
  • Database.Generated refers to automatic values generated by the database, such as auto-incrementing primary keys or default values (like GETDATE() for DateTime).
  • Database.Seed is used to insert initial data into the database when running migrations. You can configure the seed data in the OnModelCreating method or through the HasData method:

  modelBuilder.Entity<Product>()
      .HasData(new Product { Id = 1, Name = "Sample Product" });
  

120. How do you configure and use Owned Entities in EF Core?

• What it tests: Understanding of value types and embedded entities.
• Answer: Owned entities are a way to model value types that do not have their own identity and are always dependent on the parent entity. They are useful for complex types or components that logically belong to a single entity. Owned entities are configured using the OwnsOne method:

  public class Customer
  {
      public int Id { get; set; }
      public Name Name { get; set; }
  }
  
  public class Name
  {
      public string First { get; set; }
      public string Last { get; set; }
  }
  
  modelBuilder.Entity<Customer>()
      .OwnsOne(c => c.Name);
  

  When a parent entity is deleted, its owned entities are also deleted automatically.

121. Explain the concept of Database Sharding and how EF Core can help in implementing it.

• What it tests: Understanding of advanced database scaling strategies.
• Answer: Database sharding is the practice of dividing a large database into smaller, more manageable pieces called shards. Each shard holds a portion of the data and can be stored on a different server or database instance. EF Core does not provide direct support for sharding, but you can implement sharding by manually routing queries to the correct shard using techniques like custom routing or multi-database contexts. You would need to manage which shard to query based on some partitioning key (e.g., UserId).

 

122. What is the purpose of the DbContextOptions in EF Core, and how do you configure it?

• What it tests: Understanding of DbContext configuration and options.
• Answer: The DbContextOptions class is used to configure and pass options to a DbContext instance, such as the database provider, connection string, and other settings. It's typically configured in the Startup.cs (or Program.cs in .NET 6+) using dependency injection:

  services.AddDbContext<MyDbContext>(options =>
      options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
  

  This configuration allows EF Core to use different databases or connection strings based on the environment or application needs.

123. How do you configure a Many-to-Many relationship in EF Core?

• What it tests: Understanding of complex relationships in EF Core.
• Answer: In EF Core 5.0 and later, EF Core supports many-to-many relationships without needing a separate junction table. You define the many-to-many relationship using the HasMany and WithMany methods in the OnModelCreating method. EF Core automatically manages the intermediate table:

  public class Student
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public ICollection<Course> Courses { get; set; }
  }
  
  public class Course
  {
      public int Id { get; set; }
      public string Title { get; set; }
      public ICollection<Student> Students { get; set; }
  }
  
  modelBuilder.Entity<Student>()
      .HasMany(s => s.Courses)
      .WithMany(c => c.Students)
      .UsingEntity(j => j.ToTable("StudentCourses"));
  

124. How do you configure an Index in EF Core, and what are its benefits?

• What it tests: Knowledge of database performance optimization in EF Core.
• Answer: Indexes in EF Core can be created using the Fluent API with HasIndex. Indexes improve query performance, especially for frequent lookups. You can define indexes for single or multiple columns:

  modelBuilder.Entity<Product>()
      .HasIndex(p => p.Name)  // Index on a single column
      .IsUnique();  // Create a unique index
  
  modelBuilder.Entity<Order>()
      .HasIndex(o => new { o.CustomerId, o.OrderDate });  // Composite index
  

  Indexes are critical for ensuring efficient querying, particularly in scenarios involving filters, sorts, or joins on large datasets.

125. What are Composite Keys, and how do you configure them in EF Core?

• What it tests: Understanding of advanced entity key configurations.
• Answer: A composite key is a primary key composed of more than one column, which can be used when no single column can uniquely identify an entity. EF Core allows the configuration of composite keys using the HasKey method:

  public class OrderDetail
  {
      public int OrderId { get; set; }
      public int ProductId { get; set; }
      public int Quantity { get; set; }
  }
  
  modelBuilder.Entity<OrderDetail>()
      .HasKey(od => new { od.OrderId, od.ProductId });  // Composite key
  

  Composite keys are often used in many-to-many relationships or complex scenarios where a single-column key is insufficient.

126. What is the DbSet<TEntity>.AddRange method in EF Core, and how does it impact performance?

• What it tests: Understanding batch operations and performance optimization.
• Answer: AddRange is a method that adds multiple entities to the context in one call, rather than adding them one by one. This can significantly improve performance when dealing with large sets of data, as EF Core can execute a single INSERT statement for all the entities. Here’s how to use it:

  var products = new List<Product>
  {
      new Product { Name = "Product1" },
      new Product { Name = "Product2" }
  };
  dbContext.Products.AddRange(products);
  dbContext.SaveChanges();
  

  It reduces the number of database round trips, making batch operations more efficient.

127. What is the difference between DbSet<TEntity>.Add and DbSet<TEntity>.Attach in EF Core?

• What it tests: Understanding of entity tracking and state management in EF Core.
• Answer:
  • Add: Adds a new entity to the context and marks it as Added. This entity will be inserted into the database when SaveChanges() is called.
  • Attach: Attaches an existing entity to the context without marking it as Added. It is used when the entity already exists in the database, and you don't want to perform an insert but instead want to start tracking changes to the entity. You can modify its properties, and EF Core will track those changes.

  var product = new Product { Id = 1, Name = "Existing Product" };
  dbContext.Products.Attach(product);  // No insert, just tracking changes.
  

128. How can you perform a raw SQL query in EF Core?

• What it tests: Knowledge of executing custom SQL queries in EF Core.
• Answer: EF Core allows you to execute raw SQL queries through the FromSqlRaw method for SELECT queries and ExecuteSqlRaw for non-query operations (like INSERT, UPDATE, or DELETE). Example:

  var products = dbContext.Products
      .FromSqlRaw("SELECT * FROM Products WHERE Name = {0}", productName)
      .ToList();
  

  For non-query commands (e.g., INSERT or UPDATE):

  dbContext.Database.ExecuteSqlRaw("UPDATE Products SET Name = {0} WHERE Id = {1}", newName, productId);
  

129. What is DbContext.SaveChanges() doing under the hood, and how can you optimize it for large datasets?

• What it tests: In-depth knowledge of the internal workings of EF Core and optimization strategies.
• Answer: When SaveChanges() is called, EF Core tracks all changes made to entities (i.e., additions, modifications, deletions) and generates SQL commands to reflect those changes in the database. It performs the following steps:
  1. Change tracking: EF Core checks all entities in the context to identify which are new, modified, or deleted.
  2. SQL generation: EF Core generates corresponding INSERT, UPDATE, and DELETE SQL commands.
  3. Transaction handling: It wraps all the SQL commands in a single transaction.
  To optimize SaveChanges() for large datasets:
  • Batching: Use AddRange and split large operations into smaller batches.
  • Disable change tracking: For read-only operations, use AsNoTracking() to reduce memory overhead.
  • Use bulk operations: Consider third-party libraries like EFCore.BulkExtensions for bulk inserts/updates.

130. What are the trade-offs between using Eager Loading, Lazy Loading, and Explicit Loading?

• What it tests: Understanding of loading strategies and their impact on performance and usability.

• Answer:

  • Eager Loading (Include): Loads related entities along with the main entity in a single query. It’s useful when you need to access related data and want to avoid additional round trips to the database. However, it may cause performance issues if you load unnecessary related data.
  • Lazy Loading: Loads related data on demand (when the navigation property is accessed). It can lead to the N+1 query problem, where multiple queries are made for related data, leading to performance overhead.
  • Explicit Loading: You load related data only when needed, after the initial query. It’s useful when you need fine-grained control over when data is loaded and can be more efficient than lazy loading if used appropriately.

  In general:

  • Eager Loading is ideal when you know you’ll need related data.
  • Lazy Loading is good for less predictable relationships, but be cautious about N+1 issues.
  • Explicit Loading is a flexible option when you want control but don’t want to load everything upfront.

131. What is Value Generation in EF Core, and how is it used?

• What it tests: Understanding of automatic value assignment in EF Core.
• Answer: Value generation refers to the automatic creation of values for properties, typically for primary keys or other system-managed fields like timestamps or unique identifiers. EF Core supports various types of value generation strategies, such as:
  • Database-generated values (e.g., auto-incrementing keys or GUIDs generated by the database):

    modelBuilder.Entity<Product>()
        .Property(p => p.Id)
        .ValueGeneratedOnAdd();  // Database-generated value
    

  • Client-generated values (e.g., setting default values on the client side):

    modelBuilder.Entity<Product>()
        .Property(p => p.Guid)
        .HasDefaultValueSql("NEWID()");  // GUID generated by database
    

132. What is Shadow Foreign Key in EF Core, and how do you use it?

• What it tests: Knowledge of advanced relational mappings in EF Core.
• Answer: A shadow foreign key is a foreign key that exists in the database but is not exposed in the entity class as a navigation property. You can use shadow properties for foreign keys when you want to enforce relationships but don't want to include the foreign key property in your entity class. For example:

  modelBuilder.Entity<Order>()
      .HasOne(o => o.Customer)
      .With
  

Many(c => c.Orders) .HasForeignKey("CustomerId"); // Shadow foreign key ``` This allows you to store the foreign key in the database but keep the entity class clean without exposing unnecessary properties.

133. How does EF Core handle transactions, and how can you manually manage transactions in EF Core?

• What it tests: Understanding of transaction handling and custom management in EF Core.
• Answer: EF Core automatically manages transactions when calling SaveChanges(). If multiple entities are being modified, EF Core wraps them in a single transaction by default. However, you can manually manage transactions using IDbContextTransaction if you need more control, such as when performing multiple database operations outside the scope of SaveChanges():

  using (var transaction = dbContext.Database.BeginTransaction())
  {
      try
      {
          dbContext.Add(new Product { Name = "Product1" });
          dbContext.SaveChanges();
          
          dbContext.Add(new Product { Name = "Product2" });
          dbContext.SaveChanges();
          
          transaction.Commit();
      }
      catch (Exception)
      {
          transaction.Rollback();
      }
  }
  

  Manual transactions are useful in scenarios where you need to execute multiple commands and want to ensure all or nothing gets committed.

134. What is Lazy Loading in EF Core, and how do you enable or disable it?

• What it tests: Understanding lazy loading and its potential impact on performance.
• Answer: Lazy loading is a feature in EF Core that allows related entities to be loaded automatically when they are accessed for the first time. To enable lazy loading, you need to install the Microsoft.EntityFrameworkCore.Proxies package and configure it in the Startup.cs or Program.cs file:

  services.AddDbContext<MyDbContext>(options =>
      options.UseLazyLoadingProxies().UseSqlServer(connectionString));
  

  Then, ensure that the navigation properties in your entities are marked as virtual:

  public class Order
  {
      public int Id { get; set; }
      public virtual Customer Customer { get; set; }
  }
  

  Lazy loading can lead to the N+1 query problem if not handled carefully, as it triggers a new query for each related entity accessed. You can disable lazy loading by not calling UseLazyLoadingProxies() in your configuration.

135. How does EF Core handle SQL injection, and how can you prevent it?

• What it tests: Understanding of security best practices in EF Core.
• Answer: EF Core uses parameterized queries to prevent SQL injection. It automatically escapes user input, ensuring that values passed to SQL queries are treated as parameters rather than part of the query itself. This prevents attackers from injecting malicious SQL:

  var products = dbContext.Products
      .Where(p => p.Name == userInput)
      .ToList();
  

  In this example, EF Core will automatically treat userInput as a parameter, not part of the SQL query. To further prevent SQL injection, always use LINQ or parameterized queries, and avoid raw SQL queries with concatenated strings.

136. What is Change Tracker in EF Core, and how can you access and use it?

• What it tests: In-depth knowledge of the change tracking mechanism in EF Core.
• Answer: The Change Tracker in EF Core is responsible for tracking the state of entities (Added, Modified, Deleted, or Unchanged). It allows EF Core to detect changes and generate the corresponding SQL commands when calling SaveChanges(). You can access and interact with the Change Tracker via dbContext.ChangeTracker:

  var entry = dbContext.Entry(product);
  var currentState = entry.State;  // Tracks state: Added, Modified, Deleted, etc.
  

  You can also use the ChangeTracker to get the original and current values of an entity:

  var originalValue = entry.OriginalValues["Name"];
  var currentValue = entry.CurrentValues["Name"];
  

  The Change Tracker is useful when you need to inspect or modify the tracking behavior for specific entities.

137. What is the difference between DbContext.SaveChanges() and DbContext.SaveChangesAsync()?

• What it tests: Understanding of synchronous vs. asynchronous operations in EF Core.
• Answer:
  • SaveChanges() is a synchronous method that saves changes to the database in a blocking fashion.
  • SaveChangesAsync() is an asynchronous method that saves changes to the database in a non-blocking manner, allowing other operations to continue while the save operation is being performed. The async method is preferred in modern applications, especially in web applications, to avoid blocking threads and improve scalability:

    await dbContext.SaveChangesAsync();
    

138. How does EF Core handle concurrency, and how do you handle optimistic concurrency?

• What it tests: Understanding of concurrency control and how to implement it in EF Core.
• Answer: EF Core supports optimistic concurrency using concurrency tokens like RowVersion or Timestamp. When an entity is updated, EF Core checks whether the data has been modified by another user in the meantime by comparing the concurrency token (e.g., RowVersion):

  public class Product
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public byte[] RowVersion { get; set; }
  }
  

  In the OnModelCreating method, you configure it as a RowVersion property:

  modelBuilder.Entity<Product>()
      .Property(p => p.RowVersion)
      .IsRowVersion();
  

  During an update, EF Core will throw a DbUpdateConcurrencyException if it detects a concurrency conflict, allowing you to handle the conflict in your application, such as prompting the user to overwrite changes or reload the data.

139. What is the role of Seed Data in EF Core, and how do you implement it?

• What it tests: Knowledge of initializing database data in EF Core.
• Answer: Seed data in EF Core refers to the initial data inserted into the database when the application is first run or when a migration is applied. You can configure seed data in the OnModelCreating method using the HasData method:

  modelBuilder.Entity<Product>()
      .HasData(new Product { Id = 1, Name = "Product1" });
  

  When running migrations, EF Core will automatically insert the seed data if the corresponding table does not already contain data. Seed data is useful for populating look-up tables or initializing default application data.

140. What are the key differences between Eager Loading, Lazy Loading, and Explicit Loading in EF Core?

• What it tests: Understanding of different loading strategies and when to use them.
• Answer:
  • Eager Loading: Fetches related entities as part of the initial query using the Include method. It is useful when you know that you will need the related entities:

    var orders = dbContext.Orders.Include(o => o.Customer).ToList();
    

  • Lazy Loading: Related entities are loaded automatically when accessed for the first time. To enable it, you need to install the Microsoft.EntityFrameworkCore.Proxies package:

    public virtual Customer Customer { get; set; }
    

    This approach can lead to the N+1 query problem if many entities are accessed lazily.
  • Explicit Loading: Allows you to load related entities manually after the initial query:

    dbContext.Entry(order).Collection(o => o.OrderDetails).Load();
    

141. How can you implement Pagination with EF Core queries?

• What it tests: Understanding of efficiently loading large datasets.
• Answer: Pagination helps in limiting the amount of data returned by the query. This is done using Skip and Take in EF Core:

  var pageSize = 10;
  var pageNumber = 2;
  var paginatedProducts = dbContext.Products
      .Skip((pageNumber - 1) * pageSize)
      .Take(pageSize)
      .ToList();
  

  Using Skip and Take ensures that only a subset of records is loaded from the database, making it more efficient when dealing with large datasets.

142. What are Custom Conventions in EF Core, and how do you create them?

• What it tests: Knowledge of advanced configuration options.
• Answer: Custom conventions allow you to define default behaviors or configurations that apply to the entire model. You can create custom conventions in the OnModelCreating method to apply shared configurations to multiple entities:

  modelBuilder.Conventions.Add(
      new MyCustomConvention());
  

  For example, you can create a convention that automatically sets a property to be required for all string properties:

  modelBuilder.Entity<Product>()
      .Property(p => p.Name)
      .IsRequired();
  

143. What are Global Query Filters, and when would you use them?

• What it tests: Understanding of advanced query customization in EF Core.
• Answer: Global Query Filters allow you to define a filter that applies to all queries for a specific entity, ensuring that certain conditions (like soft deletes) are automatically applied without explicitly adding them to each query. They are useful for scenarios like soft delete or multi-tenancy. You can define global query filters in the OnModelCreating method:

  modelBuilder.Entity<Product>()
      .HasQueryFilter(p => !p.IsDeleted);
  

  This ensures that all queries for Product automatically exclude soft-deleted products.

Certainly! Here are even more advanced EF Core interview questions covering complex topics like performance optimization, migrations, concurrency, and practical use cases:

144. How can you optimize database queries in EF Core to avoid the "N+1 Query Problem"?

• What it tests: Understanding of performance optimization and how to avoid common pitfalls.
• Answer: The N+1 query problem occurs when EF Core issues a separate query for each related entity, leading to inefficient database access. To avoid this:
  • Use Eager Loading: Load related entities in a single query using the Include method.

    var orders = dbContext.Orders.Include(o => o.OrderDetails).ToList();
    

  • Use ThenInclude for nested relationships: If you need to load nested related data.

    var customers = dbContext.Customers
        .Include(c => c.Orders)
            .ThenInclude(o => o.OrderDetails)
        .ToList();
    

  • Use Explicit Loading: Load related data only when needed, rather than automatically.

    dbContext.Entry(order).Collection(o => o.OrderDetails).Load();
    

  • AsNoTracking: Use AsNoTracking for read-only queries to prevent change tracking and reduce overhead.

145. What is the purpose of the IsKeySet method in EF Core?

• What it tests: Knowledge of entity tracking in EF Core.
• Answer: The IsKeySet method in EF Core is used to check if the primary key value for an entity has been set. It is typically used to determine whether an entity is being tracked or if it is a new entity.

  var product = dbContext.Products.Find(1);
  if (dbContext.Entry(product).IsKeySet)
  {
      // Entity exists and has a key set
  }
  

  This can be helpful in scenarios where you need to distinguish between new and existing entities before performing operations like update or delete.

146. How can you implement soft deletes in EF Core?

• What it tests: Understanding of implementing common features like soft deletes in EF Core.
• Answer: Soft deletes are typically implemented by adding a flag (such as IsDeleted) to the entity to mark it as deleted without actually removing the record from the database. To implement soft deletes, you can:
  • Add an IsDeleted boolean property to the entity:

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool IsDeleted { get; set; }
    }
    

  • Apply a global query filter in OnModelCreating to automatically exclude soft-deleted entities:

    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);
    

  • When deleting an entity, just set the IsDeleted flag to true:

    var product = dbContext.Products.Find(productId);
    product.IsDeleted = true;
    dbContext.SaveChanges();
    

147. What are the best practices for handling migrations in EF Core in a production environment?

• What it tests: Knowledge of managing migrations in a production environment.
• Answer: Best practices for handling migrations in EF Core in production include:
  • Use version control: Keep migration files in version control to ensure consistency across environments.
  • Avoid direct Update-Database on production: Instead, generate migrations using Add-Migration and apply them to production using dotnet ef database update after testing them in a development or staging environment.
  • Backup the database before applying migrations: Always create a backup of the production database before running migrations.
  • Review migrations carefully: Review generated migrations to ensure they are optimized and do not contain unnecessary changes.
  • Test migrations in staging: Always apply migrations first to a staging environment to ensure they do not break the application.
  • Use SQL-based migrations if necessary: For complex database changes that may not translate well through EF Core migrations, consider using raw SQL migrations.
  • Avoid manual changes to the database schema: EF Core migrations should be the primary method for applying schema changes to maintain consistency.

148. How do you configure custom database types in EF Core (e.g., to support JSON columns or custom data types)?

• What it tests: Knowledge of EF Core's flexibility in handling custom database types.
• Answer: In EF Core, you can configure custom database types using ValueConverter or ValueComparer. For example, if you need to map a JSON column from the database to a .NET object, you can use a ValueConverter to convert between the JSON data and the corresponding object:

  modelBuilder.Entity<MyEntity>()
      .Property(e => e.MyJsonColumn)
      .HasConversion(
          v => JsonConvert.SerializeObject(v), 
          v => JsonConvert.DeserializeObject<MyCustomType>(v));
  

  This allows EF Core to properly handle complex types that are stored in database columns (like JSON) and convert them between the database and application.

149. What are Value Comparers, and how are they used in EF Core?

• What it tests: Understanding of EF Core's handling of value types for comparison.
• Answer: Value Comparers are used to define how EF Core compares the values of properties, especially when dealing with complex types or properties like collections. They are useful for custom equality comparisons for types like DateTime or decimal, which may have specific requirements for equality checks. Example of configuring a ValueComparer for comparing DateTime values:

  var dateTimeComparer = new ValueComparer<DateTime>(
      (d1, d2) => d1.Equals(d2),  // Equality check
      d => d.GetHashCode(),       // Hash code calculation
      d => d);                    // Copy value
  
  modelBuilder.Entity<MyEntity>()
      .Property(e => e.MyDateTime)
      .HasConversion(
          v => v.ToString("yyyyMMdd"), 
          v => DateTime.ParseExact(v, "yyyyMMdd", null))
      .Metadata.SetValueComparer(dateTimeComparer);
  

  This can be particularly useful for non-standard value comparisons in database queries or when updating entities.

150. How do you handle large-scale data migrations with EF Core (e.g., migrating large datasets or transforming data during migration)?

• What it tests: Knowledge of handling large-scale database migrations with EF Core.
• Answer: Large-scale data migrations in EF Core can be challenging due to performance constraints. Here are strategies for handling them:
  • Batch processing: Instead of updating all records at once, process data in smaller batches to avoid overwhelming the database. Use Skip and Take to retrieve and update records in chunks.
  • Custom SQL in migrations: You can write raw SQL for data transformations in a migration file. For example, to migrate a large amount of data, use SQL UPDATE or INSERT statements directly in the migration:

    migrationBuilder.Sql("UPDATE Products SET Name = 'New Name' WHERE Category = 'OldCategory'");
    

  • Use background tasks for data transformation: For very large datasets, consider performing the data transformation asynchronously in the background, especially if it involves complex calculations or external dependencies.
  • Consider using bulk insert/update libraries: For handling large inserts or updates, libraries like EFCore.BulkExtensions or Z.BulkOperations can be used to improve performance.
  • Minimize locking: When working with large datasets, ensure that database locking does not block other operations. Use WITH (NOLOCK) in custom SQL commands if necessary (though it should be used with caution).

151. What is the Model Snapshot in EF Core, and how does it relate to migrations?

• What it tests: Understanding of how EF Core tracks the model's state across migrations.
• Answer: The Model Snapshot is a file that EF Core uses to track the current state of the model (i.e., the schema of the database) in the form of C# code. It is automatically generated when migrations are created and is stored in the Migrations folder. The snapshot is used by EF Core to generate the SQL for the next migration and to compare the current model with the database schema. It acts as a representation of the database schema at a specific point in time.
  • The snapshot file is automatically updated whenever a migration is added, and EF Core compares the snapshot to the model in the code to generate migration files.

152. How do you handle migrations in a multi-tenant application with EF Core?

• What it tests: Understanding of handling migrations in complex, multi-tenant scenarios.
• Answer: In a multi-tenant application, EF Core migrations can be handled in several ways depending on the architecture:
  • Separate databases per tenant: Each tenant has its own database. This approach simplifies migrations as each database has its own schema. Migrations can be run per database.
  • Shared database with separate schemas: A single database is used, but each tenant has its own schema. In this case, migrations need to be carefully applied to each schema. EF Core does not natively support migrations per schema, so custom logic or scripts may be needed.
  • Shared database with a shared schema: Multiple tenants share the same schema (common in SaaS applications). To distinguish between tenants, tenant-specific filters are applied in queries (via global query filters or tenant ID columns). In this case, migrations should be applied carefully to avoid schema changes affecting all tenants.

153. What are Shadow Properties in EF Core, and how are they used?

• What it tests: Understanding of EF Core’s feature for handling data not represented by entity properties.
• Answer: Shadow properties are properties that exist in the database but are not directly represented in the entity class. They can be used for storing additional metadata, such as audit columns (e.g., CreatedDate, ModifiedDate), or foreign keys that are not mapped directly to an entity property. Shadow properties are configured using the Property method in OnModelCreating:

  modelBuilder.Entity<Order>()
      .Property<DateTime>("CreatedDate")
      .HasDefaultValueSql("GETDATE()");
  

  The CreatedDate property exists in the database but is not part of the Order class. You can access it through the Entry API:

  var entry = dbContext.Entry(order);
  var createdDate = entry.Property("CreatedDate").CurrentValue;
  

154. How do you configure Table Splitting in EF Core?

• What it tests: Knowledge of advanced mapping and how to optimize data storage in EF Core.
• Answer: Table Splitting occurs when two or more entities are mapped to the same database table. It is useful for scenarios where multiple entities share the same properties. In EF Core, this can be configured using the ToTable method and by configuring the relationships between entities:

  modelBuilder.Entity<Customer>()
      .ToTable("Customer")
      .Property(c => c.Id)
      .HasColumnName("CustomerId");
  
  modelBuilder.Entity<ContactDetails>()
      .ToTable("Customer")
      .HasKey(c => c.CustomerId);
  

  In this case, both Customer and ContactDetails will share the same table (Customer), but EF Core will treat them as separate entities.

155. How do you implement Multitenancy in EF Core?

• What it tests: Knowledge of supporting multi-tenant applications with EF Core.
• Answer: Implementing multitenancy in EF Core can be done in various ways depending on the strategy chosen for tenant isolation:
  • Separate databases: Each tenant has a dedicated database. This is the easiest to implement but may result in higher management overhead.
  • Shared database with tenant ID column: All tenants use the same database but have a column (e.g., TenantId) that identifies the tenant for each row. In this case, EF Core's global query filters can be used to automatically apply a tenant filter to each query:

    modelBuilder.Entity<Order>()
        .HasQueryFilter(o => o.TenantId == currentTenantId);
    

  • Separate schemas per tenant: Each tenant has its own schema within the same database. Migrations must be carefully applied to each schema to ensure consistency.

156. What is the difference between FirstOrDefault and SingleOrDefault in EF Core?

• What it tests: Understanding of querying behavior and database performance.
• Answer: Both FirstOrDefault and SingleOrDefault return a single element from a collection or null if no element is found, but they differ in how they handle multiple matching elements:
  • FirstOrDefault: Returns the first element that matches the query. If there are multiple elements that satisfy the condition, it will still return the first one.

    var product = dbContext.Products
        .Where(p => p.Name == "Product A")
        .FirstOrDefault();
    

  • SingleOrDefault: Expects that there is either one or no element that satisfies the query. If more than one element matches, it throws an InvalidOperationException.

    var product = dbContext.Products
        .Where(p => p.Name == "Product A")
        .SingleOrDefault();  // Throws exception if more than one match
    

157. What are Tracking Queries in EF Core, and how can you disable them?

• What it tests: Understanding of how EF Core tracks changes and performance considerations.
• Answer: Tracking queries in EF Core are queries that track changes made to the returned entities. By default, EF Core enables change tracking for all queried entities, which can affect performance when querying large sets of data that don’t require changes. To disable tracking (useful for read-only queries to improve performance), you can use the AsNoTracking method:

  var products = dbContext.Products
      .AsNoTracking()
      .Where(p => p.Price > 100)
      .ToList();
  

  This prevents EF Core from tracking the entities in memory, reducing overhead.

158. How can you configure EF Core to use a SQL Server Full-Text Index?

• What it tests: Knowledge of integrating full-text search capabilities in EF Core.
• Answer: EF Core doesn’t natively support full-text search, but you can use raw SQL queries to query a SQL Server Full-Text Index. To use full-text search, you first need to create a full-text index in your SQL Server database, then query it using raw SQL in EF Core:

  var products = dbContext.Products
      .FromSqlRaw("SELECT * FROM Products WHERE CONTAINS(Name, 'full-text search query')")
      .ToList();
  

  Full-text indexes allow you to perform text-based searches on large amounts of text in SQL Server, improving performance compared to LIKE queries.

159. What is No Tracking in EF Core and when should it be used?

• What it tests: Understanding of performance optimization and when to use No Tracking.
• Answer: No Tracking is a feature in EF Core that allows the framework to bypass the change tracking mechanism for the entities returned by a query. This is useful when you know the entities will not be modified and you only need to read the data. It improves performance by reducing memory usage and processing overhead:

  var products = dbContext.Products
      .AsNoTracking()
      .ToList();
  

  No Tracking should be used in read-only queries, especially when querying large sets of data or in scenarios where the entities are not being modified after retrieval.

160. How do you handle large data loads (e.g., importing large CSV files) in EF Core?

• What it tests: Understanding of handling large-scale data operations.
• Answer: When working with large datasets, like importing CSV files, EF Core can struggle with performance due to memory usage and slow insert operations. Here are strategies to handle large data loads:
  • Bulk operations: Use third-party libraries like EFCore.BulkExtensions to perform bulk inserts, updates, or deletes without loading entities into memory.
  • Batch processing: Instead of inserting all records at once, process the data in smaller batches to avoid memory overload and database locks:

    for (int i = 0; i < largeList.Count; i += 1000)
    {
        var batch = largeList.Skip(i).Take(1000);
        dbContext.Products.AddRange(batch);
        dbContext.SaveChanges();
    }
    

  • Disable change tracking: Use AsNoTracking() for bulk operations to reduce overhead.
  • Use raw SQL: For very large datasets, raw SQL can be used to insert data more efficiently.

161. How do you perform database migrations when using a shared database for multiple applications?

• What it tests: Understanding of handling migrations in a multi-application environment.
• Answer: When multiple applications share the same database, you need to ensure that migrations do not interfere with each other. Here are some strategies:
  • Use distinct migration histories: Maintain separate migration histories for each application, either by using different schema names or migration prefixes.
  • Separate migrations per module: Organize migrations by feature or module, and apply them in a controlled manner.
  • Migrate to staging first: Always apply migrations to a staging environment before production to ensure compatibility across different applications.
  • Version control migrations: Keep migrations in version control so that all applications using the shared database can apply compatible migrations.

162. What are Shadow Foreign Keys in EF Core, and how do you use them?

• What it tests: Advanced understanding of EF Core relationships and foreign keys.
• Answer: Shadow Foreign Keys are foreign key properties that exist in the database but are not explicitly defined in the entity model. They can be useful for scenarios where you want to represent a foreign key relationship in the database without exposing the foreign key property on the entity class. To define a shadow foreign key, use the HasForeignKey method:

  modelBuilder.Entity<Order>()
      .HasOne(o => o.Customer)
      .WithMany(c => c.Orders)
      .HasForeignKey("CustomerId");  // Shadow foreign key
  

  This creates a foreign key column (CustomerId) in the database that is not explicitly defined in the entity model.

163. What is the ChangeTrackingBehavior in EF Core, and how can you control it?

• What it tests: Understanding of how EF Core tracks changes at different levels.

• Answer: ChangeTrackingBehavior controls the way EF Core tracks changes to entities. By default, EF Core tracks changes to all entities retrieved from the database. You can adjust the tracking behavior at the context or entity level:

  • TrackAll: Tracks all entities (default behavior).
  • TrackNone: Does not track any entities, reducing memory usage.
  • TrackChanged: Tracks only entities that have changed.

  You can configure this behavior using the ChangeTracker:

  dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
  

164. What is the difference between AsNoTracking and AsTracking in EF Core, and when would you use each?

• What it tests: Understanding of EF Core’s change tracking behavior.
• Answer:
  • AsNoTracking: When querying data, AsNoTracking disables the change tracking mechanism, improving performance by not holding references to entities. This is ideal for read-only queries where you don't plan to modify or save the entities back to the database.

    var products = dbContext.Products.AsNoTracking().ToList();
    

  • AsTracking: The default behavior for EF Core queries, which enables change tracking. It tracks changes to entities, so they can be persisted back to the database later. It’s useful for scenarios where you plan to modify the entities and save the changes.

    var product = dbContext.Products.AsTracking().FirstOrDefault();
    

165. How do you handle optimistic concurrency control in EF Core?

• What it tests: Understanding of concurrency handling and conflict resolution in EF Core.
• Answer: Optimistic concurrency control ensures that changes to the same data by multiple users are detected and resolved. To implement this in EF Core, you typically use a version or timestamp field (e.g., RowVersion) to detect if an entity has been modified by another user between when it was loaded and when it was saved:
  1. Add a RowVersion property to your entity:

     public class Product
     {
         public int Id { get; set; }
         public string Name { get; set; }
         public byte[] RowVersion { get; set; } // Timestamp or version
     }
     

  2. Configure it as a concurrency token:

     modelBuilder.Entity<Product>()
         .Property(p => p.RowVersion)
         .IsRowVersion();
     

  3. Handle concurrency exceptions: If another user updates the entity before saving, EF Core throws a DbUpdateConcurrencyException. You can catch this exception and resolve the conflict by reloading the entity and merging changes or prompting the user.

     try
     {
         dbContext.SaveChanges();
     }
     catch (DbUpdateConcurrencyException)
     {
         // Handle concurrency conflict
     }
     

166. What is the significance of Find vs FirstOrDefault in EF Core?

• What it tests: Knowledge of different ways to retrieve data from EF Core.
• Answer:
  • Find: The Find method is optimized for retrieving entities by their primary key. It searches the local cache (first) before querying the database. It returns null if no entity is found.

    var product = dbContext.Products.Find(1);
    

  • FirstOrDefault: This is a LINQ method that queries the database (or in-memory entities) based on a condition. It returns the first element that satisfies the condition, or null if no such element is found.

    var product = dbContext.Products.Where(p => p.Name == "Product A").FirstOrDefault();
    

  • Key difference: Find is more efficient when retrieving entities by their primary key, as it checks the cache before querying the database. FirstOrDefault can be used for more complex queries with conditions other than the primary key.

167. How can you handle cascading deletes in EF Core?

• What it tests: Knowledge of entity relationships and cascading behaviors.
• Answer: Cascading deletes automatically remove related entities when the principal entity is deleted. EF Core can be configured to use cascading deletes by setting the OnDelete behavior in a relationship.
  • Cascade delete (default behavior):

    modelBuilder.Entity<Order>()
        .HasOne(o => o.Customer)
        .WithMany(c => c.Orders)
        .OnDelete(DeleteBehavior.Cascade); // Cascade delete
    

  • Restrict delete: Prevents the deletion of the principal entity if there are related entities.

    modelBuilder.Entity<Order>()
        .HasOne(o => o.Customer)
        .WithMany(c => c.Orders)
        .OnDelete(DeleteBehavior.Restrict); // Prevent deletion if there are related orders
    

  • NoAction or SetNull: NoAction prevents the delete action, and SetNull sets the foreign key to NULL upon deletion.

    modelBuilder.Entity<Order>()
        .HasOne(o => o.Customer)
        .WithMany(c => c.Orders)
        .OnDelete(DeleteBehavior.SetNull);
    

168. What is the difference between Add, AddRange, Update, and Remove in EF Core?

• What it tests: Understanding of how EF Core tracks entities and performs CRUD operations.
• Answer:
  • Add: Adds a new entity to the context and marks it as Added. This will insert a new row in the database when SaveChanges is called.

    dbContext.Products.Add(newProduct);
    

  • AddRange: Adds multiple entities to the context and marks them as Added.

    dbContext.Products.AddRange(newProducts);
    

  • Update: Marks an entity as Modified, indicating that all properties of the entity should be updated when SaveChanges is called.

    dbContext.Products.Update(existingProduct);
    

  • Remove: Marks an entity as Deleted, indicating that it should be removed from the database when SaveChanges is called.

    dbContext.Products.Remove(productToDelete);
    

169. How do you perform database seeding in EF Core, and when is it useful?

• What it tests: Understanding of database initialization and testing with EF Core.
• Answer: Database seeding is the process of adding initial data to the database when the database is created or updated. This is useful for populating lookup tables, initial configurations, or testing data.
  • Seeding data in OnModelCreating:

    modelBuilder.Entity<Product>().HasData(
        new Product { Id = 1, Name = "Product A", Price = 100 },
        new Product { Id = 2, Name = "Product B", Price = 150 }
    );
    

  • Seeding after migrations: You can also seed data after migrations using DbContext:

    if (!dbContext.Products.Any())
    {
        dbContext.Products.AddRange(new Product { ... }, new Product { ... });
        dbContext.SaveChanges();
    }
    

  • Note: When using HasData, EF Core handles the insertion during migrations, and if the data already exists (based on the primary key), it will not insert again.

170. What are Owned Entities in EF Core, and how do they differ from regular entities?

• What it tests: Knowledge of value object design and entity relationships in EF Core.
• Answer: Owned entities are types that are dependent on a parent entity and have no identity of their own. They are stored in the same table as the parent entity and are useful for representing value objects (e.g., address, contact info). They are configured using the OwnsOne or OwnsMany method.
  • Example:

    modelBuilder.Entity<Customer>()
        .OwnsOne(c => c.Address);
    

  • Difference from regular entities: Owned entities don't have a primary key and are embedded inside the parent entity. They can't exist independently of the parent entity and are treated as part of the parent’s lifecycle (i.e., they are inserted/updated/deleted with the parent entity).

171. How can you prevent N+1 query issues when querying data in EF Core?

• What it tests: Understanding of query optimization and efficient data retrieval.
• Answer: The N+1 query problem occurs when EF Core executes a separate query for each related entity, leading to inefficient database access. To prevent this, use eager loading with Include and ThenInclude to load related entities in a single query:
  • Eager loading:

    var orders = dbContext.Orders
        .Include(o => o.OrderDetails)
        .ToList();
    

  • Avoid lazy loading: Lazy loading causes EF Core to issue additional queries when navigating navigation properties. Instead, prefer explicit loading with Include.
  • Optimize with AsNoTracking: If no modifications to the entities are needed, use AsNoTracking to reduce overhead.

172. How do you implement SQL Server Full-Text Search in EF Core?

• What it tests: Knowledge of integrating SQL Server features with EF Core.
• Answer: EF Core doesn't natively support full-text search, but you can use raw SQL queries to perform full-text searches in SQL Server. First, create a full-text index on the database, then use FromSqlRaw to execute full-text search queries:

  var products = dbContext.Products
      .FromSqlRaw("SELECT * FROM Products WHERE CONTAINS(Name, 'search term')")
      .ToList();
  

  Full-text search improves the performance of searching large amounts of textual data compared to using LIKE queries.

173. How do you handle database schema changes with EF Core in a production environment?

• What it tests: Understanding of production database management and migrations.
• Answer: When handling schema changes in a production environment, you need to ensure minimal disruption and data integrity. Here are best practices:
  • Apply migrations in staging: Test migrations in a staging environment to ensure they don’t break the schema.
  • Use dotnet ef migrations add and dotnet ef database update: Use these commands to generate and apply migrations in a controlled manner.
  • Database backups: Always back up the production database before applying any migration.
  • Use zero-downtime migration techniques: For critical systems, apply schema changes in stages (e.g., adding columns, then gradually updating the code to use the new column).

174. How can you implement a Many-to-Many relationship in EF Core 7 and above?

• What it tests: Understanding of relationships and new EF Core features.
• Answer: In EF Core 7, many-to-many relationships are simplified and no longer require an explicit join entity. You can define many-to-many relationships directly using HasMany and WithMany:

  public class Student
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public ICollection<Course> Courses { get; set; }
  }
  
  public class Course
  {
      public int Id { get; set; }
      public string Title { get; set; }
      public ICollection<Student> Students { get; set; }
  }
  

  In OnModelCreating, EF Core automatically creates a join table behind the scenes:

  modelBuilder.Entity<Student>()
      .HasMany(s => s.Courses)
      .WithMany(c => c.Students)
      .UsingEntity(j => j.ToTable("StudentCourses"));
  

175. How do you handle large-scale data migrations (e.g., migrating large tables) in EF Core without downtime?

• What it tests: Knowledge of migration strategies for large-scale data updates.
• Answer: Handling large-scale data migrations without downtime requires careful planning:
  1. Use batch processing: Instead of processing all data at once, update the data in small batches to avoid locking the database for a long period:

     var batchSize = 1000;
     var totalRows = dbContext.Products.Count();
     for (int i = 0; i < totalRows; i += batchSize)
     {
         var batch = dbContext.Products.Skip(i).Take(batchSize).ToList();
         foreach (var product in batch)
         {
             product.Price = newPrice;
         }
         dbContext.SaveChanges();
     }
     

  2. Apply schema changes first: For schema updates (e.g., adding a column), apply the changes first, then update data separately. This avoids locking the entire table.
  3. Use background tasks: Offload data migration to background tasks to run in batches, ensuring the application’s responsiveness.

176. How do you optimize database queries for better performance in EF Core?

• What it tests: Knowledge of query optimization in EF Core.
• Answer: Here are several ways to optimize database queries in EF Core:
  • Use AsNoTracking for read-only queries: This avoids unnecessary change tracking, reducing memory overhead.

    var products = dbContext.Products.AsNoTracking().ToList();
    

  • Use Select to load only necessary columns: Instead of loading all columns, select only the necessary ones to reduce memory usage and improve performance.

    var productNames = dbContext.Products.Select(p => p.Name).ToList();
    

  • Use Include and ThenInclude wisely: Avoid unnecessary eager loading of related entities. Only include navigation properties when necessary.

    var order = dbContext.Orders.Include(o => o.OrderDetails).ToList();
    

  • Optimize indexing: Ensure the database has appropriate indexes for the queries you are running frequently (e.g., on foreign key columns).
  • Avoid N+1 queries: Use Include to eagerly load related data to avoid multiple queries.
  • Use raw SQL for complex queries: Sometimes, using raw SQL can provide better performance for complex queries or when using database-specific features (e.g., full-text search).

177. How do you configure Query Filters in EF Core, and when are they useful?

• What it tests: Understanding of global filters and their application in EF Core.
• Answer: Query filters are used to automatically apply conditions to all queries for a specific entity. They are particularly useful for implementing multi-tenancy or soft deletes.
  • Example (soft delete): Automatically filter out deleted entities without requiring explicit filtering in each query:

    modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);
    

  • Example (multi-tenancy): Automatically filter data based on the current tenant:

    modelBuilder.Entity<Order>().HasQueryFilter(o => o.TenantId == currentTenantId);
    

178. What is ChangeTracker.Entries and how can you use it?

• What it tests: Understanding of change tracking and how to access entity states.
• Answer: The ChangeTracker.Entries collection gives access to all tracked entities within the context. It is useful for inspecting and manipulating entity states (e.g., modified, added, deleted).
  • Example:

    foreach (var entry in dbContext.ChangeTracker.Entries())
    {
        if (entry.State == EntityState.Modified)
        {
            // Do something with the modified entity
        }
    }
    

  • This can be useful for auditing changes or applying custom logic when entities are added, modified, or deleted.

179. How do you handle Soft Deletes in EF Core?

• What it tests: Knowledge of managing deletions without actually removing data from the database.
• Answer: A soft delete involves marking an entity as deleted without actually removing it from the database. This is often done by adding a IsDeleted flag or a DeletedAt timestamp to the entity.
  • Steps:
    1. Add a IsDeleted or DeletedAt property to your entity:

       public class Product
       {
           public int Id { get; set; }
           public string Name { get; set; }
           public bool IsDeleted { get; set; }
       }
       

    2. Add a global query filter to exclude soft-deleted records:

       modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);
       

    3. When deleting an entity, set the IsDeleted flag instead of calling Remove:

       var product = dbContext.Products.Find(1);
       product.IsDeleted = true;
       dbContext.SaveChanges();
       

180. What is StoreGeneratedPattern in EF Core, and how is it used?

• What it tests: Understanding of database-generated values and how EF Core handles them.
• Answer: The StoreGeneratedPattern in EF Core specifies how a property value is generated by the database. This is typically used for properties like identity columns or computed columns.
  • Example (Identity column):

    modelBuilder.Entity<Product>()
        .Property(p => p.Id)
        .ValueGeneratedOnAdd() // Automatically generated by the database
        .UseIdentityColumn();
    

  • Example (Computed column):

    modelBuilder.Entity<Product>()
        .Property(p => p.TotalPrice)
        .HasComputedColumnSql("[Quantity] * [Price]");
    

181. How do you use FromSqlRaw and ExecuteSqlRaw in EF Core?

• What it tests: Knowledge of executing raw SQL queries in EF Core.
• Answer:
  • FromSqlRaw: Executes raw SQL queries and maps the results to entities. It’s useful when you need to perform custom queries that EF Core doesn’t support natively.

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

  • ExecuteSqlRaw: Executes raw SQL commands that do not return entities, such as update, delete, or insert statements.

    dbContext.Database.ExecuteSqlRaw("UPDATE Products SET Price = 100 WHERE Price > 150");
    

182. How do you handle Multiple Database Contexts in a single application?

• What it tests: Understanding of managing multiple DbContexts and their configurations.
• Answer: In an application with multiple databases or contexts, EF Core allows you to configure and use multiple DbContext classes. Each DbContext can be configured with its own connection string and database provider.
  • Example: Configuring multiple DbContexts in Startup.cs:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<FirstDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("FirstDatabase")));
        services.AddDbContext<SecondDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("SecondDatabase")));
    }
    

183. What is the impact of Lazy Loading in EF Core, and how can you control it?

• What it tests: Knowledge of lazy loading and its potential pitfalls in EF Core.
• Answer: Lazy loading in EF Core allows related entities to be loaded automatically when they are accessed. It can lead to the N+1 query problem if not managed properly.
  • To enable lazy loading, install the Microsoft.EntityFrameworkCore.Proxies package and enable it in the OnConfiguring method:

    options.UseLazyLoadingProxies();
    

  • To avoid issues, disable lazy loading for performance reasons using AsNoTracking or Include for eager loading when necessary.
  • Lazy loading can be disabled globally by using UseLazyLoadingProxies and not configuring proxies.

184. How do you implement Audit Logging in EF Core?

What it tests: Understanding of entity auditing and tracking changes in EF Core.

• Answer: Audit logging involves tracking and recording changes (e.g., created, updated, deleted) to entities. This can be done by using EF Core’s ChangeTracker to inspect entity states and log changes.
  • You can implement it by overriding SaveChangesAsync to log information about entity changes:

    public override int SaveChanges()
    {
        var entries = ChangeTracker.Entries().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);
        foreach (var entry in entries)
        {
            // Log the changes (e.g., who modified and when)
        }
        return base.SaveChanges();
    }
    

185. How do you handle cascading updates in EF Core?

• What it tests: Understanding of cascading behaviors and managing updates across related entities.
• Answer: EF Core supports cascading deletes by default, but cascading updates (updating related entities when the principal entity is updated) is not enabled by default. To handle cascading updates, you can configure the relationships using the OnDelete method, although cascading updates must be handled explicitly through code.
  • Manual Cascading Updates: If you want to update a related entity when the principal entity is updated, you should manually set the necessary properties before calling SaveChanges:

    var order = dbContext.Orders.Include(o => o.Customer).FirstOrDefault(o => o.Id == 1);
    order.Customer.Name = "Updated Customer Name"; // Cascading manual update
    dbContext.SaveChanges();
    

186. What are some strategies to prevent EF Core from issuing a new query for each relationship navigation property (N+1 query problem)?

• What it tests: Knowledge of query optimization techniques in EF Core.
• Answer:
  • Eager loading: Use Include to load related entities in the initial query, which reduces the number of database round-trips.

    var orders = dbContext.Orders.Include(o => o.OrderDetails).ToList();
    

  • Explicit loading: Use Load to load related entities in a controlled manner, which can be more efficient than lazy loading:

    dbContext.Entry(order).Collection(o => o.OrderDetails).Load();
    

  • Avoid Lazy Loading: Lazy loading can easily cause N+1 query problems. Disable it by not using proxies or by using AsNoTracking to optimize for performance.

187. How do you work with "Shadow Properties" in EF Core?

• What it tests: Understanding the concept of shadow properties and when to use them.
• Answer: Shadow properties are properties that do not exist in the entity class but are tracked by EF Core in the model. They are typically used for scenarios such as tracking audit fields or managing data without directly modifying the entity classes.
  • Example:

    modelBuilder.Entity<Product>().Property<string>("Description");
    

  • Shadow properties can be useful when you need to store data that is not part of the entity class but is necessary for EF Core to track, such as audit logs, timestamps, or internal data not required by the application layer.

188. What are Value Comparisons in EF Core and how do you use them?

• What it tests: Understanding of value equality and comparisons in EF Core.
• Answer: Value Comparisons allow you to configure how EF Core compares values in certain situations, such as for composite keys or when comparing complex types.
  • Example:

    modelBuilder.Entity<Order>()
        .Property(o => o.OrderKey)
        .HasConversion(
            v => v.ToString(),
            v => OrderKey.Parse(v));
    

  • Use cases: These are useful when dealing with complex types or custom value objects, ensuring EF Core can correctly handle the comparisons during queries, updates, and migrations.

189. How do you implement Change Tracking in EF Core for complex scenarios?

• What it tests: Knowledge of advanced change tracking and monitoring in EF Core.
• Answer: EF Core's change tracking helps track changes made to entities since they were loaded or attached to the context. For complex scenarios:
  • Use ChangeTracker.Entries to manually inspect changes:

    foreach (var entry in dbContext.ChangeTracker.Entries())
    {
        if (entry.State == EntityState.Modified)
        {
            // Access the original and current values
            var originalValue = entry.OriginalValues["PropertyName"];
            var currentValue = entry.CurrentValues["PropertyName"];
        }
    }
    

  • Manual change detection: You can also detect changes manually by comparing property values if you need more control over tracking or when working with disconnected entities.

190. How do you optimize many-to-many relationships in EF Core?

• What it tests: Optimizing data retrieval and performance when using many-to-many relationships.
• Answer: EF Core simplifies many-to-many relationships in version 5 and beyond, but optimizing them still requires careful consideration of query performance.
  • Eager Loading: Use Include to load related data upfront:

    var students = dbContext.Students
        .Include(s => s.Courses)
        .ToList();
    

  • Use explicit joins for complex queries: For custom many-to-many queries, manually write the join logic or use raw SQL for better control:

    var studentCourses = dbContext.Students
        .Join(dbContext.StudentCourses,
              s => s.Id,
              sc => sc.StudentId,
              (s, sc) => new { s.Name, sc.CourseId })
        .ToList();
    

191. What is the role of INotifyPropertyChanged in EF Core and how does it relate to entity changes?

• What it tests: Understanding of the INotifyPropertyChanged interface and its interaction with EF Core.
• Answer: The INotifyPropertyChanged interface notifies listeners when a property changes, which is useful for implementing UI updates in MVVM (Model-View-ViewModel) patterns. However, EF Core does not automatically track changes to properties that implement INotifyPropertyChanged. This interface can be used alongside EF Core for custom change tracking but requires manual intervention:
  • You can implement INotifyPropertyChanged in your entities to track changes in your UI layer:

    public class Product : INotifyPropertyChanged
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    
        public event PropertyChangedEventHandler PropertyChanged;
        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }
    }
    

  • To integrate this with EF Core, you would have to trigger the necessary change tracking manually or use event listeners.

192. How do you manage Database Connections and ensure efficient resource management in EF Core?

• What it tests: Understanding of resource management and performance tuning with EF Core.
• Answer: Efficiently managing database connections in EF Core is crucial for scalability and performance:
  • Use Dependency Injection (DI): Always use DI to configure the DbContext, ensuring it is scoped to the request lifecycle (e.g., one DbContext per HTTP request in web applications).
  • Dispose of DbContext properly: EF Core uses connection pooling, but you must ensure that DbContext is disposed of properly to release database connections.
  • Limit Open Connections: Use AsNoTracking for read-only queries to reduce the load on the database by not tracking entities.
  • Connection Pooling: EF Core uses connection pooling by default, which allows for efficient reuse of connections without opening new ones for each request.

193. What is Raw SQL and how do you execute it in EF Core?

• What it tests: Understanding of raw SQL execution in EF Core.
• Answer: Raw SQL execution allows you to execute SQL commands directly against the database using EF Core. This can be useful for complex queries, especially when EF Core’s LINQ provider does not support certain features or optimizations.
  • FromSqlRaw for queries that return entities:

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

  • ExecuteSqlRaw for non-query commands (e.g., updates, deletes):

    dbContext.Database.ExecuteSqlRaw("UPDATE Products SET Price = 100 WHERE Price > 150");
    

  • Parameterization: Always parameterize raw SQL queries to prevent SQL injection attacks.

194. How do you implement and manage Transactions in EF Core?

• What it tests: Knowledge of transaction management in EF Core.
• Answer: EF Core automatically wraps SaveChanges in a transaction. For more fine-grained control, you can manually manage transactions:
  • Manual transaction management:

    using (var transaction = dbContext.Database.BeginTransaction())
    {
        try
        {
            dbContext.Products.Add(new Product { Name = "Product A" });
            dbContext.SaveChanges();
            transaction.Commit(); // Commit the transaction
        }
        catch
        {
            transaction.Rollback(); // Rollback in case of error
            throw;
        }
    }
    

195. How do you configure Concurrency Tokens in EF Core?

• What it tests: Understanding of concurrency handling using tokens in EF Core.
• Answer: Concurrency tokens are properties in your entity model that EF Core uses to detect concurrent changes to the same record. Typically, a timestamp or row version

field is used as a concurrency token: - Example: csharp public class Product { public int Id { get; set; } public string Name { get; set; } public byte[] RowVersion { get; set; } // Concurrency token } modelBuilder.Entity<Product>() .Property(p => p.RowVersion) .IsRowVersion(); // Mark as concurrency token - EF Core will check the token when calling SaveChanges, and if another update has been made to the same record, it will throw a DbUpdateConcurrencyException.

 

196. How do you implement optimistic concurrency control in EF Core?

• What it tests: Knowledge of concurrency control strategies in EF Core.
• Answer: Optimistic concurrency control assumes that multiple transactions can complete without conflicting. EF Core handles this using a concurrency token (often a RowVersion or Timestamp column). When an entity is updated, the concurrency token is checked to ensure no other transactions have modified the entity since it was read.
  • Steps:
    1. Add a RowVersion property to the entity class:

       public class Product
       {
           public int Id { get; set; }
           public string Name { get; set; }
           public byte[] RowVersion { get; set; } // Concurrency token
       }
       

    2. Configure RowVersion in OnModelCreating:

       modelBuilder.Entity<Product>()
           .Property(p => p.RowVersion)
           .IsRowVersion(); // Mark as concurrency token
       

    3. When saving changes, EF Core automatically checks the concurrency token to ensure no conflict has occurred. If a conflict occurs, a DbUpdateConcurrencyException is thrown, which you can handle by merging changes.

197. What are Owned Entities in EF Core, and how are they used?

• What it tests: Understanding of owned entities and value objects in EF Core.
• Answer: Owned entities are entities that are dependent on another entity and do not have their own identity. They are often used to represent value objects, like an address or a phone number.
  • Example:

    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Address ShippingAddress { get; set; } // Owned Entity
    }
    
    public class Address
    {
        public string Street { get; set; }
        public string City { get; set; }
    }
    

  • In OnModelCreating, configure the Owned Entity:

    modelBuilder.Entity<Customer>()
        .OwnsOne(c => c.ShippingAddress);
    

  • Use case: Owned entities are commonly used for scenarios like an address or a collection of settings that belong to a specific entity.

198. How does EF Core handle cascading deletes and updates, and how do you configure them?

• What it tests: Understanding of cascading delete and update operations in EF Core.
• Answer: EF Core can be configured to automatically delete or update related entities when the principal entity is deleted or updated. Cascading behavior can be set using the OnDelete method in the OnModelCreating method.
  • Example (Cascading Deletes):

    modelBuilder.Entity<Order>()
        .HasMany(o => o.OrderDetails)
        .WithOne(od => od.Order)
        .OnDelete(DeleteBehavior.Cascade); // Automatically delete related OrderDetails when Order is deleted
    

  • Types of Delete Behaviors:
    • Cascade: Deletes related entities when the principal entity is deleted.
    • SetNull: Sets the foreign key to NULL when the principal entity is deleted.
    • Restrict: Prevents the deletion of the principal entity if related entities exist.
    • NoAction: Does not perform any action on related entities when the principal entity is deleted.
  • Similarly, cascading updates can be controlled via OnDelete(DeleteBehavior.SetNull) or other delete behaviors.

199. How do you manage database migrations in EF Core when working with multiple databases or schemas?

• What it tests: Knowledge of handling migrations across multiple databases or schemas.
• Answer: EF Core supports multiple databases and schemas by configuring different DbContext classes, each with its own migration configuration.
  • Multiple Databases: Configure different DbContext classes for each database:

    services.AddDbContext<FirstDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("FirstDatabase")));
    
    services.AddDbContext<SecondDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("SecondDatabase")));
    

  • Multiple Schemas: You can specify schemas in the OnModelCreating method:

    modelBuilder.Entity<Product>().ToTable("Products", "Inventory");
    

  • To manage migrations for each database or schema, use separate migration commands for each context:

    dotnet ef migrations add InitialMigration --context FirstDbContext
    dotnet ef migrations add InitialMigration --context SecondDbContext
    

200. What is the role of ModelBuilder in EF Core, and how can it be extended?

• What it tests: Understanding the configuration of the EF Core model and its extensibility.
• Answer: The ModelBuilder is used in EF Core to configure the entity models. This includes setting up relationships, configuring properties, and applying configurations like data constraints or index creation. It is typically used in the OnModelCreating method of your DbContext class.
  • Extensions: You can extend ModelBuilder by creating custom conventions, filters, or configurations.
    • Custom Conventions: Apply conventions globally, such as making all string properties nvarchar(100) by default:

      modelBuilder.Entity<Product>()
          .Property(p => p.Name)
          .HasMaxLength(100);
      

    • Fluent API: Use fluent API to configure relationships, indexes, and data annotations globally or for specific entities:

      modelBuilder.Entity<Product>()
          .HasIndex(p => p.Name)
          .IsUnique();
      

201. How do you implement query performance optimizations in EF Core?

• What it tests: Knowledge of query performance tuning in EF Core.
• Answer:
  • Eager Loading: Use Include and ThenInclude for related data to avoid the N+1 query problem.

    var orders = dbContext.Orders
        .Include(o => o.OrderDetails)
        .ThenInclude(od => od.Product)
        .ToList();
    

  • No Tracking: Use AsNoTracking() for read-only queries to improve performance by not tracking changes:

    var products = dbContext.Products.AsNoTracking().ToList();
    

  • Explicit Loading: Load related entities explicitly when necessary to avoid loading unnecessary data:

    dbContext.Entry(order).Collection(o => o.OrderDetails).Load();
    

  • Projecting: Use projections (Select) to load only the necessary fields instead of loading entire entities:

    var productNames = dbContext.Products
        .Where(p => p.Price > 100)
        .Select(p => p.Name)
        .ToList();
    

  • Batching: Break large queries into smaller, more manageable batches to reduce the load on the database.

202. How do you configure custom conventions in EF Core?

• What it tests: Understanding of custom conventions in EF Core.
• Answer: Custom conventions allow you to enforce rules and configurations globally in your model. You can define them in the OnModelCreating method of your DbContext class by iterating through the ModelBuilder.Model.GetEntityTypes() collection and applying settings to properties or relationships.
  • Example (configuring all string properties to have a maximum length of 100):

    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var property in entityType.GetProperties())
        {
            if (property.ClrType == typeof(string))
            {
                property.SetMaxLength(100);
            }
        }
    }
    

203. What is the Database-First approach in EF Core, and how do you use it?

• What it tests: Knowledge of reverse-engineering models from an existing database.
• Answer: The Database-First approach in EF Core involves generating entity classes and a DbContext from an existing database. This is often used when you have an existing database schema and want to work with EF Core.
  • Steps:
    1. Use the Scaffold-DbContext command to generate the model:

       dotnet ef dbcontext scaffold "Server=myServer;Database=myDB;User Id=myUser;Password=myPass;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models
       

    2. This command generates entity classes, a DbContext, and configurations based on the existing database schema.
  • The Database-First approach is helpful when working with legacy databases or when the database schema is managed separately.

204. How do you implement Full-Text Search in EF Core?

• What it tests: Knowledge of integrating full-text search in EF Core.
• Answer: EF Core does not have native support for full-text search, but you can integrate it using raw SQL queries or database-specific features. Most modern SQL databases, like SQL Server or PostgreSQL, support full-text search.
  • Example (SQL Server):

    var query = dbContext.Products
        .FromSqlRaw("SELECT * FROM Products WHERE CONTAINS(Name, 'searchTerm')")
        .ToList();
    

  • You can also use raw SQL to leverage full-text indexing and perform more advanced search operations directly in the database.

205. What are the key differences between AddDbContext and AddDbContextPool in EF Core?

• What it tests: Knowledge of database context pooling in EF Core.
• Answer: Both methods are used to register DbContext with dependency injection, but

they differ in how they manage the lifetime of DbContext instances. - AddDbContext: This creates a new DbContext instance per request or scope, which is more flexible but may result in more frequent allocations and garbage collection. - AddDbContextPool: This enables connection pooling and DbContext pooling, where a limited number of DbContext instances are reused, improving performance by avoiding unnecessary allocations. It is ideal for applications with high throughput. csharp services.AddDbContextPool<MyDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));