报错:(外键FK_Base_RoleRight_Base_Role约束错误,要求在dbo.Base_Roles表中的列名"Roles_ID"的值存在)
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Base_RoleRight_Base_Roles”. The conflict occurred in database “SAM_DB”, table “dbo.Base_Roles”, column ‘Roles_ID’.
The statement has been terminated.
原来写法:
using (TransactionScope trans = new TransactionScope())
{
//增加角色信息
db.Add(info.MapTo<Base_Roles>());
//增加角色-权限 信息
db.Base_RoleRight.AddRange(info.Base_RoleRight.MapToList<Base_RoleRight>());
result = db.SaveChanges();
}
SQLServer Profiler监测:
正确写法:(增加db.SaveChanges()😉
using (TransactionScope trans = new TransactionScope())
{
db.Add(info.MapTo<Base_Roles>());
db.SaveChanges();
db.Base_RoleRight.AddRange(info.Base_RoleRight.MapToList<Base_RoleRight>());
result = db.SaveChanges();
trans.Complete();
return result;
}
SQLServer Profiler监测:
再来看一个用户添加的例子:
using (TransactionScope trans = new TransactionScope())
{
db.Base_UserInfo.Add(base_UserInfoDto.MapTo<Base_UserInfo>());
db.SaveChanges();
db.Base_UserRight.AddRange(base_UserInfoDto.Base_UserRightDtoList.MapToList<Base_UserRight>());
db.Base_StaffOrganize.AddRange(base_UserInfoDto.Base_StaffOrganizeDtoList.MapToList<Base_StaffOrganize>());
db.Base_UserRole.AddRange(base_UserInfoDto.Base_UserRoleDtoList.MapToList<Base_UserRole>());
db.Base_AppendPropertyInstance.AddRange(base_UserInfoDto.Base_AppendPropertyInstanceDtoList.MapToList<Base_AppendPropertyInstance>());
result = db.SaveChanges();
trans.Complete();
}
SQLServer Profiler监测:
结论:RPC:Completed 执行的时候SQL顺序与代码的SQL顺序不一致。通过在每行EF语句间添加db.SaveChanges()执行顺序才会一致。