Top 80+ Entity Framework Interview Questions and Answers

Here are the top 80+ Entity Framework interview questions and answers for beginners and professionals.

Other Interview Questions – OOPs, C#, SQL, Web API

Basic Entity Framework Interview Questions & Answers

Download all Questions as .PDF

What is Entity Framework?

Entity Framework (EF) is an ORM (Object-Relational Mapper) for .NET. It allows you to interact with databases using C# code, instead of writing SQL queries.

Example:
If you have a Product class, EF can automatically create a table for it in the database and handle operations like adding, updating, or retrieving products without writing SQL code.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
} 

using (var context = new AppDbContext())
{
    var product = new Product { Name = "Laptop", Price = 1200.00M };
    context.Products.Add(product);
    context.SaveChanges();
} 

What are the main advantages of using Entity Framework?

The main advantages of using Entity Framework are:

1. Reduces SQL Code: Allows developers to focus on C# code without manually writing SQL.
2. Automates Database Operations: Handles CRUD operations automatically.
3. Simplifies Data Access: Provides an easier way to interact with databases using LINQ.
4. Change Tracking: Tracks object changes and updates the database accordingly.
5. Migration Support: Manages database schema changes with migrations.
6. Cross-Database Compatibility: Supports multiple database providers, like SQL Server, MySQL, and PostgreSQL.

Explain the difference between Database-First, Model-First, and Code-First approaches.

The three approaches in Entity Framework are:

• Database-First: The database is created first, and then EF generates classes based on the existing database schema.
  example: You have a database with tables, and EF generates C# models like ‘Customer’ and ‘Order’ from it.
• Model-First: You create a visual model using a designer, and EF generates the database and code based on that model.
  example: You use a diagram to design entities and relationships, then EF creates both the database tables and C# classes.
• Code-First: You write the C# classes first, and EF generates the database schema based on those classes.
  example: You create a ‘Product’ class in C#, and EF automatically generates the corresponding database table.

What is a DbContext in Entity Framework?

DbContext is the primary class for interacting with the database. It acts as a bridge between application and the database, managing the retrieval and saving of data.

Key responsibilities:

• Managing database connections
• Tracking changes to entities
• Executing queries and saving data

In simple terms, it’s the class you use to query and save data to the database.

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
} 

What is the purpose of the DbContextOptions class?

The DbContextOptions class is used to configure how a DbContext should behave and interact with the database. It holds configuration options such as the database provider (SQL Server/ MySQL), connection string, logging & diagnostics, Other Options (like lazy loading, query tracking, and caching) and it passes these options to the DbContext.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Product> Products { get; set; }
} 

var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
optionsBuilder.UseSqlServer("YourConnectionString");

// Passing options to the DbContext
using (var context = new AppDbContext(optionsBuilder.Options))
{
    // Your database operations here
} 

What is a DbSet in Entity Framework?

A DbSet represents a collection of entities that you can query from and save to the database. It corresponds to a table in the database, and each entity in the DbSet is a row in that table.

For example, if you have a DbSet<Product>, it represents the Products table in the database.

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; } // Products table
} 

How do you define a primary key in Entity Framework?

There are two way to define a Primary Key:

1. The Primary key is usually defined by creating a property named Id or <ClassName>Id in the entity class. EF will automatically treat this as the primary key.

public class Product
{
    public int Id { get; set; } // Primary Key
    //or
    public int ProductId { get; set; } // Primary Key

    public string Name { get; set; }
    public decimal Price { get; set; }
} 

2. If you want to explicitly define the primary key, you can use the [Key] attribute.

public class Product
{
    [Key]
    public int ProductId { get; set; } // Primary Key
    public string Name { get; set; }
} 

How can you configure a one-to-many relationship in Entity Framework?

A one-to-many relationship is configured by having one entity reference another entity (one side), and the other entity hold a collection of the first entity (many side).

Example: A Category can have many Products, but each Product belongs to one Category.

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }

    // One-to-Many: A Category has many Products
    public List<Product> Products { get; set; } 
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    // Foreign Key: Product belongs to one Category
    public int CategoryId { get; set; }
    public Category Category { get; set; }
} 

What is the purpose of the OnModelCreating method in Entity Framework?

The OnModelCreating method in Entity Framework is used to configure the model and relationships between entities when the database is being created. It allows you to customize entity configurations that cannot be done through attributes.

It helps in fine-tuning how the entities are mapped to the database.

Common uses:

• Defining relationships (one-to-many, many-to-many)
• Setting up database schema rules (table names, column properties)
• Configuring keys, indexes, and constraints

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Example: Configuring a one-to-many relationship
    modelBuilder.Entity<Product>()
        .HasOne(p => p.Category)
        .WithMany(c => c.Products)
        .HasForeignKey(p => p.CategoryId);
} 

What is Lazy Loading in Entity Framework?

Lazy Loading in Entity Framework is a feature where related data is loaded from the database only when it is accessed for the first time, rather than loading it upfront with the main entity.

This helps improve performance by fetching data only when needed.

For example, if you load a ‘Product’ without accessing its related ‘Category’, the ‘Category’ data will not be loaded until you explicitly access it.

Lazy Loading can be enabled by making navigation properties virtual

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    // Lazy Loading: Related data is loaded only when accessed
    public virtual Category Category { get; set; }
} 

What is Eager Loading in Entity Framework?
What is the purpose of the Include method in EF?

Eager Loading or Include() method is a technique where related data is loaded from the database along with the main entity, in a single query. This is done to avoid multiple round-trips to the database.

By default, Entity Framework uses lazy loading, meaning related entities are only loaded when accessed.

Use the Include() method to specify which related data to load.

var products = context.Products
                      .Include(p => p.Category) // Eagerly loads related Category
                      .ToList(); 

What is Explicit Loading in Entity Framework?

Explicit Loading is a technique where you manually load related data after the main entity is already retrieved. It gives you full control over when to load the related data.

Unlike lazy loading, explicit loading doesn’t automatically load related data. You must explicitly tell Entity Framework to load it using the Load() method.

var product = context.Products.First(p => p.ProductId == 1);

// Manually load the related Category
context.Entry(product)
       .Reference(p => p.Category) // For a reference property
       .Load(); 

How does Entity Framework differ from ADO.NET?

What are Navigation Properties in Entity Framework?

Navigation Properties allows you to navigate and manage relationships between entities. They represent the associations between different entities (like one-to-many or many-to-many relationships) and enable easy access to related data.

For example: In a one-to-many relationship, a ‘Category’ can have multiple ‘Products’, and a ‘Product’ can belong to one ‘Category’.

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }

    // Navigation Property (One-to-Many)
    public List<Product> Products { get; set; } 
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    // Navigation Property (Many-to-One)
    public Category Category { get; set; }
} 

How do you configure a many-to-many relationship in Entity Framework?

You can configure a many-to-many relationship by simply using navigation properties in both entities. EF Core automatically creates the join table and you need not to explicitly configure anything unless you want to customize it.

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

    // Navigation property for the many-to-many relationship
    public List<Course> Courses { get; set; }
}

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

    // Navigation property for the many-to-many relationship
    public List<Student> Students { get; set; }
} 

public class SchoolContext : 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);
    }
} 

How can you enable and disable Lazy Loading in Entity Framework?

You can enable or disable Lazy Loading at both the context level and property level.

