EF Core technical coding interview questions and answers

EF Core technical coding interview questions and answers

1. How do you define a one-to-many relationship in EF Core?

Question: Given two entities, Order and Customer, implement a one-to-many relationship where one Customer can have many Orders.

Answer:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }  // Navigation property
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public int CustomerId { get; set; }  // Foreign key
    public Customer Customer { get; set; }  // Navigation property
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .HasForeignKey(o => o.CustomerId); // Configure the foreign key
    }
}

2. How do you implement a `Many-to-Many` relationship in EF Core?

Question: Implement a many-to-many relationship between Student and Course, where a student can enroll in multiple courses, and each course can have multiple students.

Answer: Starting from EF Core 5.0, a many-to-many relationship is supported directly, without needing an explicit join table.

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    public ICollection<Course> Courses { get; set; }  // Navigation property
}

public class Course
{
    public int CourseId { get; set; }
    public string Title { get; set; }
    public ICollection<Student> Students { get; set; }  // Navigation property
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Student> Students { get; set; }
    public DbSet<Course> Courses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
            .HasMany(s => s.Courses)
            .WithMany(c => c.Students);
    }
}

3. How do you implement a `Composite Key` in EF Core?

Question: You have an OrderDetail entity that has a composite key consisting of OrderId and ProductId. Implement the composite key using EF Core.

Answer:

public class OrderDetail
{
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<OrderDetail> OrderDetails { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrderDetail>()
            .HasKey(od => new { od.OrderId, od.ProductId });  // Composite key configuration
    }
}

4. How do you perform a `Bulk Insert` in EF Core?

Question: You need to insert a list of User entities into the database. How do you do a bulk insert using EF Core?

Answer: EF Core does not have built-in bulk insert support, but you can achieve it using third-party libraries like EFCore.BulkExtensions.

Example using EFCore.BulkExtensions:

using EFCore.BulkExtensions;

// List of users to insert
var users = new List<User>
{
    new User { Name = "John" },
    new User { Name = "Jane" },
    new User { Name = "Jack" }
};

// Perform bulk insert
context.BulkInsert(users);

Alternatively, for small-scale inserts, you can use AddRange:

context.Users.AddRange(users);
context.SaveChanges();

5. How do you query data using LINQ and include related data in EF Core?

Question: You need to fetch a list of Orders and include their related Customer and Products (one-to-many and many-to-many relationships). Write the query.

Answer:

var ordersWithCustomerAndProducts = context.Orders
    .Include(o => o.Customer)  // Include related customer data
    .Include(o => o.OrderDetails)  // Include related order details (many-to-many)
        .ThenInclude(od => od.Product)  // Include product for each order detail
    .ToList();

6. How do you use `AsNoTracking` for performance optimization in EF Core?

Question: You want to retrieve a list of Users without tracking them for performance reasons. Write the query using AsNoTracking().

Answer:

var users = context.Users
    .AsNoTracking()  // No tracking for better performance (read-only scenario)
    .Where(u => u.Age > 30)
    .ToList();

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

Question: Implement a soft delete mechanism in the User entity, where the IsDeleted flag is used instead of actually deleting the record.

Answer:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }  // Soft delete flag
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasQueryFilter(u => !u.IsDeleted);  // Global filter to exclude soft-deleted users by default
    }
}

To mark a user as deleted:

var user = context.Users.Find(1);
user.IsDeleted = true;
context.SaveChanges();

8. How do you handle `Concurrency` in EF Core?

Question: Implement concurrency control for the User entity using a Timestamp column to handle simultaneous updates.

Answer: Add a Timestamp column to the User entity, which EF Core will use to detect concurrency issues.

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public byte[] RowVersion { get; set; }  // Concurrency control field (Timestamp)
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Property(u => u.RowVersion)
            .IsRowVersion();  // Configures RowVersion for concurrency control
    }
}

When updating:

var user = context.Users.Find(1);
user.Name = "Updated Name";
try
{
    context.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
    // Handle concurrency conflict
}

9. How do you use `Raw SQL Queries` in EF Core?

Question: Write a query to fetch Users whose Age is greater than 30 using raw SQL in EF Core.

Answer:

var users = context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Age > {0}", 30)
    .ToList();

For a non-query operation:

context.Database.ExecuteSqlRaw("DELETE FROM Users WHERE Age < {0}", 18);

10. How do you implement `Eager Loading` in EF Core?

Question: You want to retrieve a list of Orders along with their related Customer and Product entities using eager loading.

Answer:

var orders = context.Orders
    .Include(o => o.Customer)  // Eagerly load Customer
    .Include(o => o.OrderDetails)  // Eagerly load OrderDetails (many-to-many)
        .ThenInclude(od => od.Product)  // Eagerly load Product for each OrderDetail
    .ToList();

11. How do you perform a `Migrations` operation in EF Core?

Question: Perform the following operations:

Add a new property Email to the User entity.
Generate and apply a migration.

Answer:

Update the User class:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }  // New property
}

