筆記使用 EF Core 的基本要點。
![logo](/assets/DotNetIcon.png)
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 timeSaveChanges
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 whyRemove-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.