1. At Context Level: Lazy loading is disabled by default, you can enable it by calling UseLazyLoadingProxies() in Startup.cs
   ▼ Show example

   public class AppDbContext : DbContext
   {
       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
       {
           optionsBuilder.UseLazyLoadingProxies(); // Enable Lazy Loading
       }
   } 

2. At the Property Level:
   • Enabling: Mark navigation properties as virtual to allow EF to override them with proxies for lazy loading.
     ▼ Show example

     public class Product
     {
         public int ProductId { get; set; }
         public string Name { get; set; }
     
         public int CategoryId { get; set; }
     
         // Enable lazy loading for this navigation property
         public virtual Category Category { get; set; } 
     } 

   • Disabling: Do not mark the navigation property as virtual if you want to disable lazy loading for a specific property.
     ▼ Show example

     public class Product
     {
         public int ProductId { get; set; }
         public string Name { get; set; }
     
         public int CategoryId { get; set; }
     
         // Disable lazy loading by omitting 'virtual'
         public Category Category { get; set; } 
     } 

What are Data Annotations in Entity Framework?

Data Annotations in Entity Framework are attributes you can apply to your model classes and properties to configure how they should be mapped to the database. These annotations allow you to control things like validation, relationships, and schema details directly within the code, without needing to configure these settings in the DbContext.

[Table("Products")]
public class Product
{
    [Key]
    public int ProductId { get; set; }
	
    [Column("Product_Name")]
    [Required]
    public string Name { get; set; }
	
    [MaxLength(100)]
    public string Name { get; set; }
	
    [StringLength(100, MinimumLength = 5)]
    public string Name { get; set; }
	
    public int CategoryId { get; set; }

    [ForeignKey("CategoryId")]
    public Category Category { get; set; }
	
    [ConcurrencyCheck]
    public string Name { get; set; }
	
    [Timestamp]
    public byte[] RowVersion { get; set; }	
} 

How do you handle concurrency in Entity Framework?

Concurrency conflicts occur when multiple users try to update the same data at the same time. To handle these conflicts, you can use optimistic concurrency control. Entity Framework provides mechanisms to detect and resolve these conflicts using Concurrency Tokens.

Ways to Handle Concurrency in Entity Framework:

1. Using a Timestamp/RowVersion Column:

A common approach is to add a Timestamp or RowVersion column to the entity. This column gets updated every time the record is modified, allowing Entity Framework to detect if someone else has updated the record between the time it was read and when an update is attempted.

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    // Timestamp for concurrency control
    [Timestamp]
    public byte[] RowVersion { get; set; }
} 

2. Handling Concurrency Conflicts:

To handle concurrency exceptions, you catch the DbUpdateConcurrencyException in your code and decide how to resolve the conflict.

try
{
    context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
    foreach (var entry in ex.Entries)
    {
        if (entry.Entity is Product)
        {
            var proposedValues = entry.CurrentValues;
            var databaseValues = entry.GetDatabaseValues();

            // Decide how to handle the conflict
            if (databaseValues != null)
            {
                // For example, resolve by choosing the database values
                entry.OriginalValues.SetValues(databaseValues);
            }
        }
    }

    // Retry the save operation
    context.SaveChanges();
} 

3. Marking Properties for Concurrency:

You can also mark specific properties for concurrency tracking using the [ConcurrencyCheck] attribute.

public class Product
{
    public int ProductId { get; set; }

    [ConcurrencyCheck]
    public string Name { get; set; }
} 

What are Migrations in Entity Framework?

Migrations in Entity Framework are a way to manage and apply changes to your database schema over time, keeping it in sync with your model classes. When your model changes (like adding a new property, renaming a column, or creating a new table), migrations allow you to update the database schema without losing data.

Key Concepts of Migrations:

1. Tracking Changes: Migrations track changes made to your model (classes) and generate code to update the database accordingly.
2. Version Control: Each migration is like a version of your database schema, allowing you to move forward or roll back to previous versions.
3. Applying Changes: Migrations apply changes to the database using commands like Add-Migration and Update-Database.

How do you roll back a migration in Entity Framework?

You can revert the database schema to a previous state using the dotnet ef commands. Rolling back a migration removes the changes applied by that migration and updates the schema to a prior version.

You can use the following commands:

How can you seed data in Entity Framework?

Seeding data refers to the process of pre-populating the database with initial or default data. In EF Core, you can seed data during the migration process, ensuring that data is inserted when the database is first created or updated.

Steps to Seed Data in Entity Framework:

1. Override the OnModelCreating Method: You seed data by overriding the OnModelCreating method of your DbContext and using the HasData method to specify the data to be inserted.

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Seeding initial data
        modelBuilder.Entity<Product>().HasData(
            new Product { ProductId = 1, Name = "Laptop", Price = 1200 },
            new Product { ProductId = 2, Name = "Smartphone", Price = 800 }
        );
    }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
} 

2. Apply Migration with Seed Data: Once you’ve defined the seed data, you need to add a migration and update the database for the seeding to take effect.

This will insert the initial data into the Products table during migration.

Important Notes:

• HasData for Update: If the data already exists, HasData will not duplicate it. It only inserts new data.
• IDs are Required: For seeding to work, you must explicitly specify the primary key values (like ProductId in the example above).
• Migrations Only: Seeding through HasData works only during migration; if you use DropCreateDatabaseIfModelChanges, this seeding method doesn’t apply.

Seeding Related Entities: You can also seed related entities by specifying foreign keys.

modelBuilder.Entity<Category>().HasData(
    new Category { CategoryId = 1, Name = "Electronics" }
);

modelBuilder.Entity<Product>().HasData(
    new Product { ProductId = 1, Name = "Laptop", Price = 1200, CategoryId = 1 }
); 

Explain how Entity Framework handles transactions.

There are 3 ways to handle transactions in EF:

1. Automatic Transactions (Default): SaveChanges() is automatically wrapped in a transaction.
   When you call SaveChanges(), Entity Framework wraps all the changes in a transaction. If all the changes succeed, the transaction is committed. If any changes fail, the transaction is rolled back, ensuring that the database remains in a consistent state.
   ▼ Show example

   using (var context = new AppDbContext()) 
   {     
   	var product = new Product { 
   		Name = "Laptop", 
   		Price = 1200 
   	};     
   	context.Products.Add(product);     
   	context.SaveChanges();  // Automatically wrapped in a transaction 
   } 

2. Explicit Transactions: Use BeginTransaction() for manual control over transactions.
   If you need more control over the transaction (e.g., for multiple SaveChanges() calls or to handle complex scenarios), you can manage the transaction explicitly using the DbContextTransaction object.
   ▼ Show example

   using (var context = new AppDbContext()) 
   {     
   	using (var transaction = context.Database.BeginTransaction())     
   	{         
   		try         
   		{             
   			// First operation             
   			var product = new Product { Name = "Smartphone", Price = 800 };             
   			context.Products.Add(product);             
   			context.SaveChanges();  // First save within the transaction             
   			
   			// Second operation             
   			var category = new Category { Name = "Electronics" };             
   			context.Categories.Add(category);             
   			context.SaveChanges();  // Second save within the same transaction             
   			
   			// Commit transaction             
   			transaction.Commit();         
   		}         
   		catch (Exception)         
   		{             
   			// Rollback transaction if an error occurs             
   			transaction.Rollback();         
   		}     
   	} 
   } 