Open the Package Manager Console (PMC) and run:
```
Add-Migration AddEmailToUser
```
To apply the migration:
```
Update-Database
```

12. How do you implement Lazy Loading in EF Core?

Question: You have two entities, Customer and Order, where each Customer has many Orders. Implement lazy loading so that the related Orders for a Customer are loaded only when accessed.

Answer: Lazy loading in EF Core requires setting up virtual navigation properties and enabling the LazyLoadingProxies package.

Install the Microsoft.EntityFrameworkCore.Proxies package via NuGet:
```
dotnet add package Microsoft.EntityFrameworkCore.Proxies
```

Configure lazy loading in DbContext:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLazyLoadingProxies()  // Enable Lazy Loading
            .UseSqlServer("Your_Connection_String");
    }
}

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }  // Virtual navigation property
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
}

Lazy loading is enabled, and Orders will be loaded automatically when you access customer.Orders for the first time.

13. How do you configure `Global Query Filters` in EF Core?

Question: Implement a global query filter for the User entity to exclude any soft-deleted users (users with IsDeleted set to true) from being queried.

Answer: You can use HasQueryFilter to apply a global filter in EF Core. In this example, the filter will ensure that all queries exclude soft-deleted records by default.

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }  // Soft delete flag
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply global filter to exclude soft-deleted users
        modelBuilder.Entity<User>()
            .HasQueryFilter(u => !u.IsDeleted);  // Exclude IsDeleted == true by default
    }
}

Now, all queries for Users will automatically exclude the soft-deleted ones:

var activeUsers = context.Users.ToList();  // Does not include soft-deleted users

14. How do you execute a `Stored Procedure` in EF Core?

Question: You need to execute a stored procedure named GetActiveUsers that returns a list of users with an IsActive flag.

Answer: You can execute a raw SQL query using the FromSqlRaw method or ExecuteSqlRaw for non-query procedures.

Define your entity class:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

Execute the stored procedure using FromSqlRaw:

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

For non-query stored procedures (like an update or delete), you can use:

context.Database.ExecuteSqlRaw("EXEC DeleteInactiveUsers");

15. How do you handle `Transactions` in EF Core?

Question: You want to perform multiple database operations as a single transaction (e.g., add a new Order and related OrderDetails). Implement this using EF Core.

Answer: EF Core handles transactions implicitly when calling SaveChanges(), but you can explicitly control transactions using IDbContextTransaction.

using (var transaction = context.Database.BeginTransaction())
{
    try
    {
        var order = new Order { OrderDate = DateTime.Now };
        context.Orders.Add(order);

        var orderDetail = new OrderDetail { OrderId = order.OrderId, ProductId = 1, Quantity = 2 };
        context.OrderDetails.Add(orderDetail);

        context.SaveChanges();

        transaction.Commit();  // Commit the transaction
    }
    catch (Exception)
    {
        transaction.Rollback();  // Rollback in case of error
        throw;
    }
}

This ensures that both Order and OrderDetails are added as part of a single transaction.

16. How do you configure `Many-to-Many` relationship with a join entity in EF Core?

Question: Implement a many-to-many relationship between Author and Book entities using a join entity AuthorBook.

Answer: In EF Core, a join entity is used explicitly when you need additional properties in the junction table.

Define the entities:

public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
    public ICollection<AuthorBook> AuthorBooks { get; set; }
}

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public ICollection<AuthorBook> AuthorBooks { get; set; }
}

public class AuthorBook
{
    public int AuthorId { get; set; }
    public Author Author { get; set; }

    public int BookId { get; set; }
    public Book Book { get; set; }
}

Configure the relationships in OnModelCreating:

public class ApplicationDbContext : DbContext
{
    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }
    public DbSet<AuthorBook> AuthorBooks { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AuthorBook>()
            .HasKey(ab => new { ab.AuthorId, ab.BookId });

        modelBuilder.Entity<AuthorBook>()
            .HasOne(ab => ab.Author)
            .WithMany(a => a.AuthorBooks)
            .HasForeignKey(ab => ab.AuthorId);

        modelBuilder.Entity<AuthorBook>()
            .HasOne(ab => ab.Book)
            .WithMany(b => b.AuthorBooks)
            .HasForeignKey(ab => ab.BookId);
    }
}

Now you can query the many-to-many relationship through the AuthorBook join entity:

var authorBooks = context.AuthorBooks
    .Include(ab => ab.Author)
    .Include(ab => ab.Book)
    .ToList();

17. How do you handle `Custom Mappings` in EF Core?

Question: You want to map an existing database column to a property in an entity with a different name.

Answer: You can use the HasColumnName method to map a property to a column with a different name in the database.

public class User
{
    public int UserId { get; set; }
    public string FullName { get; set; }  // This is mapped to a different column
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Property(u => u.FullName)
            .HasColumnName("Name");  // Map FullName property to 'Name' column in database
    }
}

18. How do you handle `Nullable` properties in EF Core?

Question: You have a User entity with an optional DateOfBirth. How do you configure it as nullable in EF Core?

