ADO.NET, LINQ, and EF: A Comprehensive Guide with Interview Question

Introduction

Data access in .NET has evolved significantly, from the raw ADO.NET approach to modern ORM solutions like Entity Framework Core (EF Core). In this article, we will explore the history, features, and best practices of ADO.NET, LINQ, and EF Core, including deep dives into core concepts like Code-First, DbContext lifecycle, entity tracking, interceptors, TPT, TPH, TCP inheritance, lazy loading, navigation properties, joins, groupings, and more.

ADO.NET: The Foundation

Before ORM solutions, ADO.NET was the primary way to interact with databases in .NET. It provides low-level access to data via Connections, Commands, DataReaders, and DataAdapters.

Example: ADO.NET Connection & Query Execution

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Students", conn);
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}");
            }
        }
    }
}

Drawbacks of ADO.NET

Requires manual handling of SQL queries.
No built-in change tracking.
Difficult to work with complex relationships.

LINQ (Language Integrated Query)

LINQ simplifies querying by providing a type-safe, SQL-like syntax integrated into C#.

Example: LINQ to Objects

List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };
var evenNumbers = numbers.Where(n => n % 2 == 0).ToList();

foreach (var num in evenNumbers)
{
    Console.WriteLine(num);
}

Example: LINQ to SQL

using (var context = new SchoolContext())
{
    var students = context.Students.Where(s => s.Age > 18).ToList();
    students.ForEach(s => Console.WriteLine(s.Name));
}

Limitations of LINQ to SQL:

Works only with SQL Server.
Limited support for complex relationships.

Entity Framework Core (EF Core)

EF Core is an ORM that abstracts database interactions, enabling developers to use Code-First or Database-First approaches.

Code-First Approach

With Code-First, you define your models in C#, and EF Core generates the database schema.

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

public class SchoolContext : DbContext
{
    public DbSet<Student> Students { get; set; }

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

Migrations & Snapshot Creation

Run the following commands to create migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

DbContext Lifecycle & Entity Tracking

The DbContext in EF Core is responsible for tracking entity changes.

DbContext States

Unchanged: No modifications.
Added: Inserted but not yet saved.
Modified: Updated but not yet saved.
Deleted: Marked for deletion.

Example: Entity Tracking

using (var context = new SchoolContext())
{
    var student = context.Students.FirstOrDefault(s => s.Id == 1);
    student.Name = "Updated Name";

    Console.WriteLine(context.Entry(student).State); // Modified
    context.SaveChanges();
}

Interceptors in EF Core

EF Core interceptors allow you to log or modify SQL queries dynamically.

Example: Logging SQL Queries

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

Register it in `DbContext`:

optionsBuilder.AddInterceptors(new QueryInterceptor());

Types of Relationships

One-to-One

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

      public StudentProfile StudentProfile { get; set; } // Navigation Property
  }

  public class StudentProfile
  {
      public int Id { get; set; }
      public string Address { get; set; }
      public DateTime DateOfBirth { get; set; }

      public int StudentId { get; set; }  // Foreign Key
      public Student Student { get; set; }  // Navigation Property
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Student>()
          .HasOne(s => s.StudentProfile)
          .WithOne(sp => sp.Student)
          .HasForeignKey<StudentProfile>(sp => sp.StudentId);
  }

One-to-Many

  public class Class
  {
      public int Id { get; set; }
      public string ClassName { get; set; }

      public ICollection<Student> Students { get; set; } = new List<Student>(); // Navigation Property
  }

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

      public int ClassId { get; set; } // Foreign Key
      public Class Class { get; set; } // Navigation Property
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Student>()
          .HasOne(s => s.Class)
          .WithMany(c => c.Students)
          .HasForeignKey(s => s.ClassId);
  }

Many-to-Many

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

      public ICollection<StudentCourse> StudentCourses { get; set; } = new List<StudentCourse>(); // Navigation Property
  }

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

      public ICollection<StudentCourse> StudentCourses { get; set; } = new List<StudentCourse>(); // Navigation Property
  }

  // Junction Table (Many-to-Many)
  public class StudentCourse
  {
      public int StudentId { get; set; }
      public Student Student { get; set; }

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

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

      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);
  }

Querying Relationships in LINQ

One-to-One Query

  var studentWithProfile = context.Students
      .Include(s => s.StudentProfile)
      .FirstOrDefault(s => s.Id == 1);

One-to-Many Query

  var classWithStudents = context.Classes
      .Include(c => c.Students)
      .FirstOrDefault(c => c.Id == 1);

Many-to-Many Query

  var studentCourses = context.Students
      .Include(s => s.StudentCourses)
      .ThenInclude(sc => sc.Course)
      .FirstOrDefault(s => s.Id == 1);

Inheritance Strategies