3. Transaction Scope: Use TransactionScope for handling distributed transactions across multiple contexts or databases.
   ▼ Show example

   using (var scope = new TransactionScope()) 
   {     
   	using (var context1 = new AppDbContext())     
   	{         
   		context1.Products.Add(new Product { Name = "Tablet", Price = 500 });         
   		context1.SaveChanges();     
   	}     
   	
   	using (var context2 = new AnotherDbContext())     
   	{         
   		context2.Orders.Add(new Order { OrderId = 1, Quantity = 2 });         
   		context2.SaveChanges();     
   	}     
   	
   	// Commit the transaction across both contexts     
   	scope.Complete(); 
   } 

What is ChangeTracker in EF?

The ChangeTracker is responsible for keeping track of the state of entities within the DbContext. It monitors changes made to the entities and determines what updates need to be sent to the database when SaveChanges() is called.

• It tracks the state of each entity (Added, Modified, Deleted, Unchanged, or Detached).
• This information is used to decide what operations (INSERT, UPDATE, DELETE) need to be performed in the database.
• You can enable or disable automatic change detection using the AutoDetectChangesEnabled property.
  
  context.ChangeTracker.AutoDetectChangesEnabled = false;
• You can access all tracked entities via ChangeTracker.Entries() to inspect their current state.
• You can use ChangeTracker for auditing purposes (e.g., to see what entities are being modified before saving changes) or for debugging entity state issues.

using (var context = new AppDbContext())
{
    var product = context.Products.Find(1);
    product.Name = "New Laptop Name";

    // Inspecting the entity state
    var state = context.Entry(product).State;  // Returns EntityState.Modified

    // Access all tracked entities
    var trackedEntities = context.ChangeTracker.Entries();

    context.SaveChanges();  // ChangeTracker detects changes and updates the database
} 

Advanced Entity Framework Interview Questions

What is the difference between AsNoTracking and AsTracking?

In Entity Framework, AsTracking and AsNoTracking control how entities are tracked by the ChangeTracker in the DbContext.

• AsTracking (Default Behavior): Entities are tracked by the DbContext for changes. When you query data with AsTracking, EF Core tracks the returned entities and keeps track of any modifications made to them. This allows you to update or delete the entities easily by calling SaveChanges(). Use AsTracking when you modify or delete the returned entities.
  ▼ Show example

  var product = context.Products.AsTracking().FirstOrDefault(p => p.ProductId == 1); 
  product.Name = "Updated Name"; 
  context.SaveChanges();  // The changes will be tracked and saved 

• AsNoTracking: Entities are not tracked by the DbContext. EF Core does not monitor any changes made to the entities, meaning it performs better in read-only scenarios where changes are not needed.
  ▼ Show example

  var product = context.Products.AsNoTracking().FirstOrDefault(p => p.ProductId == 1);
  product.Name = "Updated Name";
  context.SaveChanges();  // Changes will NOT be saved because the entity is not tracked

How do you perform raw SQL queries in Entity Framework?

Raw SQL queries can be executed directly on the database using methods like FromSql(or FromSqlRaw) for querying entities and ExecuteSql(ExecuteSqlRaw or ExecuteSqlCommand) for non-query operations such as inserts, updates, and deletes.

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

var productNames = context.Products
    .FromSqlRaw("SELECT Name FROM Products")
    .Select(p => p.Name)
    .ToList(); 

int rowsAffected = context.Database.ExecuteSqlRaw(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {0}", 1); 

What is a Complex Type in Entity Framework?

A Complex Type in Entity Framework is a class that does not have a primary key and cannot exist independently in the database. It is used to represent a group of related fields (properties) within an entity, but it doesn’t get its own table. Instead, the properties of the complex type are stored in the same table as the owning entity.

Characteristics of a Complex Type:

• No Primary Key: Unlike entities, complex types do not have a primary key and cannot be queried or tracked individually.
• Embedded in an Entity: Complex types are always part of another entity and cannot exist on their own.
• Reusability: You can reuse a complex type across multiple entities, encapsulating common properties.
• Stored in the Same Table: The properties of a complex type are mapped to columns in the same table as the entity it belongs to.

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

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

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

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

How does Entity Framework support TPT, TPH, and TPC inheritance strategies?

Entity Framework (EF) supports three types of inheritance mapping strategies to model inheritance in relational databases: TPT (Table Per Type), TPH (Table Per Hierarchy), and TPC (Table Per Concrete Class). These strategies allow you to represent object-oriented inheritance in your data models while persisting them in relational databases.

1. TPH (Table Per Hierarchy):

In Table Per Hierarchy, all classes in an inheritance hierarchy are stored in a single database table. A discriminator column is used to distinguish between the different entity types.

Pros: Simple to implement. Better performance (single table query).
Cons: The table can become sparse (many NULL values) if the base and derived classes have many different properties.

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

public class Dog : Animal
{
    public string Breed { get; set; }
}

public class Cat : Animal
{
    public bool IsIndoor { get; set; }
} 

modelBuilder.Entity<Animal>()
            .HasDiscriminator<string>("Discriminator")
            .HasValue<Dog>("Dog")
            .HasValue<Cat>("Cat"); 

2. TPT (Table Per Type):

In Table Per Type, each class in the inheritance hierarchy has its own table in the database. The base class’s properties are stored in a base table, and each derived class has a separate table storing only the properties unique to that class.

Pros: No NULL values, as each class has its own table. Normalized table structure.
Cons: Requires JOINs when querying derived types, which can affect performance.

3. TPC (Table Per Concrete Class):

In Table Per Concrete Class, each concrete class (the base class and all derived classes) is mapped to its own table, containing all the properties of that class (including inherited properties). There is no table for the base class.

Pros: No joins are required since each class has its own table. Better performance when querying only derived types.
Cons: Data duplication: Properties inherited from the base class are duplicated in each derived class’s table. More storage space is required due to duplication.

How do you handle database connection strings in Entity Framework?

Here are common ways to manage connection strings in EF Core:

1. Storing Connection Strings in appsettings.json
   ▼ Show example
   
   

   This is a standard approach, where connection strings are stored in the appsettings.json file for easy configuration.

   example:

   {   
   	"ConnectionStrings": 
   		{     
   			"DefaultConnection": "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;"   
   		} 
   	} 

   
   

   Using in DbContext: You can retrieve the connection string in DbContext using dependency injection.

   public class AppDbContext : DbContext
   {
       public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
   
       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
       {
           if (!optionsBuilder.IsConfigured)
           {
               var connectionString = "YourConnectionStringHere";
               optionsBuilder.UseSqlServer(connectionString);
           }
       }
   } 

   
   
2. Accessing Connection Strings via Dependency Injection
   ▼ Show example
   
   

   Retrieve the connection string in Startup.cs and pass it to the DbContext.

   example:

   public class Startup
   {
       public IConfiguration Configuration { get; }
   
       public Startup(IConfiguration configuration)
       {
           Configuration = configuration;
       }
   
       public void ConfigureServices(IServiceCollection services)
       {
           services.AddDbContext<AppDbContext>(options =>
               options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
       }
   } 

   
   
3. Connection Strings for Multiple Environments: Use multiple appsettings.{Environment}.json files (e.g., appsettings.Development.json and appsettings.Production.json) for different configurations based on the environment.
4. Environment-Specific Configuration:
   ▼ Show example
   
   

   Using environment variables or secrets is useful in scenarios where sensitive information like passwords shouldn’t be stored in code.

   
   

   a. Environment Variables: Set the connection string as an environment variable, especially for production environments.

   export ConnectionStrings__DefaultConnection=”YourConnectionStringHere”

   
   

   b. User Secrets: For local development, use User Secrets to securely store the connection string outside of source control.

   dotnet user-secrets set “ConnectionStrings:DefaultConnection” “YourConnectionStringHere”

   
   

   Then, access it in appsettings.json:

   {
     "ConnectionStrings": {
       "DefaultConnection": "YourConnectionStringHere"
     }
   } 

   
   

How do you implement Soft Deletes?

Soft deletes allow you to mark an entity as deleted without actually removing it from the database. This approach helps preserve data history and supports data recovery.

Instead of deleting the entity, set the property value IsDeleted to true in your application code.

public void SoftDeleteProduct(int productId)
{
    var product = context.Products.Find(productId);
    if (product != null)
    {
        product.IsDeleted = true;
        context.SaveChanges();
    }
} 

Explain what the SaveChanges method does internally.

Detects changes, generates commands, executes them in a transaction, and updates Change Tracker.

Example – If you add, modify, or delete several Order and Customer entities and then call SaveChanges, EF Core will:

1. Detect which entities are new, modified, or deleted.
2. Generate the necessary INSERT, UPDATE, and DELETE commands.
3. Begin a transaction.
4. Execute the commands in the correct order.
5. Commit the transaction if all commands succeed or roll back if any command fails.
6. Update the Change Tracker to mark these changes as saved.

How can you log SQL queries generated by Entity Framework?

Entity Framework Core provides built-in support for logging SQL queries, making it easier to monitor or debug the SQL statements generated by your LINQ queries. Here’s how you can log SQL queries in different ways:

1. Using ILogger with Dependency Injection:

public class AppDbContext : DbContext
{
    private readonly ILoggerFactory _loggerFactory;

    public AppDbContext(DbContextOptions<AppDbContext> options, ILoggerFactory loggerFactory)
        : base(options)
    {
        _loggerFactory = loggerFactory;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (_loggerFactory != null)
        {
            optionsBuilder.UseLoggerFactory(_loggerFactory); // Enable logging
        }
    }
} 

public void ConfigureServices(IServiceCollection services)
{
    services.AddLogging(config => {
        config.AddConsole();
        config.SetMinimumLevel(LogLevel.Information); // Choose log level
    });

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

2. Using EnableSensitiveDataLogging: When logging queries, you may need to see parameter values. EF Core’s EnableSensitiveDataLogging method includes these values but should only be used in development, as it may expose sensitive data.

optionsBuilder
    .UseSqlServer("YourConnectionString")
    .EnableSensitiveDataLogging(); 

3. Using Application Insights (or Other External Loggers): If your application integrates with logging services like Application Insights or Serilog, you can capture EF Core logs there.

public void ConfigureServices(IServiceCollection services)
{
    services.AddLogging(config => {
        config.AddApplicationInsights("YourInstrumentationKey");
    });
} 

4. Console Logging (For Quick Setup): For simple applications, you can enable console logging directly when configuring the DbContext.

optionsBuilder
    .UseSqlServer("YourConnectionString")
    .LogTo(Console.WriteLine, LogLevel.Information); 

What is Shadow Property in Entity Framework Core?

A Shadow Property in Entity Framework Core is a property that exists only in the database, not in the .NET entity class, for example – “CreatedDate” column in the Database. EF creates and manages these properties and can be useful for storing data you don’t need directly in your entity model.

You can set or access shadow properties using Entry and Property methods.

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property<DateTime>("CreatedDate");  // Define shadow property
    }
} 

public void AddProduct(Product product)
{
    context.Products.Add(product);
    context.Entry(product).Property("CreatedDate").CurrentValue = DateTime.Now;  // Set shadow property
    context.SaveChanges();
} 

What is Global Query Filter in Entity Framework Core?

A Global Query Filter is a filter applied to all queries for a specific entity type. It’s defined in OnModelCreating using the Fluent API and is commonly used for scenarios like soft deletes or multi-tenancy, where you want to filter out records based on a condition (e.g., IsDeleted = false).

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);
} 

