- A+
所属分类：.NET技术
目录
引入
- 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);
