Most Frequently asked Interview Questions of entity-framework(2024)

Question: What are database migrations in Entity Framework and why are they important?

Answer:

Database migrations in Entity Framework (EF) are a way of managing changes to the database schema over time in sync with the changes made in the application’s data model (e.g., adding/removing fields, changing data types, etc.). Migrations allow developers to evolve the database schema in a structured and manageable way without losing data or causing inconsistency between the application’s model and the actual database.

---

What is a Database Migration in Entity Framework?

A migration represents a set of changes that are applied to the database schema in response to changes made in the application’s data model (the entity classes). These migrations help to automatically generate the SQL needed to update the database schema, ensuring that the database is aligned with the application’s current data model.

There are two main aspects of migrations:

1. Model Changes: Changes made to the entity classes (e.g., adding or removing properties or relationships).
2. Database Changes: The corresponding changes applied to the database schema (e.g., creating new tables, modifying columns, adding foreign keys).

Entity Framework Core provides a built-in migration system to help developers manage these changes in a smooth and controlled manner.

---

How Database Migrations Work

1. Creating a Migration: After modifying the application’s data model (adding, modifying, or removing entities and properties), a migration can be created using the Add-Migration command (for EF6) or dotnet ef migrations add (for EF Core). This command compares the current data model with the previous version and generates a migration file containing the necessary SQL code.

   Example for EF Core:

   dotnet ef migrations add AddCustomerTable

   This command creates a migration file with code that will add or modify the database schema to reflect the model changes.

2. Applying the Migration: Once the migration is created, the changes can be applied to the database using the Update-Database (EF6) or dotnet ef database update (EF Core) command. This updates the database schema to match the current version of the model.

   Example for EF Core:

   dotnet ef database update

3. Managing Multiple Migrations: Over time, as the application evolves, multiple migrations may be created to handle different versions of the schema. EF tracks which migrations have been applied to the database by storing a record in a special table called __EFMigrationsHistory.

4. Rollback and Revert Migrations: If necessary, migrations can be rolled back (or reverted) using the dotnet ef database update <migration-name> command. This allows developers to revert to a previous version of the database schema.

   Example for EF Core:

   dotnet ef database update LastKnownGoodMigration

---

Why Are Database Migrations Important?

1. Consistency Between Code and Database: Migrations help to keep the database schema consistent with the application’s data model. Without migrations, developers would have to manually update the database schema each time the model changes, which could result in errors or mismatches between the code and database.

2. Version Control for the Database: Migrations act as a version control mechanism for the database schema. This is especially important in a team environment, where multiple developers might be working on the same database. By using migrations, changes can be tracked and shared, preventing conflicts and ensuring that everyone works with the same schema.

3. Data Integrity and Safety: Migrations ensure that schema changes are applied in a structured way. This can help avoid situations where database changes cause data corruption or loss, particularly when applied manually without proper testing. Migrations can be tested before being applied to production databases.

4. Support for Continuous Integration and Deployment (CI/CD): In modern DevOps practices, applications and databases are continuously updated in a seamless pipeline. Migrations play an important role in this process by allowing automated database schema updates during CI/CD workflows, ensuring that the database schema evolves in line with application updates.

5. Flexibility and Reversibility: Migrations provide the ability to undo or rollback changes. This is important when there are errors in the migration or when the schema change turns out to be problematic after deployment. Developers can revert to a known good state of the database.

6. Database Portability: Migrations can be applied to different environments (e.g., development, staging, production), making it easier to move applications between environments while ensuring that the database schema matches the application’s current state.

7. Easier Team Collaboration: When working on a team, database migrations make it easier to collaborate because all changes to the database schema are recorded in migration files. Each developer can share their changes, apply migrations, and avoid issues where schema differences cause conflicts or bugs.

---

How to Use Migrations in Practice

1. Add New Models or Update Existing Models: After modifying your data model (e.g., adding new properties or creating new classes), generate a migration.

   dotnet ef migrations add AddNewProperties

2. Apply the Migration: Once you have created the migration, apply it to the database.

   dotnet ef database update