How do you use a Composite Key in Entity Framework?

A Composite Key is defined when an entity requires multiple columns to identify a record uniquely. You can configure it using the Fluent API in the ‘OnModelCreating’ method.

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

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

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

What is the purpose of the ModelBuilder class in EF?

The ModelBuilder class in Entity Framework is used to configure entity properties, relationships, and database mappings through the Fluent API. It’s typically used in the OnModelCreating method of DbContext to define table structures, keys, constraints, and other model configurations that are not set by default.

What are the differences between ‘FirstOrDefault’ and ‘SingleOrDefault’?

FirstOrDefault Vs SingleOrDefault

How do you detach an entity from the DbContext?

To detach an entity from the DbContext, use the Entry method combined with State property to set the state of the entity to Detached. Detaching an entity does not affect the database; it simply removes the entity from the context’s tracking.

You can also detach all entities if needed using context.ChangeTracker.Clear(), which detaches all entities being tracked by the context.

var product = context.Products.Find(productId);  // Retrieve the entity
context.Entry(product).State = EntityState.Detached;  // Detach the entity

What is the ‘Find’ method in Entity Framework, and how does it work?

The Find method in Entity Framework retrieves an entity by its primary key. It first checks the Change Tracker for a tracked entity; if found, it returns it without querying the database. If not, it performs a database query to fetch the entity. It returns the entity if found, or null if not.

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

How do you use Stored Procedures with Entity Framework?

In EF, you can use stored procedures by executing raw SQL commands like FromSqlRaw to get records or ExecuteSqlRaw to insert data. EF Core allows mapping stored procedures to entity queries or using them for data modifications.

var categoryParam = new SqlParameter("Category", "Electronics");
var products = context.Products
    .FromSqlRaw("EXEC GetProductsByCategory @Category", categoryParam)
    .ToList(); 

context.Database.ExecuteSqlRaw("EXEC InsertProduct @Name, @Price", 
    new SqlParameter("Name", "Laptop"), 
    new SqlParameter("Price", 1200)); 

What is the difference between Remove and RemoveRange?

Remove: Deletes a single entity from the database. Use when you want to delete one specific entity.

RemoveRange: Deletes multiple entities at once. Accepts a collection of entities. Use when you want to delete multiple entities in a single operation for better performance.

// Remove a single entity
context.Products.Remove(product);

// Remove multiple entities
context.Products.RemoveRange(products); 

How do you handle exceptions in Entity Framework?

Use try-catch blocks to catch specific EF-related exceptions and handle them accordingly. Some common exception types include DbUpdateException, DbUpdateConcurrencyException, and SqlException (for SQL Server).

try
{
    context.SaveChanges();
}
catch (DbUpdateException ex)
{
    // Handle database update issues (e.g., foreign key violations)
    Console.WriteLine("Database update error: " + ex.Message);
}
catch (DbUpdateConcurrencyException ex)
{
    // Handle concurrency conflicts
    Console.WriteLine("Concurrency error: " + ex.Message);
}
catch (Exception ex)
{
    // Handle other exceptions
    Console.WriteLine("An error occurred: " + ex.Message);
} 

What is the difference between DbQuery and DbSet?

Differences between DbQuery and DbSet

Entity Framework with ASP.NET Core

How do you integrate Entity Framework Core with ASP.NET Core?

1. Install EF Core Packages
2. Define DbContext
3. Configure DbContext in Startup or Program
4. Add Connection String
5. Inject and Use DbContext in Controllers

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Product> Products { get; set; }
} 

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<AppDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
        
        services.AddControllers();  // Register MVC or API controllers
    }
} 

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;"
  }
} 

