ASP.NET Core EFCore Note

2024-07-01

筆記使用 EF Core 的基本要點。

logo

PreSet

安裝 EF Core Power Tool to reverse engineer the database to generate the model classes (or using Scaffold-DbContext).

在 DBContext 加入 LogTo 設定，可以顯示 EF Core 產生的 SQL 語句在 Console 中，方便除錯 😊

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)=> optionsBuilder
        .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
        .UseSqlServer("Data Source=.\\sqlexpress;Initial Catalog=Northwind;Integrated Security=True;TrustServerCertificate=True");

Eager Loading

It will not show the related data (Orders) in the navigation properties in default.

using var context = new NorthwindContext();

var query = from c in context.Customers
            where c.CustomerId == "ALFKI"
            select c;

foreach (var item in query)
{

    Console.WriteLine(item.CustomerId);
    foreach (var order in item.Orders)
    {
        Console.WriteLine($"\t{order.OrderId} - {order.OrderDate:d}");
    }
}

⚡ We can using include to eager loading the related data (Orders).

var query = from c in context.Customers.Include(c => c.Orders)
            where c.CustomerId == "ALFKI"
            select c;

⚡ Or We can using annoymous type to eager loading the related data (Orders).

var query = from c in context.Customers.Include(c => c.Orders)
            where c.CustomerId == "ALFKI"
            select new {c.CustomerId, c.Orders};

We can using ThenInclude to eager loading another related data (OrderDetails).

var query = from c in context.Customers
            .Include(c => c.Orders)
            .ThenInclude(o => o.OrderDetails)
            .AsSplitQuery()
            where c.CustomerId == "ALFKI"
            select c;
foreach (var item in query)
{

    Console.WriteLine(item.CustomerId);
    foreach (var order in item.Orders)
    {
        Console.WriteLine($"\t{order.OrderId} - {order.OrderDate:d}");
        foreach (var detail in order.OrderDetails)
        {
            Console.WriteLine($"\t\t{detail.ProductId} - {detail.UnitPrice}");
        }
    }
}

And we can using AsSplitQuery to split the query (is there any performance issue 🤔).

⚡ Using AsSplitQuery in Entity Framework Core offers these benefits:

  • Reduced Memory Usage: Splits large result sets to avoid high memory consumption.
  • Improved Performance: Executes smaller queries faster.
  • Better Server Handling: Lessens the load on the database server.
  • Avoids SQL Limitations: Prevents hitting size and parameter limits of SQL Server.
  • Optimized Query Plans: Generates more efficient execution plans.

Lazy Loading

Lazy loading is not enabled by default. We need to install Microsoft.EntityFrameworkCore.Proxies package and enable it in the OnConfiguring method.

🚨 We have to check MultipleActiveResultSets=True in the connection string and add UseLazyLoadingProxies() in the OnConfiguring method.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
        .UseLazyLoadingProxies()
        .UseSqlServer("Data Source=.\\sqlexpress;...;MultipleActiveResultSets=True;");

And make sure Virtual keyword in the navigation properties.

[InverseProperty("Customer")]
public virtual ICollection<Order> Orders { get; set; } = new List<Order>();

🚨 Don't use Lazy Loading, it will cause performance issue, the N+1 problem.

Join Loading

Specific using inner join in the query.

// Query Syntax
var query = from o in context.Orders
            join c in context.Customers on o.CustomerId equals c.CustomerId
            where o.CustomerId == "ALFKI"
            select new { c.CompanyName, o.OrderId, o.OrderDate};

// Method Syntax
var query2 = context.Orders
    .Join(
        context.Customers, 
        o => o.CustomerId, 
        c => c.CustomerId, 
        (o, c) => new { c.CompanyName, o.OrderId, o.OrderDate, c.CustomerId })
    .Where(c => c.CustomerId == "ALFKI");

foreach (var item in query)
{
    Console.WriteLine($"{item.CompanyName} {item.OrderId} {item.OrderDate:d}");
}

foreach (var item in query2)
{
    Console.WriteLine($"{item.CompanyName} {item.OrderId} {item.OrderDate:d}");
}

Use Lazy Loading When: Uncertain Data Usage, Reduce Initial Load Time, and Simple Object Structures.

Use Eager Loading When: You Need Related Data Immediately, Performance Considerations, and Complex Data Retrieval.