3. Rolling Back Migrations: If you need to undo a migration, use:

   dotnet ef database update <previous-migration>

4. Handling Conflicts: If you and your team members are working on different migrations, sometimes conflicts can arise. Migrations can be merged manually, or you can create new migrations on top of previous ones.

---

Common Migration Commands in Entity Framework Core

• Add Migration: Adds a new migration based on the current model.

  dotnet ef migrations add <MigrationName>

• Update Database: Applies migrations to the database.

  dotnet ef database update

• Remove Migration: Removes the last migration if it hasn’t been applied to the database yet.

  dotnet ef migrations remove

• List Migrations: Lists all migrations that have been applied or are pending.

  dotnet ef migrations list

• Script Migrations: Generates SQL scripts for applying migrations manually.

  dotnet ef migrations script

---

Summary

• Database migrations in Entity Framework provide an automated way to manage and track changes to the database schema in sync with the application’s data model.
• Migrations are important because they help maintain consistency, allow for version control, enable safe schema changes, support CI/CD pipelines, and ensure team collaboration.
• Through the use of migration commands, developers can easily create, apply, and manage database schema changes without manual intervention, reducing the risk of errors and data inconsistencies.

By using migrations, developers can focus more on writing business logic and less on managing database schema changes manually, making the application development process smoother and more efficient.

Question: What is the difference between Entity Framework 6 and Entity Framework Core?

Answer:

Entity Framework 6 (EF6) and Entity Framework Core (EF Core) are both Object-Relational Mapping (ORM) frameworks that help developers interact with databases using .NET objects, but they have several differences in terms of design, features, and performance. EF Core is the next generation of Entity Framework and is designed to address limitations in EF6 while adding new features and improvements.

Here’s a breakdown of the key differences:

---

1. Platform Support

• EF6:
  • EF6 is designed for .NET Framework applications and does not support .NET Core or cross-platform development.
  • It can be used on Windows environments where .NET Framework is supported (e.g., Windows Forms, ASP.NET, and WPF applications).
• EF Core:
  • EF Core is cross-platform and supports .NET Core, as well as .NET Framework and Xamarin, making it suitable for applications that run on multiple platforms, including Windows, Linux, and macOS.
  • EF Core was built with portability in mind, so it works well in cloud and containerized environments.

---

2. Database Providers

• EF6:

  • EF6 provides support for a variety of databases, but it is mostly tied to SQL Server as its primary database provider.
  • It has providers for SQL Server, SQLite, Oracle, and other databases, but support for non-SQL Server databases can be limited.

• EF Core:

  • EF Core supports a wide variety of databases out-of-the-box, including SQL Server, SQLite, PostgreSQL, MySQL, and others.
  • EF Core is more extensible with custom database providers, allowing developers to use less common databases with ease.
  • It also supports In-Memory databases, useful for testing.

---

3. Performance

• EF6:
  • EF6 is considered slower compared to EF Core in certain scenarios. Although EF6 is optimized, it was designed with the .NET Framework, and some operations can be slower when compared to EF Core’s improvements.
• EF Core:
  • EF Core has significant performance improvements over EF6. It is optimized for better handling of large datasets, supports efficient queries, and has reduced overhead.
  • EF Core uses No-Tracking Queries (default behavior) to optimize performance for read-only queries.
  • It also has better handling of bulk operations and more efficient querying techniques.

---

4. Features

• EF6:

  • Lazy Loading is available by default.
  • Supports Stored Procedures and Functions (EF6 provides richer support for these operations).
  • Change Tracking is enabled by default, meaning EF6 automatically tracks changes made to entities.
  • EF6 has full support for Complex Types (unmapped entities, such as value objects).
  • Code First, Database First, and Model First workflows are supported.

• EF Core:

  • Lazy Loading was added in EF Core 2.1, but it is disabled by default and needs to be explicitly enabled.
  • EF Core does not have full support for Complex Types (value objects), though it supports owned types in version 5.0 and later, which is a similar concept.
  • EF Core does not fully support Database-First and Model-First workflows, but Code-First is the primary approach.
  • EF Core supports Shadow Properties (properties that are part of the model but not present in the class) and Global Query Filters for applying filters across all queries in an application (e.g., soft deletes).
  • EF Core also has support for alternate keys, concurrency tokens, and transient state tracking.

