Understanding LINQ to SQL and LINQ to Entities
LINQ to SQL and LINQ to Entities are both object-relational mapping (ORM) technologies that let you write queries in C# instead of hand-written SQL strings, then have that C# translated into SQL that runs against a relational database. LINQ to SQL, introduced with .NET 3.5, maps directly to SQL Server tables through a DataContext and is now considered legacy. LINQ to Entities is the query layer of Entity Framework (and EF Core today), which is provider-agnostic and can target SQL Server, PostgreSQL, SQLite, and others through pluggable database providers.
Cricket analogy: Think of LINQ to SQL as a commentator who only ever covers matches at Lord's, while LINQ to Entities is like a broadcaster such as Ravi Shastri who can call matches at any ground worldwide because the commentary format adapts to the venue.
How Query Translation Works
Both technologies rely on deferred execution: when you write a LINQ query against an IQueryable<T>, nothing runs immediately. Instead, the query is captured as an expression tree, and only when you enumerate the result (via foreach, ToList(), FirstOrDefault(), and so on) does the provider walk that expression tree and generate the equivalent SQL statement to send to the database. This means you can compose filters conditionally before execution without incurring multiple round-trips.
Cricket analogy: This is like a captain setting the field placements and bowling change before the over starts, comparable to how Rohit Sharma tweaks the field between deliveries, but the actual ball is only bowled, i.e. the query executed, when the umpire signals play.
// LINQ to Entities (EF Core) example
using var context = new StoreDbContext();
var query = context.Orders
.Where(o => o.Status == OrderStatus.Shipped)
.Where(o => o.CreatedAt >= DateTime.UtcNow.AddDays(-30))
.OrderByDescending(o => o.CreatedAt)
.Select(o => new OrderSummary
{
OrderId = o.Id,
CustomerName = o.Customer.Name,
Total = o.Lines.Sum(l => l.Quantity * l.UnitPrice)
});
// Nothing has hit the database yet - query is an IQueryable<OrderSummary>
var recentShippedOrders = await query.ToListAsync(); // translated to SQL hereLINQ to SQL vs LINQ to Entities
Architecturally, LINQ to SQL centers on a DataContext class with attribute-based mapping ([Table], [Column]) that generates one-to-one mappings between classes and SQL Server tables, with limited support for complex inheritance or many-to-many relationships without junction entities. LINQ to Entities, via EF Core's DbContext, supports Fluent API configuration, value converters, owned types, table-per-hierarchy inheritance, and a provider model that decouples the query pipeline from any single database engine, making it the actively developed, recommended choice for new projects.
Cricket analogy: LINQ to SQL's rigid table mapping is like a team that only ever fields the exact same XI regardless of conditions, whereas EF Core's Fluent API is like a captain such as MS Dhoni who reconfigures the batting order and bowling attack based on pitch and opposition.
LINQ to SQL is effectively in maintenance mode and tied to SQL Server on Windows. For any new .NET project, use Entity Framework Core (LINQ to Entities) — it is actively developed, cross-platform, and supports far more database providers and mapping strategies.
Change Tracking and SaveChanges
Both DataContext and DbContext maintain a change tracker that snapshots the state of loaded entities (Unchanged, Added, Modified, Deleted). When you mutate a tracked entity's properties directly in C# and later call SubmitChanges() (LINQ to SQL) or SaveChangesAsync() (LINQ to Entities), the context diffs the current values against the original snapshot and generates the appropriate INSERT, UPDATE, or DELETE statements, wrapping them in a transaction automatically.
Cricket analogy: This is like a scorer keeping a ball-by-ball log during a Test innings; only when the umpire calls stumps does the scorer finalize and publish the complete scorecard reflecting every change since the innings began.
Both LINQ to SQL and LINQ to Entities are vulnerable to the N+1 query problem when lazy loading is enabled: iterating a collection of parent entities and accessing a navigation property inside the loop triggers one extra query per row. Use eager loading (Include() in EF Core) or projection with Select() to fetch related data in a single query.
- LINQ to SQL and LINQ to Entities both translate C# LINQ queries into SQL using deferred execution and expression trees.
- LINQ to SQL only targets SQL Server and is considered legacy technology.
- LINQ to Entities (EF Core) is provider-agnostic and supports SQL Server, PostgreSQL, SQLite, and more.
- Query execution is deferred until enumeration via ToList(), foreach, or similar terminal operations.
- DbContext and DataContext both use change tracking to generate INSERT/UPDATE/DELETE statements on save.
- EF Core's Fluent API offers far richer mapping configuration than LINQ to SQL's attributes.
- Watch for the N+1 query problem with lazy-loaded navigation properties; prefer eager loading or projections.
Practice what you learned
1. Which database engines does LINQ to SQL support?
2. When does a LINQ to Entities query actually execute against the database?
3. What mechanism does EF Core's DbContext use to determine which SQL statements to generate on SaveChanges?
4. What causes the N+1 query problem in an ORM like EF Core?
5. Which configuration approach does EF Core's DbContext support that LINQ to SQL's DataContext does not offer as richly?
Was this page helpful?
You May Also Like
Lambda Expressions in LINQ
Understand how lambda expressions power LINQ's filtering, projection, and aggregation operators, and how they differ from anonymous methods and expression trees.
Expression Trees Explained
Learn what expression trees are, how the compiler builds them from lambdas, and how providers like EF Core walk them to generate SQL.
Custom LINQ Extension Methods
Learn how to write your own LINQ-style extension methods on IEnumerable<T>, including deferred execution with yield return and chaining conventions.
Related Reading
Related Study Notes in Microsoft Technologies
Browse all study notesWindows 10 / UWP Development Study Notes
.NET · 30 topics
Microsoft TechnologiesWindows Batch Scripting Study Notes
Batch · 30 topics
Microsoft TechnologiesMFC (Microsoft Foundation Classes) Study Notes
C++ · 30 topics
Microsoft TechnologiesSilverlight Study Notes
.NET · 30 topics
Microsoft TechnologiesXAML Study Notes
.NET · 30 topics
Microsoft TechnologiesWPF (Windows Presentation Foundation) Study Notes
.NET · 30 topics