public class ProductsController : ControllerBase
{
    private readonly AppDbContext _context;

    public ProductsController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
    {
        return await _context.Products.ToListAsync();
    }
} 

What is Dependency Injection, and how is it used with Entity Framework in ASP.NET Core?

Dependency Injection (DI) is a design pattern in which dependencies (e.g., services, configurations) are provided to a class rather than the class creating them itself. This promotes loose coupling and makes code easier to maintain and test. In ASP.NET Core, DI is built-in and widely used.

How It’s Used with Entity Framework in ASP.NET Core – Example: you can register your DbContext as a service and inject it where needed (e.g., in controllers or services).

public class ProductsController : ControllerBase
{
    private readonly AppDbContext _context;

    public ProductsController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
    {
        return await _context.Products.ToListAsync();
    }
} 

How do you configure multiple DbContexts in an ASP.NET Core application?

To configure multiple DbContext classes in an ASP.NET Core application, register each DbContext with a unique configuration in ‘Startup.cs’. This allows you to connect to multiple databases or use different contexts for different parts of the application.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    public DbSet<Product> Products { get; set; }
}

public class LoggingDbContext : DbContext
{
    public LoggingDbContext(DbContextOptions<LoggingDbContext> options) : base(options) { }
    public DbSet<LogEntry> LogEntries { get; set; }
} 

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("AppConnection")));
    
    services.AddDbContext<LoggingDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("LoggingConnection")));
    
    services.AddControllers();
} 

public class ProductsController : ControllerBase
{
    private readonly AppDbContext _appContext;

    public ProductsController(AppDbContext appContext)
    {
        _appContext = appContext;
    }
}

public class LogsController : ControllerBase
{
    private readonly LoggingDbContext _logContext;

    public LogsController(LoggingDbContext logContext)
    {
        _logContext = logContext;
    }
} 

How can you implement Unit of Work and Repository patterns with Entity Framework in ASP.NET Core?

To implement the Unit of Work and Repository patterns with Entity Framework in ASP.NET Core, you create repositories for data access operations and a Unit of Work class to coordinate multiple repositories, ensuring atomic operations within a single transaction.

Steps:

1. Create Repository Interface
2. Implement Repository Class
3. Create Unit of Work Interface
4. Implement Unit of Work Class
5. Register Unit of Work in Startup.cs
6. Use Unit of Work in a Controller

public interface IRepository<T> where T : class
{
    Task<IEnumerable<T>> GetAllAsync();
    Task<T> GetByIdAsync(int id);
    void Add(T entity);
    void Remove(T entity);
} 

public class Repository<T> : IRepository<T> where T : class
{
    private readonly AppDbContext _context;
    private readonly DbSet<T> _dbSet;

    public Repository(AppDbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public async Task<IEnumerable<T>> GetAllAsync() => await _dbSet.ToListAsync();
    public async Task<T> GetByIdAsync(int id) => await _dbSet.FindAsync(id);
    public void Add(T entity) => _dbSet.Add(entity);
    public void Remove(T entity) => _dbSet.Remove(entity);
} 

public interface IUnitOfWork
{
    IRepository<Product> Products { get; }
    IRepository<Order> Orders { get; }
    Task SaveAsync();
} 

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
    public IRepository<Product> Products { get; private set; }
    public IRepository<Order> Orders { get; private set; }

    public UnitOfWork(AppDbContext context)
    {
        _context = context;
        Products = new Repository<Product>(context);
        Orders = new Repository<Order>(context);
    }

    public async Task SaveAsync() => await _context.SaveChangesAsync();
} 

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    services.AddScoped<IUnitOfWork, UnitOfWork>();
    services.AddControllers();
} 

public class ProductsController : ControllerBase
{
    private readonly IUnitOfWork _unitOfWork;

    public ProductsController(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task<IActionResult> AddProduct(Product product)
    {
        _unitOfWork.Products.Add(product);
        await _unitOfWork.SaveAsync();
        return Ok();
    }
} 

How do you perform database seeding during application startup in ASP.NET Core?

To perform database seeding during application startup in ASP.NET Core, you can seed data directly within the DbContext or within the application’s startup logic. This approach populates the database with initial data if it’s empty or needs specific default values.

Steps to Seed Data on Startup:

1. Create Seed Method in DbContext: Populate default data if necessary.
2. Call Seed on Startup: Use CreateScope() to run the seeding method during app startup.
3. Optional Use of OnModelCreating: For static data, use HasData in OnModelCreating.

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public void Seed()
    {
        if (!Products.Any())
        {
            Products.AddRange(
                new Product { Name = "Product1", Price = 100 },
                new Product { Name = "Product2", Price = 200 }
            );
            SaveChanges();
        }
    }
} 

public class Program
{
    public static void Main(string[] args)
    {
        var host = CreateHostBuilder(args).Build();
        SeedDatabase(host);
        host.Run();
    }

    private static void SeedDatabase(IHost host)
    {
        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;
            var context = services.GetRequiredService<AppDbContext>();

            context.Database.Migrate(); // Apply migrations
            context.Seed(); // Seed data
        }
    }

    public static IHostBuilder CreateHostBuilder(string[] args) =>
        Host.CreateDefaultBuilder(args)
            .ConfigureWebHostDefaults(webBuilder =>
            {
                webBuilder.UseStartup<Startup>();
            });
} 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>().HasData(
        new Product { Id = 1, Name = "Product1", Price = 100 },
        new Product { Id = 2, Name = "Product2", Price = 200 }
    );
} 

What are the best practices for managing DbContext lifetime in ASP.NET Core?

Here’s a concise list of best practices for managing DbContext lifetime in ASP.NET Core:

1. Use Scoped Lifetime (Default for DbContext): Register DbContext with a scoped lifetime (one instance per request).
2. Avoid Static Fields: DbContext isn’t thread-safe, so avoid using static/global variables.
3. Avoid Singleton Services: Don’t inject DbContext into Singleton services; use IServiceProvider if necessary.
4. Let DI Dispose Automatically: ASP.NET Core disposes DbContext automatically per request; avoid manual disposal.
5. Single Request Lifetime: Limit each DbContext instance to a single request for fresh data.
6. Separate Read/Write DbContexts (Optional): For high-traffic apps, use separate contexts for read and write operations.
7. Configure Connection Resiliency: For SQL Server, use EnableRetryOnFailure() to handle transient errors.
   

   services.AddDbContext<AppDbContext>(options => 
       options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"), 
       sqlOptions => sqlOptions.EnableRetryOnFailure())); 

8. Use AsNoTracking for Reads: For read-only queries, use AsNoTracking to boost performance by skipping change tracking.
   

   var products = await _context.Products.AsNoTracking().ToListAsync(); 

How can you use Entity Framework in a background service in ASP.NET Core?

To use Entity Framework in a background service in ASP.NET Core, inject the DbContext through a scoped dependency within the background service’s execution method. This ensures each background operation gets a fresh DbContext instance per execution cycle. This prevents DbContext lifetime issues in long-running services.

Use IServiceProvider.CreateScope() within ExecuteAsync to create a new scope.

public class MyBackgroundService : BackgroundService
{
    private readonly IServiceProvider _serviceProvider;

    public MyBackgroundService(IServiceProvider serviceProvider)
    {
        _serviceProvider = serviceProvider;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            using (var scope = _serviceProvider.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
                // Use dbContext to interact with the database
                var data = await dbContext.Items.ToListAsync(stoppingToken);
                // Process data
            }

            await Task.Delay(TimeSpan.FromMinutes(5), stoppingToken);
        }
    }
} 