Answer: By default, properties that are value types (e.g., int, DateTime) are not nullable. You can make them nullable by using nullable types (int?, DateTime?).

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public DateTime? DateOfBirth { get; set; }  // Nullable DateTime
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
}

19. How do you perform a `Bulk Update` in EF Core?

Question: You need to update the IsActive status of all User entities to false. Implement a bulk update in EF Core.

Answer: EF Core does not provide built-in bulk update, but you can use third-party libraries like EFCore.BulkExtensions.

Using EFCore.BulkExtensions:

var users = context.Users.Where(u => u.IsActive == true).ToList();
foreach (var user in users)
{
    user.IsActive = false;
}

context.BulkUpdate(users);  // Efficient bulk update

Alternatively, you can execute a raw SQL update for performance:

context.Database.ExecuteSqlRaw("UPDATE Users SET IsActive = 0 WHERE IsActive = 1");

20. How do you handle Cascading Deletes in EF Core?
Question: You have a Customer entity and a Order entity. When a Customer is deleted, you want all related Orders to be deleted as well. How do you implement this?
Answer:
In EF Core, you can use the OnDelete method to configure cascading deletes.
public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }  // Navigation property
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public int CustomerId { get; set; }  // Foreign key
    public Customer Customer { get; set; }  // Navigation property
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .HasForeignKey(o => o.CustomerId)
            .OnDelete(DeleteBehavior.Cascade);  // Enable cascading delete
    }
}

Now, when a Customer is deleted, all associated Orders will also be deleted automatically.
21. How do you configure Indexes in EF Core?
Question: Create an index on the Email property of the User entity to improve query performance.
Answer:
You can configure indexes in EF Core using the HasIndex method in the OnModelCreating method.
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }  // Email property
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Create an index on the 'Email' property
        modelBuilder.Entity<User>()
            .HasIndex(u => u.Email)
            .IsUnique();  // Optional: enforce uniqueness
    }
}

This will create an index on the Email column in the database to improve query performance for operations that filter or search by email.
22. How do you handle Custom Conversions in EF Core?
Question: You need to store a DateTimeOffset as a string in the database. How would you configure the conversion in EF Core?
Answer:
EF Core provides a way to configure custom conversions using the HasConversion method. Here's how you can store a DateTimeOffset as a string:
public class Event
{
    public int EventId { get; set; }
    public DateTimeOffset EventDate { get; set; }  // Original DateTimeOffset property
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Event> Events { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Event>()
            .Property(e => e.EventDate)
            .HasConversion(
                v => v.ToString("o"),  // Convert to string using "o" format
                v => DateTimeOffset.Parse(v)  // Convert back from string to DateTimeOffset
            );
    }
}

This ensures that when EventDate is saved to the database, it is stored as a string, and when it is retrieved, it is properly converted back to DateTimeOffset.
23. How do you use Value Conversions in EF Core?
Question: You want to store a bool? property as an integer (0 or 1) in the database. How do you implement this?
Answer:
You can use value conversions to change how a bool? is stored in the database.
public class User
{
    public int UserId { get; set; }
    public bool? IsActive { get; set; }  // Nullable bool
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Property(u => u.IsActive)
            .HasConversion<int?>();  // Converts bool? to int (0 or 1)
    }
}

This ensures that the nullable bool is stored as an int in the database (0 for false, 1 for true).
24. How do you perform GroupBy in EF Core with LINQ?
Question: Write a LINQ query to group Orders by CustomerId and get the total count of orders for each customer.
Answer:
You can use the GroupBy method in LINQ to group data.
var orderCounts = context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        OrderCount = g.Count()  // Count orders per customer
    })
    .ToList();

This will return a list of anonymous objects with the CustomerId and the total count of orders for each customer.
25. How do you handle Shadow Properties in EF Core?
Question: Implement a Shadow Property to track when an entity was last updated, without explicitly adding it to the entity class.
Answer:
Shadow properties are properties that exist in the model but are not part of the entity class. You can add shadow properties in the OnModelCreating method.
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Add a shadow property 'LastUpdated' to track last update time
        modelBuilder.Entity<User>()
            .Property<DateTime>("LastUpdated");

        // Use a model-wide convention to set this value on save
        modelBuilder.Entity<User>()
            .Property<DateTime>("LastUpdated")
            .HasDefaultValueSql("GETDATE()");  // Set default to current date/time
    }
}

You can access the shadow property like this:
var user = context.Users.Find(1);
var lastUpdated = context.Entry(user).Property("LastUpdated").CurrentValue;

26. How do you handle Soft Deletes with Global Query Filters in EF Core?
Question: You want to filter out soft-deleted entities (using the IsDeleted property) for all queries on User entities.
Answer:
You can implement soft deletes and global query filters as follows:
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }  // Soft delete flag
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply a global query filter to exclude soft-deleted records
        modelBuilder.Entity<User>()
            .HasQueryFilter(u => !u.IsDeleted);
    }
}