---

5. Migrations and Schema Management

• EF6:

  • Migrations in EF6 work well but can sometimes be difficult to manage in large projects, especially when working with multiple teams.
  • EF6 uses Automatic Migrations, but it’s generally recommended to use Explicit Migrations for better control.

• EF Core:

  • EF Core introduced a more streamlined and flexible migration system, making it easier to manage database schema changes in different environments.
  • EF Core supports better handling of non-destructive migrations, allowing you to modify the database schema without losing data in production.
  • EF Core also supports reversing migrations and rolling back changes.

---

6. Querying Capabilities

• EF6:
  • EF6 offers strong querying capabilities via LINQ, and while it supports complex queries, its performance may degrade with complex joins or large datasets.
• EF Core:
  • EF Core provides advanced query optimization features and can generate more efficient SQL queries, reducing overhead in large applications.
  • It also supports eager loading, lazy loading, and explicit loading with a more optimized and flexible query pipeline.

---

7. Support for Non-Relational Databases

• EF6:
  • EF6 has limited support for NoSQL databases, and its main focus is on relational databases like SQL Server.
• EF Core:
  • EF Core is more extensible and supports a variety of non-relational databases (NoSQL), such as Cosmos DB, in addition to traditional relational databases.
  • EF Core allows for better integration with document stores, key-value stores, and other non-relational data sources.

---

8. Dependency Injection

• EF6:
  • EF6 does not have built-in support for dependency injection (DI) because it was designed before DI was widely adopted in .NET.
  • However, DI can be manually configured using third-party libraries or custom code.
• EF Core:
  • EF Core fully integrates with ASP.NET Core’s dependency injection system, making it easy to manage the lifetime and scope of database contexts and services.

---

9. Backward Compatibility

• EF6:
  • EF6 applications are not directly compatible with EF Core. If migrating from EF6 to EF Core, some manual adjustments are required to handle breaking changes.
• EF Core:
  • EF Core is a complete redesign, so there is no backward compatibility with EF6, but it brings several improvements and optimizations to make it a more modern ORM.

---

10. Community and Support

• EF6:
  • EF6 has been around for longer and is still supported, especially for applications built with .NET Framework. It has a well-established user base and community support.
• EF Core:
  • EF Core is the future of Entity Framework and is actively maintained and enhanced by Microsoft and the community. It has a growing support base due to its adoption in modern .NET Core applications.

---

Summary of Key Differences

Feature	EF6	EF Core
Platform Support	.NET Framework (Windows-only)	Cross-platform (.NET Core, .NET 5+)
Performance	Slower in certain scenarios	Faster and more efficient
Supported Databases	SQL Server, SQLite, Oracle, others	SQL Server, SQLite, PostgreSQL, MySQL, and others
Modeling and Migrations	Full support for Database-First, Code-First, Model-First	Primarily Code-First, with limited Database-First and Model-First
Lazy Loading	Enabled by default	Disabled by default, can be enabled
Features	Stored Procedures, Complex Types, etc.	Improved querying, Shadow Properties, Global Query Filters
Database Provider Flexibility	Less extensible	More extensible with better support for non-relational databases

---

Conclusion

EF6 is a mature, feature-rich ORM that works well for applications running on .NET Framework. However, EF Core is the modern, cross-platform ORM framework that is faster, more extensible, and designed for cloud-native and microservices applications. EF Core is the recommended choice for new development, while EF6 remains a solid choice for legacy applications.

Question: How do you manage relationships (One-to-Many, Many-to-Many) in Entity Framework?

Answer:

In Entity Framework (EF), relationships between entities are crucial for defining how data models interact with each other in a database. EF supports different types of relationships, including One-to-Many and Many-to-Many relationships. These relationships are established through navigation properties in the models and the configuration in the DbContext class. Below is how you can manage and configure these relationships.