services.AddHostedService<MyBackgroundService>();
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); 

How do you handle connection resiliency with Entity Framework Core in ASP.NET Core?

Configuring EnableRetryOnFailure on the DbContext provides automatic retry logic for transient errors, improving connection resiliency in ASP.NET Core applications.

Steps:

1. Enable Retry Logic: Use the EnableRetryOnFailure option.
2. Customize Retry Policy: You can configure the maximum number of retries, delay between attempts, and specific error codes to retry, which are helpful for tuning resiliency based on your database and application needs.
3. Consider Idempotent Operations: For safe retries, ensure operations are idempotent (i.e., safe to retry without side effects) to avoid unintended consequences from repeated execution.

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"), 
        sqlOptions => sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 5, // Maximum retry attempts
            maxRetryDelay: TimeSpan.FromSeconds(10),  // Delay between retries
            errorNumbersToAdd: null))); // Additional SQL error codes to retry

Performance Tuning and Optimization – Q & A

How do you optimize the performance of queries in Entity Framework?

Short answer – Apply AsNoTracking, limit selected columns, use Include and Where efficiently, avoid early ToList, and consider compiled queries for frequently run operations. These techniques reduce memory usage and improve query speed.

In Detail: To optimize query performance in Entity Framework, use the following techniques:

1. Use ‘AsNoTracking’ for Read-Only Queries: Disables change tracking, reducing overhead for read-only operations.

   var customers = await context.Customers.AsNoTracking().ToListAsync(); 

2. Limit Data with ‘Select’: Retrieve only necessary columns instead of whole entities.

   var names = await context.Customers.Select(c => c.Name).ToListAsync(); 

3. Use ‘Include’ Wisely: Include related data only when necessary to avoid unnecessary joins.

   var orders = await context.Orders.Include(o => o.Customer).ToListAsync(); 

4. Filter Early with ‘Where’: Apply filtering at the database level to minimize data retrieval.

   var activeOrders = await context.Orders.Where(o => o.IsActive).ToListAsync(); 

5. Avoid ‘ToList’ Before Query Completion: Avoid calling ToList too early; chain queries to minimize data pulled into memory.

   var highValueOrders = await context.Orders
       .Where(o => o.TotalAmount > 1000)
       .ToListAsync(); 

6. Use Compiled Queries for Frequent Operations: Compiled queries are pre-optimized for reuse and are faster for repeated queries.

   private static readonly Func<AppDbContext, int, Task<Order>> GetOrderById =
       EF.CompileAsyncQuery((AppDbContext context, int orderId) => 
       context.Orders.Single(o => o.Id == orderId)); 

What is the purpose of ‘AsSplitQuery’ in Entity Framework Core?

AsSplitQuery in Entity Framework Core improves performance by splitting a single query with multiple Include statements into separate SQL queries. This prevents duplication (Cartesian explosion) in complex relationships, reduces memory usage, and can optimize query performance.

When to Use ‘AsSplitQuery’:

• When loading multiple related entities with Include statements (e.g., loading complex object graphs).
• When you notice performance degradation or excessive data being returned due to large joins.

var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .AsSplitQuery() // Executes multiple queries for each include
    .ToListAsync(); 

How can you reduce memory usage when using Entity Framework?

To reduce memory usage in Entity Framework, follow these practices:

1. Use ‘AsNoTracking’ for Read-Only Queries: Disables change tracking, saving memory for read-only data.

   var products = await context.Products.AsNoTracking().ToListAsync(); 

2. Limit Fetched Columns with ‘Select’: Retrieve only necessary columns to minimize data loading.

   var names = await context.Customers.Select(c => c.Name).ToListAsync(); 

3. Use ‘AsSplitQuery’ for Complex Queries: Splits large joins into multiple queries, reducing memory from duplicated data.

   var orders = await context.Orders
       .Include(o => o.Items)
       .AsSplitQuery()
       .ToListAsync(); 

4. Retrieve Data in Batches with ‘Take’: Load data in smaller chunks to avoid loading large datasets all at once.

   var batch = await context.Orders.Take(100).ToListAsync(); 

5. Dispose ‘DbContext’ Correctly: Use a new DbContext per operation to prevent memory leaks.

How does the ‘FromSqlRaw’ method differ from ‘FromSqlInterpolated’?

The FromSqlRaw and FromSqlInterpolated methods are used to execute raw SQL queries.

FromSqlRaw requires manual parameter passing, while FromSqlInterpolated handles parameters automatically in interpolated strings, reducing the risk of SQL injection.

1. FromSqlRaw: Executes a raw SQL query with parameters that must be explicitly passed to prevent SQL injection. Use this for predefined SQL strings.

var customers = context.Customers
    .FromSqlRaw("SELECT * FROM Customers WHERE City = {0}", city)
    .ToList(); 

2. FromSqlInterpolated: Executes an interpolated SQL query, where parameters are automatically handled within the interpolated string, making it easier and safer to use with inline variables.

var customers = context.Customers
    .FromSqlInterpolated($"SELECT * FROM Customers WHERE City = {city}")
    .ToList(); 

What is the purpose of the ‘BatchSize’ parameter in EF?

The BatchSize parameter in Entity Framework limits the number of commands sent in a single batch during SaveChanges. This improves performance in bulk operations by reducing database round-trips and helps avoid timeouts or memory issues.

optionsBuilder.UseSqlServer(connectionString, options => options.MaxBatchSize(100)); 

How do you handle large data sets in Entity Framework?

Use these strategies to handle large data sets in EF efficiently:

1. Paging with ‘Skip’ and ‘Take’: Load data in smaller, manageable chunks.

   var batch = await context.Items.Skip(0).Take(100).ToListAsync(); 

2. Use ‘AsNoTracking’ for Read-Only Data: Disables change tracking, reducing memory usage.

   var data = await context.LargeDataSet.AsNoTracking().ToListAsync(); 

3. Filter Early with ‘Where’: Apply filters to reduce the amount of data retrieved.

   var filteredData = await context.Items.Where(i => i.IsActive).ToListAsync(); 

4. Batch Processing for Bulk Operations: Use BatchSize to process data in batches, minimizing round-trips.

   optionsBuilder.UseSqlServer(connectionString, options => options.MaxBatchSize(100)); 

How does Entity Framework handle caching?

Entity Framework uses first-level caching by default, which caches entities within the scope of a single DbContext instance. When you query for an entity, EF checks the context cache first before querying the database. This helps reduce duplicate database calls within the same context.

using (var context = new AppDbContext())
{
    var product1 = await context.Products.FindAsync(1); // Database call
    var product2 = await context.Products.FindAsync(1); // Retrieved from cache (no DB call)
} 

What is ‘CompiledQuery’ in Entity Framework?

A CompiledQuery in Entity Framework is a pre-compiled LINQ query that improves performance by reusing the query execution plan. This is useful for queries that are frequently executed, as it avoids the overhead of re-compiling the query each time.

private static readonly Func<AppDbContext, int, Task<Product>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext context, int id) => 
        context.Products.Single(p => p.Id == id));

var product = await GetProductById(context, 1); // Executes using the compiled query

How do you use a connection pool with Entity Framework?

Entity Framework Core uses connection pooling automatically when configured with a database provider that supports it (e.g., SQL Server). Connection pooling allows multiple DbContext instances to reuse connections, reducing the overhead of opening and closing database connections.