EF Core supports Table Per Type (TPT), Table Per Hierarchy (TPH), and Table Per Concrete Class (TPC).

TPH (Table Per Hierarchy)

Stores all entities in a single table with a discriminator column.

public class Person { 
public int Id { get; set; } 
public string Name { get; set; } 
}
public class Teacher : Person { public string Subject { get; set; } }
public class Student : Person { public int Grade { get; set; } }

TPT (Table Per Type)

Each entity gets its own table.

modelBuilder.Entity<Teacher>().ToTable("Teachers");
modelBuilder.Entity<Student>().ToTable("Students");

TPC (Table Per Concrete Class)

Each derived entity is stored in its own table without a base table.

Strategy	Description	Pros	Cons
TPH (Table Per Hierarchy)	Single table with a Discriminator column	Best performance	Wastes space (null columns)
TPT (Table Per Type)	Separate table for each class	Normalized	More joins (slower)
TPC (Table Per Concrete Class)	Each class gets a separate table	No nulls, no joins	Harder to query

Loading Strategies

Eager Loading: Loads related entities immediately.
Lazy Loading: Loads related entities only when accessed.
Explicit Loading: Loads related data manually.

Eager Loading

Eager loading is when related entities are loaded at the same time as the main entity using the Include() and ThenInclude() methods. This reduces the number of queries by fetching everything in a single database call.

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

    public int ClassId { get; set; }  // Foreign key for Class
    public Class Class { get; set; }  // Reference navigation property

    public ICollection<Course> Courses { get; set; } = new List<Course>(); // Collection navigation property
}

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

    public ICollection<Student> Students { get; set; } = new List<Student>();
}

public class Class
{
    public int Id { get; set; }
    public string ClassName { get; set; }

    public ICollection<Student> Students { get; set; } = new List<Student>();
}

using (var context = new SchoolContext(options))
{
    // Fetch students along with their Class and Courses in a single query
    var students = context.Students
        .Include(s => s.Class)   // Eager load the Class entity
        .Include(s => s.Courses) // Eager load the Courses collection
        .ToList();

    foreach (var student in students)
    {
        Console.WriteLine($"Student: {student.Name}, Class: {student.Class.ClassName}");

        foreach (var course in student.Courses)
        {
            Console.WriteLine($"Enrolled in: {course.CourseName}");
        }
    }
}

Lazy Loading

Lazy loading in Entity Framework Core allows related entities to be loaded automatically when they are accessed for the first time. Here's a full example demonstrating lazy loading using UseLazyLoadingProxies() in an ASP.NET Core application

Requires enabling proxy generation:

builder.Services.AddDbContext<SchoolContext>(options => 
    options.UseSqlServer(connectionString)
           .UseLazyLoadingProxies());

using (var context = new SchoolContext(options))
{
    var student = context.Students.FirstOrDefault(s => s.Id == 1);

    // At this point, Courses are not yet loaded.
    Console.WriteLine("Student fetched!");

    // Lazy loading happens when accessing the navigation property.
    foreach (var course in student.Courses)
    {
        Console.WriteLine($"Enrolled in: {course.CourseName}");
    }
}

Explicit Loading

Explicit loading in Entity Framework Core is a technique where you manually load related entities instead of relying on lazy loading. This is useful when lazy loading is disabled or when you want precise control over when related data is fetched.

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

    // No 'virtual' keyword, as explicit loading does not rely on proxies
    public ICollection<Course> Courses { get; set; } = new List<Course>();
}

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

    public ICollection<Student> Students { get; set; } = new List<Student>();
}

using (var context = new SchoolContext(options))
{
    // Fetch a student without loading related courses
    var student = context.Students.FirstOrDefault(s => s.Id == 1);

    if (student != null)
    {
        Console.WriteLine("Student fetched!");

        // Explicitly load the Courses collection
        context.Entry(student).Collection(s => s.Courses).Load();

        // Now the courses are loaded
        foreach (var course in student.Courses)
        {
            Console.WriteLine($"Enrolled in: {course.CourseName}");
        }
    }
}

Comparison with Lazy, Eager & Explicit Loading

Feature	Lazy Loading	Explicit Loading	Eager Loading
Configuration	Requires `UseLazyLoadingProxies()`	Uses `.Entry().Load()`	Uses `.Include()`
Query Count	Multiple queries (when accessing navigation properties)	Multiple queries (when calling `.Load()`)	Single query
Performance	Can cause N+1 problem	Controlled loading	More efficient but may load unnecessary data
Use Case	Automatic loading when accessed	Precise control over related data loading	When you need related data immediately

Joins & Grouping

Inner Join

var result = from s in context.Students
             join c in context.Courses on s.CourseId equals c.Id
             select new { s.Name, Course = c.Name };

Grouping

var groups = context.Students
    .GroupBy(s => s.CourseId)
    .Select(g => new { CourseId = g.Key, Count = g.Count() })
    .ToList();