---

1. One-to-Many Relationship

A One-to-Many relationship exists when a single entity in one table is related to multiple entities in another table. For example, one Customer can have many Orders, but an Order belongs to only one Customer.

Steps to Manage One-to-Many Relationships:

• Entities: Define the navigation properties on both ends of the relationship. In this case, a Customer can have many Orders, and an Order belongs to one Customer.

  public class Customer
  {
      public int CustomerId { get; set; }
      public string Name { get; set; }
  
      // One-to-Many: A Customer can have many Orders
      public ICollection<Order> Orders { get; set; }
  }
  
  public class Order
  {
      public int OrderId { get; set; }
      public DateTime OrderDate { get; set; }
  
      // Many-to-One: An Order belongs to one Customer
      public int CustomerId { get; set; }
      public Customer Customer { get; set; }
  }

• Foreign Key: In the Order class, we define the CustomerId foreign key to establish the relationship between Order and Customer.

• Fluent API Configuration (Optional): If you prefer using the Fluent API for configuration, you can configure the foreign key explicitly.

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Order>()
          .HasOne(o => o.Customer) // An Order has one Customer
          .WithMany(c => c.Orders) // A Customer has many Orders
          .HasForeignKey(o => o.CustomerId); // Foreign Key
  }

Database Schema for One-to-Many:

This relationship is represented in the database as a foreign key in the Orders table pointing to the CustomerId in the Customers table.

Customer	Orders
CustomerId (PK)	OrderId (PK)
Name	OrderDate
	CustomerId (FK)

---

2. Many-to-Many Relationship

A Many-to-Many relationship exists when multiple entities in one table are related to multiple entities in another table. For example, one Student can enroll in many Courses, and one Course can have many Students.

Steps to Manage Many-to-Many Relationships:

In EF Core 5.0 and above, Many-to-Many relationships are supported directly without needing a separate join entity. For versions prior to EF Core 5.0, you would have to manually define the junction table.

Entities:

For EF Core 5.0+, you define the navigation properties for both entities without needing an explicit join entity.

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }

    // Many-to-Many: A Student can enroll in many Courses
    public ICollection<Course> Courses { get; set; }
}

public class Course
{
    public int CourseId { get; set; }
    public string CourseName { get; set; }

    // Many-to-Many: A Course can have many Students
    public ICollection<Student> Students { get; set; }
}

• Junction Table: In EF Core 5.0 and later, EF Core automatically handles the creation of a junction table behind the scenes for many-to-many relationships.

• Fluent API Configuration (Optional): You can explicitly configure the relationship using the Fluent API, but EF Core will automatically handle it for you.

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Student>()
          .HasMany(s => s.Courses)
          .WithMany(c => c.Students)
          .UsingEntity<Dictionary<string, object>>(
              "StudentCourse", // Name of the junction table
              j => j.HasOne<Course>().WithMany().HasForeignKey("CourseId"),
              j => j.HasOne<Student>().WithMany().HasForeignKey("StudentId")
          );
  }

In EF Core versions before 5.0, you need to define a join table explicitly.

public class StudentCourse
{
    public int StudentId { get; set; }
    public Student Student { get; set; }

    public int CourseId { get; set; }
    public Course Course { get; set; }
}

Then, configure the many-to-many relationship with the join entity:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<StudentCourse>()
        .HasKey(sc => new { sc.StudentId, sc.CourseId });

    modelBuilder.Entity<StudentCourse>()
        .HasOne(sc => sc.Student)
        .WithMany(s => s.StudentCourses)
        .HasForeignKey(sc => sc.StudentId);

    modelBuilder.Entity<StudentCourse>()
        .HasOne(sc => sc.Course)
        .WithMany(c => c.StudentCourses)
        .HasForeignKey(sc => sc.CourseId);
}

Database Schema for Many-to-Many:

With EF Core 5.0+, the database schema will look like:

Student	StudentCourse (Junction Table)	Course
StudentId (PK)	StudentId (FK)	CourseId (PK)
Name	CourseId (FK)	CourseName