Now, every query on the Users table will exclude the soft-deleted entities automatically.
27. How do you apply Partial Updates (Update specific fields) in EF Core?
Question: You want to update the Name property of a User without modifying other properties.
Answer:
You can perform a partial update by specifying the properties to be updated using Update or Attach.
var user = new User { UserId = 1, Name = "New Name" };

// Update only the Name property
context.Users.Update(user);  // This will mark only the Name property as modified
context.SaveChanges();

Alternatively, you can explicitly set which properties to update:
var user = context.Users.Find(1);
user.Name = "Updated Name";

context.Entry(user).Property(u => u.Name).IsModified = true;  // Mark only Name as modified
context.SaveChanges();

28. How do you handle Pagination in EF Core?
Question: You need to paginate a list of Users, retrieving 10 users per page.
Answer:
You can implement pagination by using the Skip and Take methods in LINQ.
int pageNumber = 1;  // For example, first page
int pageSize = 10;    // Number of users per page

var pagedUsers = context.Users
    .Skip((pageNumber - 1) * pageSize)  // Skip users based on page number
    .Take(pageSize)                     // Take users based on page size
    .ToList();

29. How do you handle Auto-increment primary keys in EF Core?
Question: You want to configure the UserId of the User entity as an auto-increment primary key.
Answer:
By default, EF Core configures primary keys to auto-increment (using IDENTITY in SQL Server). You don’t need to do anything special unless you need to configure it differently.
public class User
{
    public int UserId { get; set; }  // Auto-increment by default
    public string Name { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
}

EF Core automatically configures UserId as an identity column in SQL Server, so it will auto-increment.


30. How do you handle Concurrency Control in EF Core?

Question: How would you implement optimistic concurrency control to ensure that no other process has modified a record before updating it?

Answer:
To implement optimistic concurrency in EF Core, you can use a ConcurrencyToken (usually a Timestamp or RowVersion column). The database will check if the record has been modified before performing the update.



Add a RowVersion property to the entity:
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public byte[] RowVersion { get; set; }  // RowVersion for concurrency control
}



Configure it in the OnModelCreating method:
public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Property(u => u.RowVersion)
            .IsRowVersion();  // Marks the RowVersion property for concurrency control
    }
}



When updating the entity, EF Core will check if the RowVersion has changed:
var user = context.Users.FirstOrDefault(u => u.UserId == 1);
user.Name = "Updated Name";
context.SaveChanges();  // Throws a DbUpdateConcurrencyException if the RowVersion doesn't match



If the RowVersion is modified by another process, EF Core will throw a DbUpdateConcurrencyException that can be caught to handle the conflict.

31. How do you create a Computed Column in EF Core?

Question: How would you configure a computed column in the database, such as a FullName that concatenates FirstName and LastName?

Answer:
You can configure computed columns in EF Core using the HasComputedColumnSql method.



Define the entity:
public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName { get; set; }  // Computed column
}



Configure the computed column in the OnModelCreating method:
public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Property(u => u.FullName)
            .HasComputedColumnSql("FirstName + ' ' + LastName");  // SQL expression for computed column
    }
}



EF Core will now map the FullName property to a computed column in the database. When queried, the FullName will be calculated automatically.

32. How do you handle Multiple DbContexts in EF Core?

Question: In a project, you have two DbContexts, one for Customer and one for Order. How do you handle multiple DbContexts?

Answer:
You can configure multiple DbContexts in the Startup.cs (or equivalent) file by registering them with the dependency injection container.



Define your DbContexts:
public class CustomerDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
}

public class OrderDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
}



Register them in the Startup.cs file:
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<CustomerDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("CustomerConnection")));
    services.AddDbContext<OrderDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("OrderConnection")));
}



Inject the DbContexts in your services or controllers:
public class CustomerService
{
    private readonly CustomerDbContext _customerContext;

    public CustomerService(CustomerDbContext customerContext)
    {
        _customerContext = customerContext;
    }
}

public class OrderService
{
    private readonly OrderDbContext _orderContext;

    public OrderService(OrderDbContext orderContext)
    {
        _orderContext = orderContext;
    }
}



By registering and injecting multiple DbContexts, you can work with different parts of your application that use separate databases or different entities.

33. How do you handle Nullability and Required fields in EF Core?

Question: How do you configure an entity property to be required (non-nullable) in EF Core, and how do you allow nullability?

Answer:
You can use the IsRequired method to enforce non-nullability and allow nulls with nullable types.



For a required property (non-nullable):
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }  // Non-nullable
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Property(u => u.Name)
            .IsRequired();  // Ensures the Name field is not null
    }
}



For a nullable property (e.g., DateOfBirth):
public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public DateTime? DateOfBirth { get; set; }  // Nullable DateTime
}



By default, EF Core will configure properties as nullable or non-nullable based on their type (e.g., string is nullable, while int is non-nullable). You can explicitly configure them using the IsRequired method.

34. How do you create Stored Procedures with parameters in EF Core?