Left Join (GroupJoin)

var result = from c in context.Courses
             join s in context.Students on c.Id equals s.CourseId into studentGroup
             from sg in studentGroup.DefaultIfEmpty()
             select new { Course = c.Name, Student = sg?.Name ?? "No Student" };

Nested Grouping

Want to group students by class and then group their courses within each class.

var groupedData = from student in context.Students
                  group student by student.Class.ClassName into classGroup  // First Level: Group by Class
                  select new
                  {
                      ClassName = classGroup.Key,
                      Students = from student in classGroup
                                 group student by student.Name into studentGroup  // Second Level: Group by Student
                                 select new
                                 {
                                     StudentName = studentGroup.Key,
                                     Courses = studentGroup.SelectMany(s => s.Courses)
                                                           .Select(c => c.CourseName)
                                                           .Distinct()
                                 }
                  };

// Display results
foreach (var classGroup in groupedData)
{
    Console.WriteLine($"Class: {classGroup.ClassName}");

    foreach (var studentGroup in classGroup.Students)
    {
        Console.WriteLine($"  Student: {studentGroup.StudentName}");

        foreach (var course in studentGroup.Courses)
        {
            Console.WriteLine($"    Course: {course}");
        }
    }
}

When dealing with hierarchical data (e.g., Categories → Products, Departments → Employees).
When you need multi-level aggregations.

Using let to Calculate a Derived Value

Let's say we have a Student entity with FirstName and LastName. We want to retrieve students with a full name in uppercase.

var studentsWithFullName = from student in context.Students
                           let fullName = student.FirstName + " " + student.LastName // Define temporary variable
                           select new
                           {
                               StudentId = student.Id,
                               FullName = fullName.ToUpper() // Use the `let` variable
                           };

foreach (var student in studentsWithFullName)
{
    Console.WriteLine($"ID: {student.StudentId}, Name: {student.FullName}");
}

Using let in Filtering

Let's find students enrolled in at least 2 courses

var studentsInMultipleCourses = from student in context.Students
                                let courseCount = student.Courses.Count()
                                where courseCount >= 2
                                select new
                                {
                                    student.Name,
                                    EnrolledCourses = courseCount
                                };

foreach (var student in studentsInMultipleCourses)
{
    Console.WriteLine($"{student.Name} is enrolled in {student.EnrolledCourses} courses.");
}

Using let for Nested Queries

Let's say we want to retrieve students who are enrolled in any course with "Math" in its name.

var mathStudents = from student in context.Students
                   let mathCourses = student.Courses.Where(c => c.CourseName.Contains("Math"))
                   where mathCourses.Any() // Filter students who have at least one Math course
                   select new
                   {
                       student.Name,
                       Courses = mathCourses.ToList()
                   };

foreach (var student in mathStudents)
{
    Console.WriteLine($"{student.Name} is taking:");

    foreach (var course in student.Courses)
    {
        Console.WriteLine($" - {course.CourseName}");
    }
}

Query vs. Method Syntax

// Query Syntax
var students = from s in context.Students
               where s.Age > 18
               select s;

// Method Syntax
var studentsMethod = context.Students.Where(s => s.Age > 18).ToList();

Deferred Execution

Queries are not executed immediately but when iterated over.

var students = context.Students.Where(s => s.Age > 18); // Query is not executed yet
var list = students.ToList(); // Execution happens here

Immediate Execution

To force immediate execution, use methods like: ToList(), ToArray(), Count(), FirstOrDefault()

var studentCount = context.Students.Count(); // Query executes immediately

Aggregation

var studentCount = context.Students.Count();
var maxAge = context.Students.Max(s => s.Age);
var avgAge = context.Students.Average(s => s.Age);

Partitioning

var first5Students = context.Students.Take(5).ToList();
var skipFirst5 = context.Students.Skip(5).ToList();

Set Operations(Distinct, Union, Intersect, Except)

var distinctAges = context.Students.Select(s => s.Age).Distinct().ToList();
var commonAges = context.Students.Select(s => s.Age)
                 .Intersect(context.Teachers.Select(t => t.Age))
                 .ToList();

Fluent API vs Data Annotations

Feature	Fluent API	Data Annotations
Configuration	modelBuilder.Entity<Student>()	[Required]
Relationships	HasOne().WithMany()
Indexing	HasIndex(x => x.Name)	`[Index]` (Not supported in EF Core)

Example: Fluent API Configuration

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .Property(s => s.Name)
        .IsRequired();
}

Change Tracking & Entity States

EF Core automatically tracks entity states: Unchanged, Added, Modified, Deleted

var student = context.Students.First();
student.Name = "Updated Name";
Console.WriteLine(context.Entry(student).State); // Modified
context.SaveChanges();

Bulk Operations(ExecuteUpdate, ExecuteDelete)