In EF Core versions before 5.0, the schema will have an explicit junction table:

Student	StudentCourse (Junction Table)	Course
StudentId (PK)	StudentId (FK)	CourseId (PK)
Name	CourseId (FK)	CourseName

---

Managing Relationships with Data Annotations

• One-to-Many: You can use data annotations to define the relationship, though the Fluent API is often preferred for more control.

  public class Order
  {
      [Key]
      public int OrderId { get; set; }
  
      public int CustomerId { get; set; }
  
      [ForeignKey("CustomerId")]
      public Customer Customer { get; set; }
  }

• Many-to-Many: EF Core 5.0+ does not require a join entity for Many-to-Many relationships, but if you need one, you can use data annotations.

  public class StudentCourse
  {
      [Key]
      public int StudentId { get; set; }
  
      [ForeignKey("StudentId")]
      public Student Student { get; set; }
  
      [Key]
      public int CourseId { get; set; }
  
      [ForeignKey("CourseId")]
      public Course Course { get; set; }
  }

---

Summary

• One-to-Many Relationships: In EF, a One-to-Many relationship is managed by defining a navigation property in both the principal and dependent entities, with a foreign key in the dependent entity.

• Many-to-Many Relationships: In EF Core 5.0 and later, Many-to-Many relationships are automatically handled without the need for a join table. Prior to EF Core 5.0, you need an explicit junction table to manage this relationship.

• Data Annotations vs Fluent API: You can use both Data Annotations or the Fluent API to configure relationships. While Data Annotations are simpler, the Fluent API gives you more control, especially for complex relationships.

By using these methods, you can establish relationships between entities in EF, which EF will translate into corresponding foreign key constraints and relationships in the database.

Question: How do you use stored procedures with Entity Framework?

Answer:

In Entity Framework (EF), you can use stored procedures to execute database operations such as querying, inserting, updating, and deleting data. Using stored procedures helps improve performance and maintainability, especially for complex operations that may require direct database manipulation or optimization beyond what EF can handle automatically.

Here are the different ways to use stored procedures with Entity Framework:

---

1. Using Stored Procedures for Queries

To execute a stored procedure that returns data (like a SELECT statement), you can use DbSet.FromSqlRaw() or DbSet.FromSqlInterpolated() (EF Core) or Database.SqlQuery() (EF 6) to call the stored procedure and map the results to entities.

EF Core Example:

Suppose you have a stored procedure GetCustomersByCountry that takes a country parameter and returns a list of customers.

CREATE PROCEDURE GetCustomersByCountry
    @Country NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Customers WHERE Country = @Country;
END

To call this stored procedure and map the results to the Customer entity:

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

    public List<Customer> GetCustomersByCountry(string country)
    {
        return Customers.FromSqlRaw("EXEC GetCustomersByCountry @Country", new SqlParameter("@Country", country)).ToList();
    }
}

EF 6 Example:

In EF 6, you use Database.SqlQuery() to execute the stored procedure.

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

    public List<Customer> GetCustomersByCountry(string country)
    {
        return this.Database.SqlQuery<Customer>("EXEC GetCustomersByCountry @Country", 
                new SqlParameter("@Country", country)).ToList();
    }
}

Return Type:

In both EF 6 and EF Core, the stored procedure result is mapped to a list of entities that match the structure of the result set (e.g., Customer).

---

2. Executing Stored Procedures for Insert, Update, or Delete

You can also use stored procedures to perform Insert, Update, or Delete operations through ExecuteSqlRaw() (EF Core) or ExecuteSqlCommand() (EF 6).

EF Core Example:

Suppose you have a stored procedure for deleting a customer:

CREATE PROCEDURE DeleteCustomer
    @CustomerId INT
AS
BEGIN
    DELETE FROM Customers WHERE CustomerId = @CustomerId;
END

To execute this stored procedure in EF Core:

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

    public void DeleteCustomer(int customerId)
    {
        this.Database.ExecuteSqlRaw("EXEC DeleteCustomer @CustomerId", 
            new SqlParameter("@CustomerId", customerId));
    }
}