Question: You want to execute a stored procedure named GetUserOrders with an UserId parameter in EF Core.

Answer:
You can use FromSqlRaw or ExecuteSqlRaw to execute stored procedures with parameters.



Define a model for the stored procedure result:
public class UserOrderDto
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public string ProductName { get; set; }
}



Execute the stored procedure with parameters:
var userId = 1;
var userOrders = context.Set<UserOrderDto>()
    .FromSqlRaw("EXEC GetUserOrders @UserId", new SqlParameter("@UserId", userId))
    .ToList();



This approach will execute the stored procedure and map the results to a DTO (UserOrderDto in this case).

35. How do you handle Bulk Insert operations in EF Core?

Question: You need to insert a large number of records (e.g., 1000 Users) into the database in a single operation using EF Core.

Answer:
EF Core doesn’t natively support bulk inserts, but you can use third-party libraries like EFCore.BulkExtensions to perform bulk operations efficiently.



Install the EFCore.BulkExtensions package via NuGet:
dotnet add package EFCore.BulkExtensions



Perform a bulk insert:
var users = new List<User>
{
    new User { Name = "John Doe" },
    new User { Name = "Jane Smith" },
    // Add 1000 more users
};

context.BulkInsert(users);  // Efficient bulk insert



This approach is highly efficient compared to inserting records one by one.

36. How do you perform Eager Loading in EF Core?

Question: You want to load a Customer along with their Orders in a single query (Eager Loading).

Answer:
You can use the Include method to perform eager loading in EF Core, which loads related entities along with the primary entity.

var customersWithOrders = context.Customers
    .Include(c => c.Orders)  // Eager load Orders along with Customer
    .ToList();

This will return all customers along with their related orders in a single query, reducing the number of queries executed.

37. How do you use Explicit Loading in EF Core?

Question: You want to load the Orders of a Customer explicitly after loading the Customer entity.

Answer:
Explicit loading allows you to load related data on demand using the Entry method.



Load the Customer first:
var customer = context.Customers.FirstOrDefault(c => c.CustomerId == 1);



Explicitly load the Orders for the Customer:
context.Entry(customer)
    .Collection(c => c.Orders)  // Explicitly load the Orders collection
    .Load();



This allows you to load related entities when needed, which can help with performance and reduce unnecessary queries.

38. How do you configure a `Foreign Key` relationship in EF Core?

Question: How would you set up a Customer entity and an Order entity where each Order is related to a Customer?

Answer: In EF Core, you configure a foreign key relationship by defining the navigation properties and ensuring a foreign key property exists. Here's how you can configure it:

Define the entities with a foreign key:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }  // Navigation property
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public int CustomerId { get; set; }  // Foreign key
    public Customer Customer { get; set; }  // Navigation property
}

Configure the relationship using OnModelCreating (if necessary):

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasOne(o => o.Customer)  // One order has one customer
            .WithMany(c => c.Orders)  // A customer can have many orders
            .HasForeignKey(o => o.CustomerId);  // Define the foreign key property
    }
}

EF Core automatically configures the foreign key (CustomerId) based on conventions, but you can explicitly configure it using the HasForeignKey method if needed.

39. How do you handle `Database Migrations` in EF Core?

Question: How would you add a migration and apply it to your database in EF Core?

Answer: EF Core provides a CLI tool to handle migrations. The basic steps to add and apply a migration are as follows:

Add a migration: In the terminal, run the following command to add a migration:
```
dotnet ef migrations add MigrationName
```
This will generate a migration file in your project (in the Migrations folder).
Apply the migration to the database: To apply the migration to the database (i.e., update the schema), run the following command:
```
dotnet ef database update
```

EF Core will compare the current model with the database schema and apply the necessary changes.

40. How do you handle `Seed Data` in EF Core?

Question: How would you seed initial data in your database using EF Core?

Answer: You can seed data using the HasData method in the OnModelCreating method to provide initial data for your entities.

Define the seed data:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().HasData(
            new Customer { CustomerId = 1, Name = "John Doe" },
            new Customer { CustomerId = 2, Name = "Jane Smith" }
        );
    }
}

When you run a migration, EF Core will automatically insert this data into the database. To add the seed data, use the following command after creating the migration:
```
dotnet ef migrations add SeedDataMigration
dotnet ef database update
```

EF Core will insert the seed data into the database during migration execution.

41. How do you handle `Lazy Loading` in EF Core?

Question: How would you configure lazy loading for related entities in EF Core?

Answer: Lazy loading in EF Core allows related entities to be automatically loaded when accessed. EF Core uses proxies to implement lazy loading, so you need to enable it in the Startup.cs file or your DbContext class.

Install the Microsoft.EntityFrameworkCore.Proxies package:

dotnet add package Microsoft.EntityFrameworkCore.Proxies

Enable lazy loading in the OnConfiguring method of your DbContext:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseLazyLoadingProxies()
            .UseSqlServer("your_connection_string");
    }
}

Mark navigation properties as virtual for lazy loading to work:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }  // Virtual for lazy loading
}

