Querying and Saving with EF Core

After this lesson you can

• Query a table with LINQ through a DbSet, and see the SQL that EF Core writes
• Add, change and remove rows with Add, Remove and SaveChangesAsync

Key takeaways

• A LINQ chain on a DbSet is an IQueryable<T>: a query waiting to run, which EF Core turns into one SQL statement.
• An async method such as ToListAsync or FirstOrDefaultAsync runs the query; ToQueryString() shows its SQL without running it.
• Include(c => c.Products) loads related rows. Without it, a category's Products list stays empty.
• Add, Remove and changed properties reach the database only when you call SaveChangesAsync(), which saves them all in one transaction.
• Use AsNoTracking() for read-only queries: it's faster for data you'll only display.

Every example in this lesson uses the Product, Category and NorthwindContext classes from DbContext and Entities, and a context named db.

Querying

Query a DbSet with the LINQ methods you know, and finish with the async version of the last step: ToListAsync, FirstOrDefaultAsync, CountAsync, AnyAsync. They come from Microsoft.EntityFrameworkCore.

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
DbContextOptions<NorthwindContext> options = new DbContextOptionsBuilder<NorthwindContext>().UseSqlServer(connectionString).Options;
using NorthwindContext db = new NorthwindContext(options);

decimal maxPrice = 8m;
IQueryable<Product> query = db.Products
    .Where(p => p.UnitPrice < maxPrice)
    .OrderBy(p => p.ProductName);

Console.WriteLine(query.ToQueryString());
Console.WriteLine();

List<Product> cheap = await query.ToListAsync();
foreach (Product product in cheap)
{
    Console.WriteLine(quot;{product.ProductName} {product.UnitPrice:C}");
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public int? CategoryId { get; set; }

    [Column(TypeName = "money")]
    public decimal? UnitPrice { get; set; }

    public short? UnitsInStock { get; set; }
    public bool Discontinued { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; } = "";
    public string? Description { get; set; }

    public List<Product> Products { get; set; } = new List<Product>();
}

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

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
}

DECLARE @maxPrice money = 8.0;

SELECT [p].[ProductId], [p].[CategoryId], [p].[Discontinued], [p].[ProductName], [p].[UnitPrice], [p].[UnitsInStock]
FROM [Products] AS [p]
WHERE [p].[UnitPrice] < @maxPrice
ORDER BY [p].[ProductName]

Filo Mix $7.00
Geitost $2.50
Guaraná Fantástica $4.50
Konbu $6.00
Rhönbräu Klosterbier $7.75
Tourtière $7.45

• The chain is a query, not a list. db.Products.Where(...).OrderBy(...) is an IQueryable<Product>: a description of a query, waiting to be run. It's the deferred execution from What is LINQ?, taken further: instead of looping over a list, EF Core turns the whole description into SQL.
• ToQueryString() shows that SQL without running it. The variable maxPrice became a parameter, @maxPrice, so the query is safe from SQL injection, just like Dapper's @ parameters.
• ToListAsync() runs it: one query, one trip to the database.

More ways to read:

Without Include, a category's Products list stays empty: EF Core only loads related rows when you ask.

Saving

To change data, change the objects, then call SaveChangesAsync(). The context remembers every entity it loaded or was given (this is called change tracking), works out what's different, and writes the SQL:

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
DbContextOptions<NorthwindContext> options = new DbContextOptionsBuilder<NorthwindContext>().UseSqlServer(connectionString).Options;
using NorthwindContext db = new NorthwindContext(options);

// Add: INSERT
Product snack = new Product { ProductName = "Trail Mix", UnitPrice = 4.50m, UnitsInStock = 20, CategoryId = 3 };
db.Products.Add(snack);
await db.SaveChangesAsync();
Console.WriteLine(quot;Added with a new id: {snack.ProductId > 77}");

// Change: UPDATE
snack.UnitPrice = 5.00m;
await db.SaveChangesAsync();
Product? saved = await db.Products.AsNoTracking().FirstOrDefaultAsync(p => p.ProductId == snack.ProductId);
Console.WriteLine(quot;New price: {saved?.UnitPrice:C}");

// Remove: DELETE
db.Products.Remove(snack);
await db.SaveChangesAsync();
Console.WriteLine(quot;Still there: {await db.Products.AnyAsync(p => p.ProductName == "Trail Mix")}");

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public int? CategoryId { get; set; }

    [Column(TypeName = "money")]
    public decimal? UnitPrice { get; set; }

    public short? UnitsInStock { get; set; }
    public bool Discontinued { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; } = "";
    public string? Description { get; set; }

    public List<Product> Products { get; set; } = new List<Product>();
}

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

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
}

Added with a new id: True
New price: $5.00
Still there: False

• Add marks a new entity to be inserted. After SaveChangesAsync(), its ProductId holds the id the database chose: no OUTPUT INSERTED needed.
• Changing a property of a tracked entity is enough: SaveChangesAsync() notices and runs an UPDATE of just that column.
• Remove marks an entity to be deleted.
• Nothing reaches the database until SaveChangesAsync(). Several changes saved together are sent in one transaction: all of them happen, or none do.
• AsNoTracking() reads without tracking, which is faster for data you'll only display. Use it for read-only queries.

In a controller

Put it together, and a controller needs no DAO at all:

[HttpGet("{id}")]
public async Task<ActionResult<Product>> GetById(int id)
{
    Product? product = await db.Products.FindAsync(id);
    if (product == null)
    {
        return NotFound();
    }
    return product;
}

[HttpPost]
public async Task<ActionResult<Product>> Add(Product product)
{
    db.Products.Add(product);
    await db.SaveChangesAsync();
    return CreatedAtAction(nameof(GetById), new { id = product.ProductId }, product);
}

Check yourself

2 questions to try on the lesson page.

So what

With EF Core, reading is LINQ and writing is changing objects. Finish queries with an async method, call SaveChangesAsync() when you're done changing, and let EF Core write the SQL in between.

Key terms

• LINQ Query
• IQueryable<T>
• Change Tracking
• SaveChanges
• Include
• AsNoTracking

Cheat sheets

• Querying and Saving with EF Core