[toc] # 引入 - Microsoft.EntityFrameworkCore - Microsoft.EntityFrameworkCore.Design - Microsoft.EntityFrameworkCore.SqlServer - Microsoft.EntityFramewor ...
目錄
引入
- Microsoft.EntityFrameworkCore
- Microsoft.EntityFrameworkCore.Design
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
DBFirst MsSqlServer
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.9" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.9">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.9" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="7.0.9">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
測試DbFirst
User表
1.創建測試表
USE [DotNetCoreWeb]
GO
/****** Object: Table [dbo].[User] Script Date: 2023/7/17 17:45:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Age] [int] NULL,
[Sex] [int] NULL,
[Mobile] [nvarchar](50) NULL,
[Emali] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL,
[Account] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Nuget
1.使用Nuget控制台執行下麵代碼,需要替換連接字元串
Scaffold-DbContext "Data Source=192.168.1.11;Initial Catalog=DotNetCoreWeb;Persist Security Info=True;User ID=sa;Password=a123456789A..;TrustServerCertificate=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force
測試
1.使用WebApi測試
using EntityFrameCoreWebApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore.ChangeTracking;
namespace EntityFrameCoreWebApp.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
public class UsersController : ControllerBase
{
DotNetCoreWebContext _context;
public UsersController(DotNetCoreWebContext context)
{
_context = context;
}
[HttpGet]
public IActionResult Get()
{
User user = _context.Users.First(it => it.Id == 1);
return new JsonResult(user);
}
[HttpGet]
public IActionResult GetById(int id)
{
User user = _context.Users.First(it => it.Id == id);
return new JsonResult(user);
}
[HttpPost]
public IActionResult Add(User user)
{
EntityEntry<User> result = _context.Users.Add(user);
int ret = _context.SaveChanges();
return new JsonResult(new { SaveChanges = ret});
}
}
}
CodeFirst MsSqlServer
- 先備份
- EnsureDeleted 有庫則刪除
- EnsureCreated 創建
_context.Database.EnsureDeleted(); //刪除
_context.Database.EnsureCreated(); //創建
保存原始數據並重新創建庫
備份數據
public async Task<IActionResult> BackUpData()
{
List<Role> roles = _context.Roles.ToList();
string basepath = AppContext.BaseDirectory;
XmlSerializer serializer = new XmlSerializer(typeof(List<Role>));
using (FileStream fs = FileEx.Create(basepath + "Role.xml"))
{
serializer.Serialize(fs, roles);
}
await Task.CompletedTask;
return new JsonResult(new { state = true});
}
在OnModelCreating增加
public void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
string backUpPath = AppContext.BaseDirectory + "Role.xml";
if (File.Exists(backUpPath))
{
using (StreamReader sr = File.OpenText(backUpPath))
{
XmlSerializer xmlSerializer = new XmlSerializer(typeof(List<Role>));
List<Role>? roles = (List<Role>?)xmlSerializer.Deserialize(sr);
if (roles != null && roles.Any())
{
modelBuilder.Entity<Role>().HasData(roles);
}
}
}
}
執行codefirst
_context.Database.EnsureDeleted();
_context.Database.EnsureCreated();
遷移命令
- 類似於Git提交記錄
- 生成遷移文件add-migration 遷移文件名稱 , 執行完會生成對應的 Migrations 文件夾
- add-migration db01
- 更新或還原遷移文件到資料庫update-database 遷移文件名稱
- update-databse db01
列印日誌
- 使用 UseLoggerFactory()方法
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Data Source=192.168.1.11;Initial Catalog=DotNetCoreWeb2;Persist Security Info=True;User ID=sa;Password=a123456789A..;TrustServerCertificate=true").UseLoggerFactory(LoggerFactory.Create(configure => configure.AddConsole()));
}
狀態
- Detached 未關聯
- Unchanged 有關聯,但沒有操作
- Deleted 刪除
- Modified修改
- Added 添加
- 執行刪除的時候修改狀態為其他狀態將會執行對應的狀態 例:
- 下方代碼並不會刪除而是執行修改操作
User user = await dbContext.Users.FirstAsync(it => it.Name == "周傑倫");
dbContext.Users.Remove(user);
dbContext.Entry(user).State = EntityState.Modified;
user.Name = "劉德華";
dbContext.SaveChanges();
導航屬性
- 有級聯關係的可以創建導航屬性 如: User 和 Roel 用 RoleId 作為外鍵Id
- 在User類中增加 public virtual Role Role { get; set; } = null!; 為導航屬性
- 在Role類中增加 public virtual ICollection
Users { get; set; } = new List (); 為引用屬性
貪婪載入
使用Include 可以將關聯Role表的User數據全部載入出來
Role role = await dbContext.Roles.Include(it => it.Users).FirstAsync(it => it.Id == 7);
懶載入
第一種方式
- 引入
- 在生成的Context.cs => OnConfiguring(DbContextOptionsBuilder optionsBuilder) 中添加 optionsBuilder.UseLazyLoadingProxies() 這樣查詢的時候不會載入Role數據, 當需要的時候才會載入
第二種方式
- 在實體類中的構造函數使用 ILazyLoader loader 註入 如:
ILazyLoader loader;
public User(ILazyLoader loader)
{
this.loader = loader;
}
private Role _Role;
public virtual Role Role
{
get
{
return loader!.Load(this, ref _Role!)!;
}
set
{
_Role = value;
}
}
設置查詢狀態不跟蹤
- 有些時候不需要對查詢的數據進行增刪改, 所以去掉狀態跟蹤提升性能
全局設置
- 在生成的Context.cs的構造函數中添加
this.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
局部設置
dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
執行時設置
await dbContext.Roles.AsNoTracking().FirstAsync(it => it.Id == 7);