Use Join: Complex Join Conditions, Performance Optimization, and Ad-Hoc Queries.

Create, Modify and Delete with DBContext

EntityState is used to track the state of an entity.

  • Added: The entity is being tracked by the context but does not yet exist in the database.
  • Unchanged: The entity is being tracked by the context and exists in the database, and its property values have not changed from the values in the database.
  • Modified: The entity is being tracked by the context and exists in the database, and some or all of its property values have been modified.
  • Deleted: The entity is being tracked by the context and exists in the database, but has been marked for deletion from the database the next time SaveChanges is called.

Create

int id = context.Regions.Max(r => r.RegionId) + 1;
var region = context.Regions.Find(id);

if (region == null)
{
    context.Regions.Add(new Region { RegionId = id, RegionDescription = "Cetral" });
    context.SaveChanges();
}


foreach (var item in context.Regions.AsNoTracking())
{
    Console.WriteLine((item.RegionId, item.RegionDescription.Trim()));
}

Modify

int id = 5;
var region = context.Find<Region>(id);

// check if region is not null and then update its data
if (region != null)
{
    region.RegionDescription = "New Region";
    context.SaveChanges();
}

⚡ Another way to update the data.

context.Update(new Region { RegionId = 5, RegionDescription = "Brand New Region" });
context.SaveChanges();

Delete

var region = context.Regions.Find(5);

if (region is not null)
{
    context.Regions.Remove(region);
    context.SaveChanges();
}

⚡ Another way to delete the data.

context.Remove(new Region { RegionId = 5 });
context.SaveChanges();

⚡⚡ If you want to delete all the data in the table.

context.Regions.ExecuteDelete();

Why AsNoTracking is used in the foreach loop?

if without AsNoTracking:

foreach (var item in context.Regions)
{
    Console.WriteLine((item.RegionId, item.RegionDescription.Trim()));
}

context.Update(new Region { RegionId = 5, RegionDescription = "Brand New Region" });
context.SaveChanges();

🚨 Warning will happend: "The instance of entity type 'Region' cannot be tracked because another instance with the same key value for {'RegionId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using DbContextOptionsBuilder.EnableSensitiveDataLogging to see the conflicting key values."

To fix this issue, we can use AsNoTracking or using context.ChageTracker.Clear().

context.ChangeTracker.Clear();

RawQuery

We can use FromSqlRaw to execute raw SQL query, but it is not recommended, because it have to select all the columns 👎

var query = context.Employees.FromSqlRaw("select * from employees");

Instead of using FromSqlRaw, we can use SqlQuery to execute raw SQL query.

var query = context.Database.SqlQuery<EmployeeView>($"Select employeeid, firstname from employees");

public record EmployeeView(int EmployeeId, string FirstName, int OrderId);

Stored Procedure

We can using SqlQuery to execute stored procedure if result will be mapped to a class.

string id = "ALFKI";

var query = context.Database.SqlQuery<CustOrderHistResult>(
    $"exec CustOrderHist {id}");

foreach (var item in query)
{
    Console.WriteLine($"{item.ProductName} {item.Total}");
}

public record CustOrderHistResult(string ProductName, int Total);

If not mapped to a class, we can use ExecuteSqlInterpolated to execute stored procedure.

Add a cusomt stored procedure in the database.

CREATE PROCEDURE [dbo].AddRegion	
	@regionID int,
	@regionDescription nvarchar(50)
AS
	insert into Region (RegionID, RegionDescription) values (@regionID, @regionDescription)
RETURN 0
context.Database.ExecuteSqlInterpolated($"exec AddRegion @regionid={id}, @regiondescription={description}");

ASP.NET Web API Rock with EF Core

NuGet Install

We can use EF Core to connect to database, and we can use Microsoft.EntityFrameworkCore.SqlServer to connect to SQL Server.

And we install Microsoft.EntityFrameworkCore.Tools to use add-migration and update-database commands.

<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.6">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
</ItemGroup>

DBContext

And then we add a Data folder and add a DbContext file to connect to database.

using Microsoft.EntityFrameworkCore;
using Mod06.Models;

namespace Mod06.Data;

public class TodoContext(DbContextOptions<TodoContext> options): DbContext(options)
{
    public DbSet<TodoItem> TodoItems { get; set; }
}

And then we add ConnectionStrings in appsettings.json.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=.\\sqlexpress;Database=MyDatabase;Integrated Security=True;Encrypt=True;Trust Server Certificate=True;"
  },
}

