主键命名约定如果实体类属性命名为Id的或者为(实体名Id)的自动为主键如果不符合命名约定使用HasKey(e e.主键属性).HasName(PrimaryKey_属性名)//默认pk_属性名默认外键public class Post { public int PostId { get; set; } public string Title { get; set; } null!; public string Content { get; set; } null!; public int BlogId { get; set; } public Blog? BlogEntity { get; set; } }public class Blog { public int BlogId { get; set; } public string Url { get; set; } null!; public DateTime CreatedTime { get; set; } /// summary /// 最后更新时间 /// /summary public DateTime UpdatedTime { get; set; } public ListPost Posts { get; } new(); }必须要有导航属性Post.Blog是引用导航属性Blog.Posts是集合导航属性只要有其一才会把Post.BlogId当做外键生成2个都没不行。如果依赖实体包含一个名称与以下模式之一匹配的属性则该属性将被配置为外键主实体即被依赖的实体通常是关系里 “一” 的那一方对应的实体的主键属性的名称。外键命名约定当主题属性里有以下其中之一的属性命名则当成外键当有引用导航名1导航属性名主键属性名 BlogEntityBlogId默认2导航属性名Id BlogEntityId当不存在引用导航属性只有集合导航属性3主实体名主键属性名 BlogBlogId默认4主实体名Id BlogId注如果主实体的主键属性名是 BlogId而非 Id则生成的外键列名仍为 BlogId因为主实体类名 主键名会是 BlogBlogId但 EF 会自动简化为 BlogId。举个例子如果主键属性名为BlogKey那么主实体名主键属性名生成的外键将为(Blog BlogKey)Cascade 级联Restrict 限制SetNull 为空SetDefault 为默认核心触发原则最左前缀匹配必须包含左前缀字段查询条件中必须有索引的第一个字段 a否则无法触发组合索引。前缀连续不中断从左到右使用索引字段中间不能跳过。示例索引 idx(a,b,c)查询 where a1 and c2 只能触发 a 的索引where a1 and b2 and c3 可触发完整索引。范围查询会中断后续字段若某个字段使用范围查询如 、、between其右侧的字段无法再使用索引。示例索引 idx(a,b,c)查询 where a1 and b2 只能触发 a 的索引where a1 and b2 and c3 只能触发 a 和 b 的索引。避免函数 / 运算操作索引字段错误示例where substr(a,1,2)ab对 a 用函数。正确示例where a like ab%前缀匹配可触发 a 的索引。尽量实现 “索引覆盖”查询的字段select 后的列若都在组合索引中无需回表查询主键对应的完整数据效率更高。示例索引 idx(a,b,c)查询 select a,b from table where a1 and b2 可实现索引覆盖若查询 select a,b,dd 不在索引中则需回表。假设表 user 有字段id主键、name、age、gender且建立了组合索引 idx(name, age)。1. 触发回表的场景查询语句select id, name, age, gender from user where name张三;步骤 1通过组合索引 idx(name, age)快速找到 name张三 对应的主键 id比如 101。步骤 2因为查询需要 gender 字段但组合索引里没有 gender所以必须用 id101 去主键索引中查找才能获取 gender 的值。结论这一步 “用 id 查 gender” 就是回表多了一次索引查询效率更低。查询url或者其中之一会触发覆盖索引提高查询速度但如果为url和有不在包含列中的属性就不会触发覆盖索引url addresstitle不会触发。覆盖索引是sqlserver独有的mysql用组合索引。DBfirst预加载include显示加载 不推荐对导航实体接着查询延迟加载调试生成sql个数与访问次数相关N1问题访问role一次加上你可能访问的N个导航实体非代理模式拆分查询-防止笛卡尔集爆炸问题关联查询会返回笛卡尔集会包含左表重复数据SELECT p.Id, p.PassportNo, b.Id, b.Amount, b.PassportId FROM Passports p LEFT JOIN Bills b ON p.Id b.PassportId WHERE p.Id 1;拆分查询生成的 SQL2 条独立语句第一条查 “护照”sql SELECT p.Id, p.PassportNo FROM Passports p WHERE p.Id 1;第二条查 “这个护照对应的所有账单”sql SELECT b.Id, b.Amount, b.PassportId FROM Bills b WHERE b.PassportId 1;结果集大小第一条返回 1 条记录仅护照信息第二条返回 1000 条记录仅账单信息总记录数还是 1001但没有重复的护照信息结果集体积比之前小很多节省了重复的 1000KB。全局配置.AsSplitQuery()AsQueryable()和AsEnumerable()区别AsEnumerable是查询所有字段在内存中过滤条件AsQueryable是按需索取sql注入var user johndoe; var blogs await context.Blogs .FromSqlInterpolated($SELECT * FROM Blogs WHERE Author {user}) .ToListAsync(); 或者 var user new SqlParameter(user, johndoe); var blogs await context.Blogs .FromSqlRaw(SELECT * FROM Blogs WHERE Author user, user) .ToListAsync();Addusing (var context new AppDbContext()) { var newUser new User { Id 0, Name John }; context.Users.Add(newUser); context.SaveChanges(); Console.WriteLine(newUser.Id); // 此时会输出数据库自动生成的自增 Id }// 附加实体默认状态为 Unchanged context.Users.Attach(user); // 标记需要修改的属性只更新该字段 context.Entry(user).Property(u u.Name).IsModified true; // 无需查询完整实体只需构建包含主键的实体 var userToDelete new User { Id userId }; // 附加实体并标记为删除状态 context.Users.Attach(userToDelete); context.Entry(userToDelete).State EntityState.Deleted; var newUser new User { Name userName, Age age }; // 附加实体并标记为新增状态 context.Users.Attach(newUser); context.Entry(newUser).State EntityState.Added; context.SaveChanges();delete// 直接构建嵌套子查询EF Core 会转换为 NOT IN 语法 var deleteCount context.Customers .Where(c !context.Orders .Select(o o.CustomerId) .Distinct() .Contains(c.CustomerId)) .ExecuteDelete(); sql DELETE FROM [Customers] WHERE [CustomerId] NOT IN ( SELECT DISTINCT [o].[CustomerId] FROM [Orders] AS [o] ) ----------------------------------updateint updatedCount context.Students // 筛选条件Tid 等于 李四老师的 Tid子查询 .Where(s s.Tid context.Teachers .Where(t t.Tname 李四) .Select(t t.Tid) .FirstOrDefault() // 对应 SQL 中的 子查询确保子查询返回单行 ) // efcore3.0的批量更新设置 Ssex 为 女生 .ExecuteUpdate(setters setters .SetProperty(s s.Ssex, 女生) ); sql UPDATE Students SET Ssex女生 WHERE Tid ( SELECT Tid From Teachers WHERE Tname 李四 );// 方法1使用 Entity Framework Plus 扩展库 // https://entityframework-plus.net/ dbContext.SetTableA() .Where(a dbContext.SetTableB().Any(b b.Id a.Id a.Category 某个条件)) .Update(a new TableA { Column1 dbContext.SetTableB() .Where(b b.Id a.Id) .Select(b b.Column2) .FirstOrDefault() }); UPDATE [TableA] SET [Column1] ( -- 子查询从TableB匹配Id取值无匹配则返回NULL SELECT TOP 1 [b].[Column2] FROM [TableB] AS [b] WHERE [b].[Id] [TableA].[Id] ) -- 外层筛选条件关联TableB且TableA.Category满足条件 WHERE EXISTS ( SELECT 1 FROM [TableB] AS [b] WHERE [b].[Id] [TableA].[Id] AND [TableA].[Category] N某个条件 ); ----------------------------// EF Core 7.0 及以上版本支持 await dbContext.SetTableA() .Where(a a.Category 某个条件) .ExecuteUpdateAsync(setters setters .SetProperty(a a.Column1, a dbContext.SetTableB() .Where(b b.Id a.Id) .Select(b b.Column2) .FirstOrDefault())); UPDATE [TableA] SET [Column1] ( -- 子查询匹配TableB的Column2FirstOrDefault()对应TOP 1 无匹配返回NULL SELECT TOP(1) [b].[Column2] FROM [TableB] AS [b] WHERE [b].[Id] [TableA].[Id] ) -- 外层筛选条件仅更新Category匹配的记录 WHERE [TableA].[Category] N某个条件; --------------------------------------------------- 不推荐 // 配置日志输出到控制台 dbContext.Database.Log sql Console.WriteLine(执行的SQL sql); // 执行你的代码 var query from a in dbContext.SetTableA() join b in dbContext.SetTableB() on a.Id equals b.Id where a.Category 某个条件 select new { A a, BColumn2 b.Column2 }; foreach (var item in await query.ToListAsync()) { item.A.Column1 item.BColumn2; } await dbContext.SaveChangesAsync();事务当调用 SaveChanges() 时EF Core 会自动创建一个事务并将所有待执行的操作包含在该事务中。如果所有操作成功则提交事务如果有任何错误则自动回滚。using Microsoft.EntityFrameworkCore; using System; using System.Threading.Tasks; public class AppDbContext : DbContext { public DbSetUser Users { get; set; } public DbSetOrder Orders { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(Your_Connection_String); // 对于SQLiteoptionsBuilder.UseSqlite(Data Sourceapp.db); } } public class User { public int Id { get; set; } public string Name { get; set; } } public class Order { public int Id { get; set; } public string Product { get; set; } public int UserId { get; set; } } public class TransactionService { // 同步事务示例 public void ProcessWithTransaction() { using (var context new AppDbContext()) { // 开始事务 using (var transaction context.Database.BeginTransaction()) { try { // 操作1 context.Users.Add(new User { Name Bob }); context.SaveChanges(); // 操作2假设UserId使用刚添加的用户ID var newUserId context.Users.First(u u.Name Bob).Id; context.Orders.Add(new Order { Product Laptop, UserId newUserId }); context.SaveChanges(); // 提交事务 transaction.Commit(); Console.WriteLine(所有操作已提交); } catch (Exception ex) { // 回滚事务 transaction.Rollback(); Console.WriteLine($事务已回滚: {ex.Message}); } } } } // 异步事务示例 public async Task ProcessWithTransactionAsync() { using (var context new AppDbContext()) { // 异步开始事务 using (var transaction await context.Database.BeginTransactionAsync()) { try { context.Users.Add(new User { Name Charlie }); await context.SaveChangesAsync(); var newUserId context.Users.First(u u.Name Charlie).Id; context.Orders.Add(new Order { Product Phone, UserId newUserId }); await context.SaveChangesAsync(); // 异步提交 await transaction.CommitAsync(); Console.WriteLine(所有操作已异步提交); } catch (Exception ex) { // 异步回滚 await transaction.RollbackAsync(); Console.WriteLine($事务已异步回滚: {ex.Message}); } } } } }// 注意需要数据库支持分布式事务如SQL Server using (var context1 new AppDbContext1()) using (var context2 new AppDbContext2()) { // 开启分布式事务 using (var transaction await context1.Database.BeginTransactionAsync()) { try { // 操作第一个数据库 context1.Table1.Add(new Table1Entity()); await context1.SaveChangesAsync(); // 将第二个上下文关联到同一事务 await context2.Database.UseTransactionAsync(transaction.GetDbTransaction()); // 操作第二个数据库 context2.Table2.Add(new Table2Entity()); await context2.SaveChangesAsync(); // 提交分布式事务 await transaction.CommitAsync(); } catch { await transaction.RollbackAsync(); throw; } } }建议不使用导航属性join查询https://blog.csdn.net/cxnwb/article/details/120257199?fromshareblogdetailsharetypeblogdetailsharerId120257199sharereferPCsharesourcem0_68206177sharefromfrom_link// 联表查询 inner join查询员工及其所属部门信息 var query from emp in db.Employees join dept in db.Departments on emp.DepartmentId equals dept.Id // 手动指定关联条件 select new { 员工姓名 emp.Name, 部门名称 dept.DepartmentName, 薪资 emp.Salary, 部门位置 dept.Location }; // 执行查询延迟加载ToList() 触发数据库访问 var result query.ToList(); SELECT [e].[Name] AS [员工姓名], [d].[DepartmentName] AS [部门名称], [e].[Salary] AS [薪资], [d].[Location] AS [部门位置] FROM [Employees] AS [e] INNER JOIN [Departments] AS [d] ON [e].[DepartmentId] [d].[Id] 2 var query db.Employees // 1. 外部表左表要查询的主表是员工表 .Join( inner: db.Departments, // 2. 内部表右表要关联的表是部门表 // 3. 外部表的关联键员工表中用来匹配部门的字段逻辑外键 outerKeySelector: emp emp.DepartmentId, // 4. 内部表的关联键部门表中用来匹配员工的字段主键 innerKeySelector: dept dept.Id, // 5. 结果投影只取需要的字段组装成新的匿名对象避免返回全表字段 resultSelector: (emp, dept) new { 员工姓名 emp.Name, // 从员工表取姓名 部门名称 dept.DepartmentName, // 从部门表取名称 薪资 emp.Salary, // 从员工表取薪资 部门位置 dept.Location // 从部门表取位置 } ); var result query.ToList(); // 执行查询ToList() 触发数据库访问返回结果列表 ------------------------------------------------------------------------------------- // LEFT JOIN查询所有员工无部门则部门信息为 null var leftJoinQuery from emp in db.Employees join dept in db.Departments on emp.DepartmentId equals dept.Id into tempDept from dept in tempDept.DefaultIfEmpty() select new { emp.Name, DepartmentName dept?.DepartmentName ?? 无部门 }; 2. using (var db new AppDbContext()) { // Lambda 语法实现 LEFT JOIN查询所有员工 对应部门无部门也保留员工 var query db.Employees // 1. 第一步GroupJoin分组连接—— 对应查询表达式的 join ... into .GroupJoin( inner: db.Departments, // 关联的右表部门表 outerKeySelector: emp emp.DepartmentId, // 左表员工的关联键 innerKeySelector: dept dept.Id, // 右表部门的关联键 resultSelector: (emp, deptGroup) new // 临时结果员工 匹配的部门集合 { Employee emp, DepartmentGroup deptGroup // 存储当前员工匹配的所有部门一对一则要么1条要么空 } ) // 2. 第二步SelectMany DefaultIfEmpty()—— 扁平化集合处理空值左连接核心 .SelectMany( // 把部门集合“拆解开”一对一场景下拆后要么1个部门要么空 collectionSelector: temp temp.DepartmentGroup.DefaultIfEmpty(), // 最终结果投影组装员工和部门的字段 resultSelector: (temp, dept) new { 员工姓名 temp.Employee.Name, 员工薪资 temp.Employee.Salary, // 空值处理无部门则显示“无部门” 部门名称 dept?.DepartmentName ?? 无部门, 部门位置 dept?.Location ?? 无 } ); // 执行查询ToList() 触发数据库访问 var result query.ToList(); }public ListMenuModel GetUserMenus(int userId) { using (var dbContext new YourDbContext()) { // 方式1多层Join关联等价于SQL的多表JOIN var menus (from menu in dbContext.MenuModel join roleMenu in dbContext.RoleMenu on menu.MenuId equals roleMenu.MenuId join userRole in dbContext.UserRole on roleMenu.RoleId equals userRole.RoleId join role in dbContext.RoleInfo on userRole.RoleId equals role.RoleId where menu.State 1 role.State 1 userRole.UserId userId select menu) .Distinct() // 去重避免同一菜单被多个角色关联 .ToList(); // 方式2子查询更简洁适合关联层级多的场景 var menus2 (from menu in dbContext.MenuModel where menu.State 1 dbContext.RoleMenu.Any(rm rm.MenuId menu.MenuId dbContext.UserRole.Any(ur ur.RoleId rm.RoleId ur.UserId userId dbContext.RoleInfo.Any(r r.RoleId ur.RoleId r.State 1) ) ) select menu) .ToList(); //方式3 子查询 var query from menu in dbContext.SetMenuModel() join roleMenu in dbContext.SetRoleMenu() on menu.MenuId equals roleMenu.MenuId where menu.State 1 // 直接在查询中嵌套角色筛选条件 (from ur in dbContext.SetUserRole() join role in dbContext.SetRoleInfo() on ur.RoleId equals role.RoleId where ur.UserId userid role.state 1 select ur.RoleId ).Contains(roleMenu.RoleId) select menu; return menus; } } //1 SELECT DISTINCT [m].[MenuId], [m].[MenuName], [m].[State] FROM [MenuModel] AS [m] INNER JOIN [RoleMenu] AS [rm] ON [m].[MenuId] [rm].[MenuId] INNER JOIN [UserRole] AS [ur] ON [rm].[RoleId] [ur].[RoleId] INNER JOIN [RoleInfo] AS [r] ON [ur].[RoleId] [r].[RoleId] WHERE [m].[State] 1 AND [r].[State] 1 AND [ur].[UserId] userId //2 SELECT [m].[MenuId], [m].[MenuName], [m].[State] FROM [MenuModel] AS [m] WHERE [m].[State] 1 AND EXISTS ( SELECT 1 FROM [RoleMenu] AS [rm] WHERE [rm].[MenuId] [m].[MenuId] AND EXISTS ( SELECT 1 FROM [UserRole] AS [ur] INNER JOIN [RoleInfo] AS [r] ON [ur].[RoleId] [r].[RoleId] WHERE [ur].[RoleId] [rm].[RoleId] AND [ur].[UserId] userId AND [r].[State] 1 ) ) //3 SELECT [m].[MenuId], [m].[MenuName], [m].[State] FROM [MenuModel] AS [m] INNER JOIN [RoleMenu] AS [rm] ON [m].[MenuId] [rm].[MenuId] WHERE [m].[State] 1 AND [rm].[RoleId] IN ( SELECT [ur].[RoleId] FROM [UserRole] AS [ur] INNER JOIN [RoleInfo] AS [r] ON [ur].[RoleId] [r].[RoleId] WHERE [ur].[UserId] userId AND [r].[State] 1 )left join例子public async TaskListUserModel GetUsersWithRoleAsync(string? keyword) { var userRoleData from user in dbContext.SetUserModel() where string.IsNullOrEmpty(keyword) || user.RealName.Contains(keyword) || user.UserName.Contains(keyword) join userRole in dbContext.SetUserRole() on user.UserId equals userRole.UserId into tempUserRole from tur in tempUserRole.DefaultIfEmpty() join role in dbContext.SetRoleInfo() on tur.RoleId equals role.RoleId into tempRole from tr in tempRole.DefaultIfEmpty() where tr null || tr.state 1 select new { User user, Role tr }; var userRoleGroups await userRoleData .GroupBy(item item.User.UserId) .ToListAsync(); var resultUsers new ListUserModel(); foreach (var g in userRoleGroups) { var user g.First().User; // 提取该用户的所有角色过滤null user.Roles g.Select(item item.Role) .Where(r r ! null) .Distinct() // 去重避免重复角色 .ToList(); resultUsers.Add(user); } return resultUsers; } //----------------------------------------生成sql SELECT [s].[user_id], [s].[age], [s].[password], [s].[real_name], [s].[state], [s].[user_icon], [s].[user_name], [r].[role_id], [r].[role_name], [r].[state] FROM [system_users] AS [s] LEFT JOIN [user_role] AS [u] ON [s].[user_id] [u].[user_id] LEFT JOIN [roles] AS [r] ON [u].[role_id] [r].[role_id] WHERE [s].[state] 1 AND (([r].[role_id] IS NULL) OR [r].[state] 1) ORDER BY [s].[user_id]复杂查询using (var db new AppDbContext()) { // 1. 定义查询参数查询“北京”位置的部门下的员工 var targetLocation Beijing; // 2. 执行参数化原生SQL用dynamic接收结果 var query db.Database .FromSqlInterpolated($ SELECT e.Name AS EmployeeName, -- 员工姓名别名EmployeeName d.DepartmentName AS DeptName, -- 部门名称别名DeptName e.Salary AS Salary, -- 薪资别名Salary d.Location AS DeptLocation -- 部门位置别名DeptLocation FROM Employees e -- 员工表别名e INNER JOIN Departments d -- 内连接部门表别名d ON e.DepartmentId d.Id -- 关联条件员工.DepartmentId 部门.Id WHERE d.Location {targetLocation} -- 参数化传入自动防SQL注入 ) .ToListdynamic(); // 动态类型接收结果不推荐运行时才异常 // 3. 使用结果运行时通过别名访问字段 foreach (var item in query) { Console.WriteLine($Employee: {item.EmployeeName}, Dept: {item.DeptName}, Salary: {item.Salary:C}); } } 推荐 using (var db new AppDbContext()) { // 1. 定义强类型DTO字段名、类型必须与SQL结果的别名、类型完全匹配 public class EmpDeptDto { public string EmployeeName { get; set; } // 对应SQL别名EmployeeName字符串 public string DeptName { get; set; } // 对应SQL别名DeptName字符串 public decimal Salary { get; set; } // 对应SQL别名Salarydecimal与数据库字段类型一致 public string DeptLocation { get; set; } // 对应SQL别名DeptLocation字符串 } // 2. 执行参数化SQL映射到强类型DTO var query2 db.SetEmpDeptDto() .FromSqlInterpolated($ SELECT e.Name AS EmployeeName, d.DepartmentName AS DeptName, e.Salary AS Salary, d.Location AS DeptLocation FROM Employees e INNER JOIN Departments d ON e.DepartmentId d.Id -- 可按需添加参数条件示例WHERE d.Location {targetLocation} ) .ToList(); // 直接返回ListEmpDeptDto强类型集合 // 3. 使用结果编译时有字段提示无拼写错误风险 foreach (var dto in query2) { Console.WriteLine($Employee: {dto.EmployeeName}, Dept: {dto.DeptName}, Location: {dto.DeptLocation}); } }1对多一个部门对应多个员工你按照我这一篇文章的做法虽然去掉外键可以正常curd但是当你下次迁移更新数据库会报错找不到你外键的错误需要手动删除你迁移类中dropForiegnKey函数和adddoriegnkey函数但是每次迁移手动删除太麻烦了你可以参考*我的另一篇*重写一下方法就行了我的另一篇public class Department { public long Id { get; set; } public string Name { get; set; } public ListEmployee? Employees { get; set; } new ListEmployee();//--------$ } public class Employee { public long Id { get; set; } public string Name { get; set; } public Department? Dept { get; set; }//---------------$我没使用外键所以可能为空加上“” //意思就是当我级联include查询员工表员工表存在 //但他没有部门id为空也会返回为空 public long? DepartmentId { get; set; } }实体类配置//部门 public void Configure(EntityTypeBuilderDepartment builder) { builder.Property(e e.Name).IsRequired().HasMaxLength(20); builder.HasKey(e e.Id); builder.HasManyEmployee(e e.Employees) .WithOne(e e.Dept);//一个部门有多个员工(对应Employee类)一个员工对应一个部门 } //员工 public void Configure(EntityTypeBuilderEmployee builder) { builder.Property(e e.Name).HasMaxLength(20).IsRequired(); builder.Property(e e.DepartmentId); builder.HasKey(e e.Id); builder.HasOneDepartment(e e.Dept) .WithMany(e e.Employees); }因为EFCore为保证数据一致性如果你使用外键当你删除部门时会带着该部门员工一起删除现在企业中以阿里的开发标准不需要有外键使用Efcor codefirst后数据库自动有外键配置需要手动删除把外键id也重新设置可为空实验一下以id删除部门public Result DelDept(int id) { var dept _context.DepartmentTable.Include(e e.Employees) .Single(e e.Id id); if (dept ! null) { _context.DepartmentTable.Remove(dept); _context.SaveChanges(); return Result.Success(ok); } else { return Result.Error(删除失败); } }下面程序返回了两端sql意思是啥呢意思是 部门表 left join员工表 找到部门id为符合条件后删除部门然后置对应员工的DepartmentId为nullSELECT t.Id, t.Name, e.Id, e.DepartmentId, e.Name FROM ( SELECT d.Id, d.Name FROM DepartmentTable AS d WHERE d.Id __p_0 LIMIT 2 ) AS t LEFT JOIN EmployeeTable AS e ON t.Id e.DepartmentId ORDER BY t.Id UPDATE EmployeeTable SET DepartmentId p0 WHERE Id p1; SELECT ROW_COUNT(); DELETE FROM DepartmentTable WHERE Id p2; SELECT ROW_COUNT();把代码改成当删除部门id为1的科研部同时删除该部门下的员工看看是否成功public Result DelDept(int id) { var dept _context.DepartmentTable.Include(e e.Employees).Single(e e.Id id); if (dept ! null) { var emplist dept.Employees; _context.EmployeeTable.RemoveRange(emplist);//删除该部门所有员工 _context.DepartmentTable.Remove(dept); _context.SaveChanges(); return Result.Success(ok); } else { return Result.Error(删除失败); } }执行上面代码后数据库的变化