Now, related entities (like Orders for Customer) will be loaded automatically when accessed, but only if they haven't already been loaded.

42. How do you perform `Explicit Loading` in EF Core?

Question: You want to explicitly load a related entity (e.g., Orders) for a Customer after querying the Customer entity.

Answer: Explicit loading allows you to load related data manually after loading the primary entity. This is done using the Entry method in EF Core.

Load the Customer first:

var customer = context.Customers.FirstOrDefault(c => c.CustomerId == 1);

Explicitly load the related Orders collection:

context.Entry(customer)
       .Collection(c => c.Orders)  // Load Orders explicitly
       .Load();

Explicit loading is useful when you don't want to load related data immediately but want to load it on demand.

43. How do you handle `SQL Raw Queries` in EF Core?

Question: You want to execute a raw SQL query that returns data from a table and map the result to a custom DTO. How would you do that in EF Core?

Answer: You can use FromSqlRaw to execute raw SQL queries in EF Core and map the result to a custom DTO.

Define your DTO (Data Transfer Object):

public class OrderSummaryDto
{
    public int OrderId { get; set; }
    public string ProductName { get; set; }
    public DateTime OrderDate { get; set; }
}

Execute the raw SQL query and map the result:

var orderSummaries = context.Set<OrderSummaryDto>()
    .FromSqlRaw("SELECT OrderId, ProductName, OrderDate FROM Orders WHERE OrderDate > {0}", DateTime.Now)
    .ToList();

This query will execute the SQL directly, returning a list of OrderSummaryDto with the data fetched from the Orders table.

44. How do you handle `Transactions` in EF Core?

Question: How would you ensure multiple changes in the database are wrapped in a single transaction?

Answer: In EF Core, transactions are handled using the SaveChanges method by default. If you want to ensure multiple database operations are committed together, you can create an explicit transaction.

Begin a transaction manually:

using (var transaction = context.Database.BeginTransaction())
{
    try
    {
        // Perform operations
        context.Customers.Add(new Customer { Name = "Alice" });
        context.Orders.Add(new Order { CustomerId = 1, OrderDate = DateTime.Now });
        context.SaveChanges();

        // Commit the transaction if everything is successful
        transaction.Commit();
    }
    catch (Exception)
    {
        // Rollback if there's an error
        transaction.Rollback();
        throw;
    }
}

This ensures that both the Customer and Order changes are committed or rolled back together.

45. How do you prevent `N+1` Query Problem in EF Core?

Question: How would you avoid the N+1 query problem when querying a list of Customers and their related Orders?

Answer: The N+1 query problem happens when you load related entities in a loop, resulting in multiple database queries. You can prevent it by using Eager Loading with the Include method.

Use eager loading with Include to load related entities in a single query:

var customersWithOrders = context.Customers
    .Include(c => c.Orders)  // Load related Orders in a single query
    .ToList();

By using eager loading, the related Orders will be loaded along with the Customers, reducing the number of queries executed.

Certainly! Here are even more EF Core technical coding interview questions and answers to help you prepare for your interview:

46. How do you perform a `Left Join` in EF Core?

Question: How would you perform a Left Join in EF Core between the Customer and Order entities, where you want to include customers even if they have no orders?

Answer: EF Core supports Left Join functionality using LINQ's DefaultIfEmpty method, which ensures that all records from the left side (the Customer entity) are included, even if no matching records exist on the right side (the Order entity).

var leftJoinQuery = from customer in context.Customers
                    join order in context.Orders
                    on customer.CustomerId equals order.CustomerId into customerOrders
                    from order in customerOrders.DefaultIfEmpty()
                    select new
                    {
                        customer.Name,
                        OrderId = order?.OrderId,
                        OrderDate = order?.OrderDate
                    };

var result = leftJoinQuery.ToList();

In this example, DefaultIfEmpty() is used to include all customers, even those without orders, by returning a null value for Order when there is no matching record.

47. What is `Change Tracking` in EF Core, and how does it work?

Question: Explain how change tracking works in EF Core and how you can use it to detect changes in your entities.

Answer: In EF Core, change tracking is the process where EF Core keeps track of the changes made to entities during the lifecycle of a DbContext. It automatically tracks the state of each entity, including whether it is added, modified, or deleted.

Tracking Changes:
- When you load an entity, EF Core tracks the changes made to its properties.
- When you call SaveChanges(), EF Core checks which entities have been modified and generates the necessary SQL to update the database.

Example:

var customer = context.Customers.First();
customer.Name = "Updated Name";  // EF Core tracks this change

context.SaveChanges();  // Generates an UPDATE statement for the changed entity

Changing Tracking Behavior: You can disable change tracking for performance reasons, especially when querying large datasets that you don't intend to modify:
```
var customers = context.Customers
                        .AsNoTracking()  // Disable change tracking for this query
                        .ToList();
```

48. How do you implement `Soft Delete` in EF Core?

Question: How would you implement a soft delete mechanism, where records are marked as deleted without actually being removed from the database?