Register DbContext And Migrations Database

After that we add srvices in program.cs.

builder.Services.AddDbContext<TodoContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

Finally open Package Manager Console and run add-migration InitialCreate and update-database commands.

Olala, we can use EF Core to connect to database 😁

By the way, if something wrong, we can use Remove-Migration to remove migration. But why Remove-Migration instead of manually delete migration file?

Because manually delete doesn't update the model snapshot, which can lead to inconsistencies between your code and the database schema and it doesn't clean up any other related files or code. The database state is not checked, so you might end up with mismatches between your code and the database 😲

SeedData

public class TodoContext(DbContextOptions<TodoContext> options): DbContext(options)
{
    public DbSet<TodoItem> TodoItems { get; set; }

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

        // seed TodoItem releated to cook
        modelBuilder.Entity<TodoItem>().HasData(
            new TodoItem
            {
                Id = 1,
                Name = "Buy milk",
                IsComplete = false
            },
            new TodoItem
            {
                Id = 2,
                Name = "Buy eggs",
                IsComplete = false
            },
            new TodoItem
            {
                Id = 3,
                Name = "Cut onions",
                IsComplete = false
            }
        );
    }
}

We add a controller to get TodoItems.

[Route("api/[controller]")]
[ApiController]
public class TodoItemsController(TodoContext _context) : ControllerBase
{
    // GET: api/<TodoItemsController>
    [HttpGet]
    public async Task<IEnumerable<TodoItem>> Get()
    {
        return await _context.TodoItems.ToListAsync();
    }
}

And how to get only one TodoItem.

[HttpGet("{id}")]
public async Task<ActionResult<TodoItem>> Get(int id)
{
    var todoItem = await _context.TodoItems.FindAsync(id);
    if (todoItem == null)
    {
        return NotFound();
    }
    return todoItem;
}

the difference between IActionResult, ActionResult

  • IActionResult: It can return any type of result with help of Ok(), NotFound(), BadRequest(), etc, but it is not strongly typed.
  • ActionResult: It is strongly typed and can return only one type of result without the help of Ok(), NotFound(), BadRequest(), etc.

tl;dr: Use ActionResult<T> when you want to return a specific type of result.

And how to add, update, delete TodoItem.

// POST api/<TodoItemsController>
[HttpPost]
public async Task<ActionResult<TodoItem>> Post([FromBody] TodoItem todoItem)
{
    _context.TodoItems.Add(todoItem);
    await _context.SaveChangesAsync();

    return CreatedAtAction(nameof(Get), new { id = todoItem.Id }, todoItem);
}

// PUT api/<TodoItemsController>/5
[HttpPut("{id}")]
public async Task<IActionResult> Put(int id, [FromBody] TodoItem todoItem)
{
    if (id != todoItem.Id)
    {
        return BadRequest();
    }

    _context.Update(todoItem);
    await _context.SaveChangesAsync();
    return NoContent();
}

// DELETE api/<TodoItemsController>/5
[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{
    var todoItem = await _context.TodoItems.FindAsync(id);
    if (todoItem == null)
    {
        return NotFound();
    }

    _context.TodoItems.Remove(todoItem);
    await _context.SaveChangesAsync();

    return NoContent();
}

Finally we can use .http files or REST Client to test our API.

@url=https://localhost:7127
###
GET {{url}}/api/todoitems

###
GET {{url}}/api/todoitems/1

###
POST {{url}}/api/todoitems
Content-Type: application/json

{
  "name": "New Item",
  "isComplete": false
}

###
PUT {{url}}/api/todoitems/6
Content-Type: application/json

{
  "id": 6,
  "name": "Updated Item",
  "isComplete": true
}

###
DELETE {{url}}/api/todoitems/6
Method If Success Http StatusCode
GET 200 OK
POST 201 Created
PUT 204 No Content
DELETE 204 No Content

What is HATEOAS in RESTful API?

HATEOAS stands for Hypermedia As The Engine Of Application State. It is a constraint of the REST application architecture. A hypermedia-driven site provides information to navigate the site's REST interfaces dynamically by including hypermedia links with the responses.

After post new data, like location to navigate to the new data.

⚡ To save time, we can generate .http file by endpoint explorer.

#ASPNETCore #WebAPI #Dev