Entity Framework Core — LINQ Becomes SQL
With Dapper you wrote every SQL statement for ReadingRoom. Entity Framework Core lets you write queries in C# with LINQ — the compiler checks property names, and EF writes the SQL. The one idea to master is when that SQL actually runs.
The rule
LINQ builds the query;
ToListAsyncruns it;SaveChangesAsyncwrites.
Entities are plain classes. A DbContext is your session with the database, and each DbSet is a table:
public class Book
{
public int Id { get; set; }
public string Title { get; set; } = "";
public string Author { get; set; } = "";
public int Year { get; set; }
}
public class LibraryDb(DbContextOptions<LibraryDb> options) : DbContext(options)
{
public DbSet<Book> Books => Set<Book>();
public DbSet<Member> Members => Set<Member>();
public DbSet<Loan> Loans => Set<Loan>();
}Register it once; each request then gets its own instance by injection:
builder.Services.AddDbContext<LibraryDb>(o =>
o.UseSqlServer(builder.Configuration.GetConnectionString("Library")));
app.MapGet("/books", async (LibraryDb db) =>
await db.Books
.Where(b => b.Year > 2000)
.OrderBy(b => b.Title)
.ToListAsync());By convention, a property named Id is the primary key, and the class Book maps to a table named after its DbSet, Books. Providers exist for SQL Server, PostgreSQL (Npgsql), SQLite and more; you swap them with one Use... call.
Where and OrderBy on a DbSet only describe the query (an IQueryable). Nothing touches the database until you execute it with ToListAsync, FirstOrDefaultAsync, CountAsync or a loop. EF then sends one SQL statement:
SELECT b.Id, b.Author, b.Title, b.Year
FROM Books AS b
WHERE b.Year > 2000
ORDER BY b.TitleWriting is just as direct: db.Books.Add(book); await db.SaveChangesAsync(); sends the INSERT.
The shortcut
Read a LINQ chain left to right and stop at the first executing method. Everything before it becomes SQL; everything after it runs in C# on objects already in memory.
Good chain
db.Books.Where(b => b.Year > 2000).ToListAsync()— the Where is before the executor, so it becomes SQL.
The trap: forgetting await and SaveChangesAsync. Add alone changes nothing in the database.
Drills
Answer fast — trust the rule.
When does var q = db.Books.Where(b => b.Year > 2000); send SQL?
Which call writes a newly added book to the database?
What lifetime does AddDbContext give LibraryDb by default?
Which chain filters inside the database?
Remember
- LINQ builds the query;
ToListAsyncruns it;SaveChangesAsyncwrites. - Everything before the first executing method becomes SQL.
- Register with
AddDbContext; it is scoped, one per request.