EF 6 Example:

For EF 6, you use ExecuteSqlCommand():

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

    public void DeleteCustomer(int customerId)
    {
        this.Database.ExecuteSqlCommand("EXEC DeleteCustomer @CustomerId", 
            new SqlParameter("@CustomerId", customerId));
    }
}

Return Type:

• For Insert, Update, or Delete operations, no result is returned. Instead, ExecuteSqlRaw() or ExecuteSqlCommand() will return the number of affected rows (for logging purposes or exception handling).

---

3. Stored Procedure with Output Parameters

If the stored procedure uses output parameters, you can access the output value by adding the output parameter to the method call.

EF Core Example:

Suppose the stored procedure returns a value using an output parameter.

CREATE PROCEDURE GetCustomerCountByCountry
    @Country NVARCHAR(50),
    @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Customers WHERE Country = @Country;
END

In EF Core, you can use SqlParameter to access the output parameter:

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

    public int GetCustomerCountByCountry(string country)
    {
        var countParam = new SqlParameter("@Count", SqlDbType.Int) 
        { 
            Direction = ParameterDirection.Output 
        };

        this.Database.ExecuteSqlRaw("EXEC GetCustomerCountByCountry @Country, @Count OUT", 
            new SqlParameter("@Country", country), countParam);

        return (int)countParam.Value;
    }
}

Here, the stored procedure sets the value of the @Count parameter, and you retrieve it from the SqlParameter after executing the procedure.

EF 6 Example:

For EF 6, you would use the same approach, leveraging SqlParameter for output parameters:

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

    public int GetCustomerCountByCountry(string country)
    {
        var countParam = new SqlParameter("@Count", SqlDbType.Int) 
        { 
            Direction = ParameterDirection.Output 
        };

        this.Database.ExecuteSqlCommand("EXEC GetCustomerCountByCountry @Country, @Count OUT", 
            new SqlParameter("@Country", country), countParam);

        return (int)countParam.Value;
    }
}

---

4. Using Entity Framework with Complex Stored Procedures

For complex stored procedures that return multiple result sets (e.g., multiple tables or aggregated data), EF may not directly map the results to entities. In such cases, you can execute raw SQL queries and map the results manually, or use a DataReader to process the result sets.

Example: Multi-Result Stored Procedure

CREATE PROCEDURE GetCustomerOrderDetails
    @CustomerId INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerId = @CustomerId;
    SELECT * FROM Orders WHERE CustomerId = @CustomerId;
END

To call the stored procedure and handle multiple result sets:

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

    public (Customer, List<Order>) GetCustomerOrderDetails(int customerId)
    {
        var customerParam = new SqlParameter("@CustomerId", customerId);

        using (var command = this.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = "EXEC GetCustomerOrderDetails @CustomerId";
            command.Parameters.Add(customerParam);

            this.Database.OpenConnection();
            using (var reader = command.ExecuteReader())
            {
                var customer = reader.Read() ? reader.GetCustomer() : null;
                var orders = reader.NextResult() ? reader.GetOrders() : new List<Order>();

                return (customer, orders);
            }
        }
    }
}

You can use custom extensions or a DataReader to extract results from each result set.

---

Summary

• Executing Stored Procedures: Use FromSqlRaw() or ExecuteSqlRaw() in EF Core and SqlQuery() or ExecuteSqlCommand() in EF 6 to interact with stored procedures.

• Output Parameters: Use SqlParameter with the Direction set to Output to retrieve values from output parameters.

• Insert/Update/Delete: Execute these operations through stored procedures using ExecuteSqlRaw() in EF Core or ExecuteSqlCommand() in EF 6.

• Multiple Results: Handle multiple result sets with raw SQL execution or manual mapping using a DataReader.

Using stored procedures with Entity Framework helps improve performance for complex operations while maintaining separation of concerns between business logic and data access.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as entity-framework interview questions, entity-framework interview experiences, and details about various entity-framework job positions. Click here to check it out.