context.Students.Where(s => s.Age < 15).ExecuteDelete();
context.Students.Where(s => s.Age > 18).ExecuteUpdate(s => s.SetProperty(s => s.Age, s => s.Age + 1));

Transactions

using var transaction = context.Database.BeginTransaction();
context.Students.Add(new Student { Name = "John" });
context.SaveChanges();
transaction.Commit();

Compiled Queries for Performance

private static readonly Func<SchoolContext, int, Student> GetStudentById =
    EF.CompileQuery((SchoolContext context, int id) => context.Students.First(s => s.Id == id));

EF Core has evolved to provide a powerful and flexible ORM solution with features like interceptors, navigation properties, entity tracking, lazy loading, and inheritance strategies. Mastering these concepts will help you ace technical interviews and build robust applications.

Questions You Might Face in interview

How does EF Core track entity changes?

EF Core tracks entity changes using the Change Tracker mechanism. Every time an entity is retrieved, EF Core maintains its original values and monitors modifications

Entity States in EF Core:

State	Description
Unchanged	No modifications detected.
Added	Entity is new and will be inserted.
Modified	Entity is updated.
Deleted	Entity is removed from the database.
Detached	Entity is not being tracked.

Example: Checking Change Tracking

var student = context.Students.FirstOrDefault(s => s.Id == 1);
student.Name = "Updated Name"; 

Console.WriteLine(context.Entry(student).State); // Outputs: Modified
context.SaveChanges();

How to Disable Tracking?

Use .AsNoTracking() to improve performance for read-only queries.

var students = context.Students.AsNoTracking().ToList();

What are shadow properties in EF Core?

Shadow properties exist in the EF Core model but not in the C# class. They are useful for tracking extra data without modifying the entity.

Example: Adding a Shadow Property

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

Example: Accessing a Shadow Property

var createdAt = context.Entry(student).Property("CreatedAt").CurrentValue;

Useful for auditing (CreatedAt, UpdatedAt) without modifying entity classes.

How would you handle multi-tenancy in EF Core?

Multi-tenancy means supporting multiple clients (tenants) with isolated data.

Approaches:

Schema-based (Separate DB Schema per tenant)
Database-based (Separate DB per tenant)
Row-based (Single DB, tenant column in tables)

Example: Global Query Filters for Multi-Tenancy

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

Ensures tenants only access their data.
Works well for row-based multi-tenancy.

How does `AsNoTracking()` affect performance?

AsNoTracking() disables tracking, making queries faster for read-only operations.

Example: With Tracking (Slower)

var students = context.Students.ToList(); // Tracked

Example: Without Tracking (Faster)

var students = context.Students.AsNoTracking().ToList(); // Not tracked

Use for reporting and read-only queries.
Do NOT use if updating entities.

How do you optimize bulk insert/update operations?

Use AddRange() for inserts.

context.Students.AddRange(new Student { Name = "John" }, new Student { Name = "Jane" });
context.SaveChanges();

Use ExecuteUpdate() for bulk updates (EF Core 7+)

context.Students.Where(s => s.Age > 18)
    .ExecuteUpdate(s => s.SetProperty(s => s.Age, s => s.Age + 1));

Much faster than looping and updating individually.

How do you handle concurrency conflicts in EF Core?

EF Core optimistic concurrency control prevents data overwrites.

Using a Row Version Column

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Timestamp] // Marks as concurrency token
    public byte[] RowVersion { get; set; }
}

Handling Conflicts

try
{
    context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
    Console.WriteLine("Concurrency conflict detected!");
}

How to Add a Database View in `DbContext` and Query It in EF Core

CREATE VIEW vw_StudentDetails AS
SELECT s.Id AS StudentId, s.Name AS StudentName, c.ClassName
FROM Students s
JOIN Classes c ON s.ClassId = c.Id;

Create a C# Model for the View

public class StudentDetailView
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }
    public string ClassName { get; set; }
}

Add the View to DbContext

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

    public DbSet<Student> Students { get; set; }
    public DbSet<Class> Classes { get; set; }

    // Add the view
    public DbSet<StudentDetailView> StudentDetails { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Configure the view as a table mapping but make it read-only
        modelBuilder.Entity<StudentDetailView>().ToView("vw_StudentDetails").HasKey(s => s.StudentId);
    }
}

Query the View in LINQ

using (var context = new SchoolContext(options))
{
    var studentDetails = context.StudentDetails.ToList();

    foreach (var student in studentDetails)
    {
        Console.WriteLine($"ID: {student.StudentId}, Name: {student.StudentName}, Class: {student.ClassName}");
    }
}

Filtering and Aggregation with LINQ

var studentsInClassA = context.StudentDetails
    .Where(s => s.ClassName == "Class A")
    .OrderBy(s => s.StudentName)
    .ToList();