Answer: A soft delete is typically implemented by adding a Deleted flag (e.g., IsDeleted) or a DeletedAt timestamp to your entity and updating this field when deleting an entity, rather than removing it from the database.

Entity Setup: Add a IsDeleted flag or DeletedAt property to your entity:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }  // Soft delete flag
}

Mark Entity as Deleted: Instead of calling Remove, you update the IsDeleted flag:

var customer = context.Customers.FirstOrDefault(c => c.CustomerId == 1);
customer.IsDeleted = true;
context.SaveChanges();

Filter Soft-Deleted Records: To exclude soft-deleted records from your queries, use a global query filter:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .HasQueryFilter(c => !c.IsDeleted);  // Filter soft-deleted records globally
    }
}

This approach ensures that IsDeleted acts as a soft delete flag, and you can still access and restore deleted entities if needed.

49. How do you implement `Database Sharding` in EF Core?

Question: How would you implement a sharded database strategy (splitting data across multiple databases or tables) in EF Core?

Answer: EF Core does not have built-in support for database sharding, but you can implement it by manually directing queries to different databases based on some partitioning logic. You would typically implement a custom DbContext or service to handle sharding.

Create a custom service for sharding: You could create a service that determines which database to use based on the entity or partition key (e.g., CustomerId):

public class ShardedDbContextFactory
{
    private readonly string[] _shardConnectionStrings;
    public ShardedDbContextFactory(string[] shardConnectionStrings)
    {
        _shardConnectionStrings = shardConnectionStrings;
    }

    public ApplicationDbContext GetContext(int customerId)
    {
        var shardIndex = customerId % _shardConnectionStrings.Length;
        var connectionString = _shardConnectionStrings[shardIndex];
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        optionsBuilder.UseSqlServer(connectionString);
        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

Use the factory in your application:

var dbContext = dbContextFactory.GetContext(customerId);
var customer = dbContext.Customers.FirstOrDefault(c => c.CustomerId == customerId);

With this approach, you can dynamically route queries to different databases based on customer ID or other partitioning keys.

50. How do you handle `Complex Types` (Value Objects) in EF Core?

Question: How would you map a Complex Type or value object, like an Address, in EF Core, so that it’s stored as a part of another entity?

Answer: EF Core allows you to define complex types (value objects) that don't have their own primary keys but are part of a larger entity. You can use Owned Types to map complex types to database columns.

Define a Complex Type: Create the value object, such as Address:

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}

Add the Complex Type to an Entity: The Address is part of the Customer entity:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }  // Complex type
}

Configure the Complex Type in the OnModelCreating Method: In the DbContext, configure the Address as an owned type:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .OwnsOne(c => c.Address);  // Configure Address as a complex type (owned entity)
    }
}

This will result in the Address being stored in the same table as Customer but as separate columns.

51. How do you handle `Multiple Database Providers` in EF Core?

Question: How would you configure EF Core to use different database providers, such as SQL Server and SQLite, in different environments or situations?

Answer: EF Core supports multiple database providers, and you can configure different providers based on your application's environment.

Install Necessary NuGet Packages: You would need to install the appropriate packages for each database provider:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Configure the DbContext for Multiple Providers: In your Startup.cs (or equivalent) file, you can conditionally configure the database provider based on the environment:

public void ConfigureServices(IServiceCollection services)
{
    if (Environment.IsDevelopment())
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlite("Data Source=app.db"));
    }
    else
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    }
}

By using conditional logic, you can switch between SQL Server, SQLite, or any other provider based on your application's environment or configuration.

52. What is the `ChangeTracker` in EF Core, and how can you use it?

Question: What is the ChangeTracker in EF Core, and how would you use it to track changes to an entity?

Answer: The ChangeTracker in EF Core is used to track the state of entities and detect changes to their properties. It can be used to inspect and modify the state of entities (e.g., Added, Modified, Deleted).

Example of using ChangeTracker:

var customer = context.Customers.FirstOrDefault();
customer.Name = "Updated Name";

var entry = context.Entry(customer);  // Access the ChangeTracker for this entity
Console.WriteLine(entry.State);  // Prints the state (Modified in this case)
context.SaveChanges();

Detecting changes for all tracked entities:

foreach (var entry in context.ChangeTracker.Entries())
{
    if (entry.State == EntityState.Modified)
    {
        Console.WriteLine($"Entity {entry.Entity.GetType().Name} was modified");
    }
}

The ChangeTracker provides access to entity states and can help you detect and respond to changes before committing them to the database.

Certainly! Here are even more EF Core technical coding interview questions and answers to help you prepare for your interview:

46. How do you perform a `Left Join` in EF Core?

Question: How would you perform a Left Join in EF Core between the Customer and Order entities, where you want to include customers even if they have no orders?

var leftJoinQuery = from customer in context.Customers
                    join order in context.Orders
                    on customer.CustomerId equals order.CustomerId into customerOrders
                    from order in customerOrders.DefaultIfEmpty()
                    select new
                    {
                        customer.Name,
                        OrderId = order?.OrderId,
                        OrderDate = order?.OrderDate
                    };