connection pooling is enabled by default, so simply configuring the connection string with DbContext automatically uses it:

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer("YourConnectionString")); 

No additional configuration is needed to enable pooling; however, you can control pool size with certain providers if needed. Connection pooling improves performance by managing connection reuse internally.

How do you monitor and profile the performance of Entity Framework queries?

To monitor and profile the performance of Entity Framework queries, you can use the following methods:

• Enable Logging: Use ASP.NET Core’s built-in logging to capture SQL queries and execution details.

  services.AddDbContext<AppDbContext>(options =>
      options.UseSqlServer("YourConnectionString")
             .EnableSensitiveDataLogging() // Optional: shows parameter values
             .LogTo(Console.WriteLine));    // Logs to the console
  

• Use Microsoft.EntityFrameworkCore.Diagnostics Events: Monitor EF Core events for detailed query insights by subscribing to diagnostic events.
• Third-Party Profiling Tools: Tools like MiniProfiler, EF Core Profiler, or SQL Server Profiler provide detailed insights into query execution time, frequency, and optimization opportunities.
• Query Execution Plans: Review SQL execution plans in SQL Server Management Studio (SSMS) to understand how queries are processed and identify potential improvements.
• Performance Counters and Application Insights: For production environments, use Application Insights or performance counters to track EF Core performance and detect bottlenecks.

EF Core Features & Others – Q & A

What is the ‘HasQueryFilter’ method used for?

The HasQueryFilter method defines a global filter for entities, automatically applying a condition to all queries for that entity type. This is useful for soft deletes, multi-tenancy, and other scenarios requiring consistent filtering.

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

How does EF Core support ‘spatial data’ types?

Spatial data types are specialized data types used to store geometric and geographic information, such as points, lines, and polygons, representing real-world locations. These data types are often used for mapping, geographic information systems (GIS), and location-based services.

Entity Framework Core supports spatial data types using NetTopologySuite, allowing storage and querying of geographic data like points and polygons.

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer("YourConnectionString", sqlOptions =>
        sqlOptions.UseNetTopologySuite())); 

public class Location
{
    public int Id { get; set; }
    public Point Coordinates { get; set; } // Point is a spatial data type
} 

What is the ‘ToView’ method in Entity Framework Core?

The ToView method maps an entity to a database view rather than a table. It is used when you want to query a view but not allow modifications, as views are typically read-only in EF Core.

Example: If you have a view called ActiveProducts in your database, you can map it to an entity:

modelBuilder.Entity<Product>()
    .ToView("ActiveProducts"); 

Here, Product will be mapped to the ActiveProducts view, allowing you to run queries on it while preventing insert, update, or delete operations.

What is ‘Keyless Entity Type’ in EF Core?

A Keyless Entity Type is an entity that does not have a primary key. (Earlier it is called ‘Query Types’.) It’s useful for mapping database views, raw SQL queries, or other read-only data structures where a primary key isn’t needed.

public class SalesReport
{
    public string ProductName { get; set; }
    public int TotalSales { get; set; }
} 

modelBuilder.Entity<SalesReport>().HasNoKey(); 

How do you handle enum types in EF Core?

Entity Framework Core supports enum types by mapping them to their underlying integer values in the database. You can use enums directly in entity classes, and EF Core will handle conversion automatically.

public enum OrderStatus
{
    Pending,
    Shipped,
    Delivered
}

public class Order
{
    public int Id { get; set; }
    public OrderStatus Status { get; set; } // Enum property
} 

var Orders = context.Orders
			.Where(o => o.Status == OrderStatus.Shipped)
			.ToList(); 

What are Interceptors in EF Core, and how are they used?

Interceptors are components that allow you to intercept and modify operations like database commands, queries, and save actions before they are executed. They’re useful for logging, auditing, and custom validation.

public class MyCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        Console.WriteLine($"Executing Command: {command.CommandText}");
        return base.NonQueryExecuting(command, eventData, result);
    }
} 

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .AddInterceptors(new MyCommandInterceptor())
        .UseSqlServer("YourConnectionString");
} 

How do you use Value Conversions in EF Core?

Value Conversions in Entity Framework Core are used to convert property values between the entity and the database format, allowing you to store complex types, enums, or custom data formats as simpler database types.

public enum PriorityLevel
{
    Low,
    Medium,
    High
}

public class Task
{
    public int Id { get; set; }
    public PriorityLevel Priority { get; set; } // Enum property
} 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Task>()
        .Property(t => t.Priority)
        .HasConversion<string>(); // Stores enum as string
} 

What are backing fields, and how are they used in EF Core?

Backing fields in Entity Framework Core are private fields used to store data for a property, typically when you want to control how the property is accessed or modified. They’re useful for encapsulation or when you need custom logic in getters and setters without exposing the underlying field directly.

public class Product
{
    private decimal _price;

    public int Id { get; set; }
    public decimal Price
    {
        get => _price;
        set => _price = value > 0 ? value : throw new ArgumentException("Price must be positive");
    }
} 

modelBuilder.Entity<Product>()
    .Property(p => p.Price)
    .HasField("_price"); 

How do you implement audit logging in Entity Framework?

To implement audit logging in Entity Framework, you can override the SaveChanges method to track changes and log them before they’re saved to the database. This is useful for recording data changes, timestamps, and users for audit purposes.

public class EntityBase
{
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
} 

public override int SaveChanges()
{
    var entries = ChangeTracker.Entries()
        .Where(e => e.Entity is EntityBase && (e.State == EntityState.Added || e.State == EntityState.Modified));

    foreach (var entry in entries)
    {
        var entity = (EntityBase)entry.Entity;
        entity.UpdatedAt = DateTime.Now;

        if (entry.State == EntityState.Added)
        {
            entity.CreatedAt = DateTime.Now;
        }
    }

    return base.SaveChanges();
} 

What are the security considerations when using Entity Framework?

The following are the key security considerations:

• SQL Injection Prevention: Use parameterized queries or LINQ to avoid SQL injection. Avoid concatenating raw SQL strings.

  var user = context.Users.FirstOrDefault(u => u.Username == username); 

• Sensitive Data Protection: Enable encryption for sensitive fields and use SecureString for in-memory sensitive data. Avoid exposing sensitive data in logs.
• Restricting Permissions: Limit database access permissions based on the principle of least privilege; only grant necessary CRUD operations.
• Proper Exception Handling: Catch and handle exceptions to avoid leaking sensitive error details in logs or UI responses.
• Use AsNoTracking: For read-only data, use ‘AsNoTracking()’ to prevent potential unintended modifications and reduce memory usage.

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

What is the ‘NoTracking’ query used for in EF?

A NoTracking query retrieves data without tracking changes to the entities, meaning Entity Framework won’t monitor or cache these objects. This improves performance and memory usage for read-only operations.

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

How can you disable a migration in EF?

To disable a migration in Entity Framework, you can either remove it or skip applying it.

• Remove Migration: If the migration has not yet been applied to the database, use the Remove-Migration command in the Package Manager Console:
  Remove-Migration
• Skip Applying Migration: If the migration has already been applied, update the database to a previous migration, or delete it manually from the __EFMigrationsHistory table in the database. Then, remove the migration file from the project to avoid it being re-applied.

These methods prevent the migration from being applied or re-applied in future updates.

How do you test Entity Framework code?

To test Entity Framework code, you can use in-memory databases or a mocking framework to simulate a real database without persisting data.

