Entity Framework Core Architecture & Providers

Entity Framework Core is completely modular. It does not contain any inherent knowledge about SQL Server, PostgreSQL, or SQLite. Instead, it relies on a sophisticated Provider Architecture to interpret our C# code into the precise syntax required by whatever database engine we decide to attach.

1. The Three Layers of EF Core

When you execute a LINQ query, it traverses three distinct architectural layers before the data is returned.

1. The Application Layer (DbContext): This is your C# code. You write standard LINQ commands (e.g., .Where(), .OrderBy()).
2. The Core Engine (EF Core): The framework intercepts your LINQ expression tree. It processes caching, change tracking, and prepares a standard "Query Model".
3. The Database Provider: The specific plugin (e.g., Npgsql for PostgreSQL) receives the "Query Model" and physically translates it into Postgres-specific SQL dialects.

2. Installing Database Providers

Because EF Core is agnostic, we must install the specific driver for our desired database engine via NuGet.

# For Microsoft SQL Server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# For PostgreSQL (Maintained by the open-source community)
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

# For SQLite (Great for mobile apps or local testing)
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

# For Azure Cosmos DB (NoSQL Support!)
dotnet add package Microsoft.EntityFrameworkCore.Cosmos

3. Provider-Specific Behavior

Most LINQ translates perfectly across all providers. However, different databases have different proprietary features. Database providers allow you to utilize these specific features directly from C# via extension methods.

Example: SQL Server "Temporal Tables"

// This method physically does not exist if you are using SQLite.
// It is exclusively injected by the SqlServer provider package!
var historicalData = await _context.Products
    .TemporalAsOf(DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

Example: PostgreSQL "Arrays & JSONB"

// Npgsql allows mapping C# Arrays directly to PostgreSQL array types
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .Property(p => p.Tags) // string[]
        .HasColumnType("text[]");
}

4. Interview Mastery

Q: "Can an application simultaneously connect to a SQL Server database for transactional data and a PostgreSQL database for reporting using the EXACT same EF Core entity classes?"

Architect Answer: "Yes, absolutely. The Entity classes (POCOs) are purely C#. The mapping behavior is defined entirely by the `DbContext`. You would create two isolated Contexts: `AccountingSqlServerContext` and `ReportingPostgresContext`. During the dependency injection phase in `Program.cs`, you configure one context to use `.UseSqlServer()`, and the other to use `.UseNpgsql()`. Because EF Core allows completely separate ModelBuilder configurations per Context, you can map the exact same `User` class to a `[dbo].[Users]` table in SQL Server, and a `public.users` table in Postgres simultaneously without any cross-contamination."