var result = leftJoinQuery.ToList();

In this example, DefaultIfEmpty() is used to include all customers, even those without orders, by returning a null value for Order when there is no matching record.

47. What is `Change Tracking` in EF Core, and how does it work?

Question: Explain how change tracking works in EF Core and how you can use it to detect changes in your entities.

Tracking Changes:
- When you load an entity, EF Core tracks the changes made to its properties.
- When you call SaveChanges(), EF Core checks which entities have been modified and generates the necessary SQL to update the database.

Example:

var customer = context.Customers.First();
customer.Name = "Updated Name";  // EF Core tracks this change

context.SaveChanges();  // Generates an UPDATE statement for the changed entity

Changing Tracking Behavior: You can disable change tracking for performance reasons, especially when querying large datasets that you don't intend to modify:
```
var customers = context.Customers
                        .AsNoTracking()  // Disable change tracking for this query
                        .ToList();
```

48. How do you implement `Soft Delete` in EF Core?

Question: How would you implement a soft delete mechanism, where records are marked as deleted without actually being removed from the database?

Entity Setup: Add a IsDeleted flag or DeletedAt property to your entity:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }  // Soft delete flag
}

Mark Entity as Deleted: Instead of calling Remove, you update the IsDeleted flag:

var customer = context.Customers.FirstOrDefault(c => c.CustomerId == 1);
customer.IsDeleted = true;
context.SaveChanges();

Filter Soft-Deleted Records: To exclude soft-deleted records from your queries, use a global query filter:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .HasQueryFilter(c => !c.IsDeleted);  // Filter soft-deleted records globally
    }
}

This approach ensures that IsDeleted acts as a soft delete flag, and you can still access and restore deleted entities if needed.

49. How do you implement `Database Sharding` in EF Core?

Question: How would you implement a sharded database strategy (splitting data across multiple databases or tables) in EF Core?

Create a custom service for sharding: You could create a service that determines which database to use based on the entity or partition key (e.g., CustomerId):

public class ShardedDbContextFactory
{
    private readonly string[] _shardConnectionStrings;
    public ShardedDbContextFactory(string[] shardConnectionStrings)
    {
        _shardConnectionStrings = shardConnectionStrings;
    }

    public ApplicationDbContext GetContext(int customerId)
    {
        var shardIndex = customerId % _shardConnectionStrings.Length;
        var connectionString = _shardConnectionStrings[shardIndex];
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        optionsBuilder.UseSqlServer(connectionString);
        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

Use the factory in your application:

var dbContext = dbContextFactory.GetContext(customerId);
var customer = dbContext.Customers.FirstOrDefault(c => c.CustomerId == customerId);

With this approach, you can dynamically route queries to different databases based on customer ID or other partitioning keys.

50. How do you handle `Complex Types` (Value Objects) in EF Core?

Question: How would you map a Complex Type or value object, like an Address, in EF Core, so that it’s stored as a part of another entity?

Define a Complex Type: Create the value object, such as Address:

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}

Add the Complex Type to an Entity: The Address is part of the Customer entity:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }  // Complex type
}

Configure the Complex Type in the OnModelCreating Method: In the DbContext, configure the Address as an owned type:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .OwnsOne(c => c.Address);  // Configure Address as a complex type (owned entity)
    }
}

This will result in the Address being stored in the same table as Customer but as separate columns.

51. How do you handle `Multiple Database Providers` in EF Core?

Question: How would you configure EF Core to use different database providers, such as SQL Server and SQLite, in different environments or situations?

Answer: EF Core supports multiple database providers, and you can configure different providers based on your application's environment.

Install Necessary NuGet Packages: You would need to install the appropriate packages for each database provider:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Configure the DbContext for Multiple Providers: In your Startup.cs (or equivalent) file, you can conditionally configure the database provider based on the environment:

public void ConfigureServices(IServiceCollection services)
{
    if (Environment.IsDevelopment())
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlite("Data Source=app.db"));
    }
    else
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    }
}

By using conditional logic, you can switch between SQL Server, SQLite, or any other provider based on your application's environment or configuration.

52. What is the `ChangeTracker` in EF Core, and how can you use it?

Question: What is the ChangeTracker in EF Core, and how would you use it to track changes to an entity?

Example of using ChangeTracker:

var customer = context.Customers.FirstOrDefault();
customer.Name = "Updated Name";

var entry = context.Entry(customer);  // Access the ChangeTracker for this entity
Console.WriteLine(entry.State);  // Prints the state (Modified in this case)
context.SaveChanges();

Detecting changes for all tracked entities:

foreach (var entry in context.ChangeTracker.Entries())
{
    if (entry.State == EntityState.Modified)
    {
        Console.WriteLine($"Entity {entry.Entity.GetType().Name} was modified");
    }
}

The ChangeTracker provides access to entity states and can help you detect and respond to changes before committing them to the database.