Example – Using the InMemory provider allows testing of EF Core operations without a physical database:

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseInMemoryDatabase(databaseName: "TestDatabase")
    .Options;

using var context = new AppDbContext(options);
context.Products.Add(new Product { Name = "Test Product" });
context.SaveChanges();

var product = context.Products.FirstOrDefault();
Assert.Equal("Test Product", product.Name); 

UseInMemoryDatabase creates a temporary, in-memory database that can be used to test CRUD operations and EF Core logic. This approach is efficient for unit tests, as it avoids external dependencies.

How do you configure Entity Framework for SQL Server in a .NET Core application?

To configure Entity Framework for SQL Server in a .NET Core application, you use the UseSqlServer method in the DbContext configuration, typically in Startup.cs or in the Program.cs file.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer 

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer("YourConnectionString")); 

How do you implement custom conventions in Entity Framework?

To implement custom conventions in Entity Framework Core, you use the OnModelCreating method to define rules that apply to entity properties or configurations globally.

Example – To create a custom convention that adds a prefix to all table names:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        entity.SetTableName("tbl_" + entity.GetTableName());
    }
} 

The custom convention prefixes each table name with “tbl_“. Custom conventions help enforce consistent rules across all entities without repetitive code.

What are the different lifecycle methods available in Entity Framework?

In Entity Framework, lifecycle methods allow you to customize behavior at various stages of the entity lifecycle. Key lifecycle methods include:

• OnModelCreating: Called when the model is being created, allowing configuration of entity relationships, conventions, and more.

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      // Configure entity properties, relationships
  } 

• SaveChanges / SaveChangesAsync: Called before data is saved to the database. You can override these to add custom logic, such as audit logging.

  public override int SaveChanges()
  {
      // Custom logic before saving
      return base.SaveChanges();
  } 

• OnConfiguring: Allows configuration of DbContext options, like specifying the database provider and connection string.

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

How do you configure default schema in EF?

Specify OnModelCreating method in DbContext, this sets a default schema for all tables in the context.

Example – To set the schema to sales:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("sales");
} 

With this configuration, EF Core will create all tables under the sales schema unless overridden for individual entities.

What are the various logging providers available for Entity Framework Core?

Entity Framework Core supports several logging providers for capturing database operations and performance metrics. Common logging providers include:

1. Console Logger: Logs directly to the console, useful for development and debugging.

   optionsBuilder.UseSqlServer("YourConnectionString")
                 .LogTo(Console.WriteLine); 

2. Debug Logger: Logs to the Output window in Visual Studio, ideal for debugging.

   optionsBuilder.UseSqlServer("YourConnectionString")
                 .LogTo(Debug.WriteLine); 

3. Third-Party Providers: Supports popular logging frameworks like Serilog, NLog, and Log4Net by configuring them in Program.cs or Startup.cs.
4. Application Insights: Used for monitoring in production environments, especially in Azure-based applications.

How do you enforce a unique constraint in EF?

To enforce a unique constraint in Entity Framework Core, you use the HasIndex method with the IsUnique option in the OnModelCreating method.

Example – To enforce a unique constraint on the Email property of a User entity:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasIndex(u => u.Email)
        .IsUnique();
} 

How do you manage multi-tenancy with Entity Framework?

To manage multi-tenancy in Entity Framework Core, you typically use a global query filter to restrict data access by tenant ID. This approach filters records by a tenant identifier, allowing each tenant to only access their own data.

public class Order
{
    public int Id { get; set; }
    public int TenantId { get; set; }
    public string ProductName { get; set; }
} 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasQueryFilter(o => o.TenantId == _currentTenantId);
} 

How do you migrate an existing application to EF Core?

To migrate an existing application to Entity Framework Core, you need to follow these main steps:

1. Install EF Core Packages: Add the EF Core packages specific to your database provider (e.g., Microsoft.EntityFrameworkCore.SqlServer).
   dotnet add package Microsoft.EntityFrameworkCore.SqlServer
2. Create a DbContext: Define a new DbContext class that represents your database and includes DbSets for each entity.

   public class AppDbContext : DbContext
   {
       public DbSet<Customer> Customers { get; set; }
       // other DbSets
   
       protected override void OnConfiguring(DbContextOptionsBuilder options)
       {
           options.UseSqlServer("YourConnectionString");
       }
   } 

3. Define Models: Create or update entity classes that match your existing database schema.
4. Run Initial Migration: Generate an initial migration based on your existing schema without applying it to the database.
   dotnet ef migrations add InitialMigration –context AppDbContext
5. Map Existing Tables: Use HasNoKey, HasColumnName, or HasTable in OnModelCreating to map entities to existing tables without modifying their structure.

What are the advantages and disadvantages of using Entity Framework?

Advantages:

• Productivity: Automates database operations, reducing manual SQL coding.
• Change Tracking: Automatically tracks entity changes, simplifying update processes.
• Database Independence: Supports multiple database providers, enabling easy switching.
• Automatic Migrations: Handles schema changes and data migrations efficiently.
• Strongly Typed: Provides compile-time validation for queries, reducing runtime errors.

Disadvantages:

• Performance Overhead: Slower than raw SQL for complex queries or large datasets.
• Less Control: Limited fine-tuning options compared to direct SQL, especially in complex queries.
• Learning Curve: Requires understanding of LINQ, EF configuration, and model relationships.
• Debugging Complexity: Issues can be harder to troubleshoot due to abstraction layers.
• Memory Usage: High memory usage with large datasets due to change tracking and caching.

What are some common pitfalls when using Entity Framework?

Here are some common pitfalls when using Entity Framework:

1. Unintended Lazy Loading: Lazy loading can result in many small queries (N+1 problem), impacting performance when loading related data.
2. Large Data Loads: Loading too much data at once can cause high memory usage and slow query performance.
3. Lack of AsNoTracking: Not using AsNoTracking for read-only queries can lead to unnecessary tracking, increasing memory usage.
4. Inefficient Queries: Complex LINQ queries may translate poorly to SQL, resulting in suboptimal database performance.
5. Ignoring Transactions: Entity Framework handles transactions by default, but complex operations may require explicit transaction management.
6. Overusing SaveChanges: Calling SaveChanges too frequently can result in multiple database trips; batch operations when possible.
7. Migrations Mismanagement: Incorrect migration handling can lead to schema drift or migration conflicts, especially in teams.
8. Not Handling Concurrency: Ignoring concurrency configurations may lead to data conflicts and unexpected overwrites in multi-user environments.

Avoiding these pitfalls can help maintain good performance, stability, and scalability when working with Entity Framework.

How can you optimize performance in Entity Framework?

To optimize performance in Entity Framework, consider these strategies:

1. Use AsNoTracking: For read-only queries, disable change tracking to reduce memory usage.
   
   var products = context.Products.AsNoTracking().ToList();
2. Limit Data with Projections: Select only required fields using .Select() to reduce data load.
   
   var productNames = context.Products.Select(p => p.Name).ToList();
3. Batch Operations: Minimize SaveChanges calls by batching multiple updates in a single transaction.
4. Eager Loading: Use .Include() to load related data in a single query rather than multiple queries.
   
   var orders = context.Orders.Include(o => o.Customer).ToList();
5. Use Compiled Queries: Pre-compile frequently used queries for faster execution, especially in EF Core.
6. Optimize Database Schema: Ensure indexes, foreign keys, and database normalization support EF queries efficiently.