//针对情景二的异常的处理方案封装
public void Method5()
{
const string userName = "******";
SysUser user;
using (var db = new DefaultDbContext())
{
user = db.Set <SysUser>().Single(m => m.UserName == userName);
}
user.AddDate = DateTime.Now;
using (var db = new DefaultDbContext())
{
//通过查询，让上下文中存在相同主键的对象
SysUser oldUser = db.Set <SysUser>().Find(user.Id);
System.Console.WriteLine("更新前：{0}。", oldUser.AddDate);
db.Update <SysUser>(user);
int count = db.SaveChanges();
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新后：{0}。", newUser.AddDate);
}
}
//情景二的异常情况：上下文2中已存在与外来实体主键相同的数据了，这将引发InvalidOperationException异常
public void Method3()
{
const string userName = "******";
SysUser user;
using (var db = new DefaultDbContext())
{
user = db.Set <SysUser>().Single(m => m.UserName == userName);
}
user.AddDate = DateTime.Now;
using (var db = new DefaultDbContext())
{
//通过查询，让上下文中存在相同主键的对象
SysUser oldUser = db.Set <SysUser>().Find(user.Id);
System.Console.WriteLine("更新前：{0}。", oldUser.AddDate);
DbEntityEntry <SysUser> entry = db.Entry(user);
System.Console.WriteLine("Attach前的状态：{0}", entry.State);
db.Set <SysUser>().Attach(user); //报错！！
System.Console.WriteLine("Attach后的状态：{0}", entry.State);
entry.State = EntityState.Modified;
int count = db.SaveChanges();
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新后：{0}。", newUser.AddDate);
}
}
//针对情景二的异常的处理方案
public void Method4()
{
const string userName = "******";
SysUser user;
using (var db = new DefaultDbContext())
{
user = db.Set <SysUser>().Single(m => m.UserName == userName);
}
user.AddDate = DateTime.Now;
using (var db = new DefaultDbContext())
{
//通过查询，让上下文中存在相同主键的对象
SysUser oldUser = db.Set <SysUser>().Find(user.Id);
System.Console.WriteLine("更新前：{0}。", oldUser.AddDate);
DbEntityEntry <SysUser> entryUser = db.Entry(user);
System.Console.WriteLine("上下文二中user对象的状态：{0}", entryUser.State);//EntityState.Detached
DbEntityEntry <SysUser> entry = db.Entry(oldUser);
System.Console.WriteLine("上下文二中oldUser对象的状态1：{0}", entry.State);
entry.CurrentValues.SetValues(user);
System.Console.WriteLine("上下文二中oldUser对象的状态2：{0}", entry.State);
int count = db.SaveChanges();
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新后：{0}。", newUser.AddDate);
}
}
//情景二：在一个上下文中查询，在另一个上下文中修改
public void Method2()
{
const string userName = "******";
SysUser user;
using (var db = new DefaultDbContext())
{
user = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新前：{0}。", user.AddDate);
}
user.AddDate = DateTime.Now;
using (var db = new DefaultDbContext())
{
DbEntityEntry <SysUser> entry = db.Entry(user);
System.Console.WriteLine("Attach前的状态：{0}", entry.State); //EntityState.Detached
db.Set <SysUser>().Attach(user);
System.Console.WriteLine("Attach后的状态：{0}", entry.State); //EntityState.Unchanged
entry.State = EntityState.Modified;
int count = db.SaveChanges();
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新后：{0}。", newUser.AddDate);
}
}
//架构中的查询Entity是不进行变更追踪的，注意区别
public void Method7()
{
using (var db = new DefaultDbContext())
{
var user = db.Set <SysUser>().First();
System.Console.WriteLine("查询时保留追踪：{0}", user.AddDate);
user.AddDate = DateTime.Now.AddHours(10);
db.SaveChanges();
var newUser = db.Set <SysUser>().First();
System.Console.WriteLine("查询时保留追踪：{0}", newUser.AddDate);
}
using (var db = new DefaultDbContext())
{
var user = db.Set <SysUser>().AsNoTracking().First();
System.Console.WriteLine("查询时不保留追踪：{0}", user.AddDate);
user.AddDate = DateTime.Now.AddHours(10);
db.SaveChanges();
var newUser = db.Set <SysUser>().First();
System.Console.WriteLine("查询时不保留追踪：{0}", newUser.AddDate);
}
}
public async Task <List <RouteOutputDto> > GetMenusForRoute()
{
List <RouteOutputDto> routeOutputDtos = new List <RouteOutputDto>();
List <MbpMenu> menus;
// 过滤超管,如果是超管admin账号则忽略,为了防止菜单被其他管理员误操作导致不好复原
if (_currentUser.LoginName == "admin")
{
menus = _defaultDbContext.MbpMenus.Where(m => m.MenuType == EnumMenuType.Page).ToList();
}
else
{
// 其他用户 根据用户角色权限的配置获取菜单
menus = (from tuser in _defaultDbContext.Set <MbpUser>()
join tuserrole in _defaultDbContext.Set <MbpUserRole>()
on tuser.Id equals tuserrole.UserId
join trole in _defaultDbContext.Set <MbpRole>()
on tuserrole.RoleId equals trole.Id
join trolemenu in _defaultDbContext.Set <MbpRoleMenu>()
on trole.Id equals trolemenu.RoleId
join tmenu in _defaultDbContext.Set <MbpMenu>()
on trolemenu.MenuId equals tmenu.Id
where tmenu.MenuType == EnumMenuType.Page && tuser.Id == _currentUser.Id
select tmenu).ToList();
}
List <RouteOutputDto> tempRoute = new List <RouteOutputDto>();
// 将菜单调整为路由表数据结构形式
foreach (var item in menus)
{
RouteOutputDto routeOutputDto = _mapper.Map <RouteOutputDto>(item);
routeOutputDto.Name = routeOutputDto.Code;
routeOutputDto.Component = item.MenuCompent;
routeOutputDto.Meta.Title = item.Name;
routeOutputDto.Meta.Icon = item.MenuIcon;
tempRoute.Add(routeOutputDto);
}
var dictNodes = tempRoute.ToDictionary(x => x.Id);
// 重组成vue-element-admin 路由表json
foreach (var item in dictNodes.Values)
{
if (item.ParentId == 1)
{
item.Path = "/" + item.Path;
routeOutputDtos.Add(item);
}
else
{
if (dictNodes.ContainsKey(item.ParentId))
{
dictNodes[item.ParentId].Children.Add(item);
}
}
}
return(routeOutputDtos);
}
/// <summary>
/// 分析统计并标记离线，因为离线后不会有数据必须用后台计算得出
/// 在线的标记在方法Calculate（计算）里会标记
/// </summary>
public static void AnalyzeOffLine(object state)
{
using (var context = new DefaultDbContext())
{
long nowticks = ZeYu.Intelligence.Monitor.Common.TimeStamp.GetTimestamp(DateTime.UtcNow) / 1000;
SqlParameter parm = new SqlParameter("NowTicks", nowticks);
var list1 = context.Set <ZeYu.Intelligence.Monitor.Model.ActiveInfo>().FromSql($"select * from ActiveInfo where ABS(@NowTicks-LatestSamplingTime)>300 ", parm).ToList();
var list2 = context.Set <ZeYu.Intelligence.Monitor.Model.ActiveInfo>().FromSql($"select * from ActiveInfo where ABS(@NowTicks-LatestSamplingTime)<301 ", parm).ToList();
UpdateTerminalOnlineSummary(list1, list2);
//Console.WriteLine(list1.Count);
}
}
//场景三：当然也会出现类似场景二的InvalidOperationException异常
//参照场景二进行try catch处理，无效，因为State在被设置 EntityState.Unchanged时，会自动将原值赋值过来
public void Method7()
{
const string userName = "******";
long id;
using (var db = new DefaultDbContext())
{
id = db.Set <SysUser>().Single(m => m.UserName == userName).Id;
}
SysUser user = new SysUser {
Id = id, Password = "******" + DateTime.Now.Second
};
using (var db = new DefaultDbContext())
{
//关闭自动跟踪，方便调试查看
db.Configuration.AutoDetectChangesEnabled = false;
db.Database.Log += (log) => { System.Console.WriteLine(log); };
//通过查询，让上下文中存在相同主键的对象
SysUser oldUser = db.Set <SysUser>().Find(user.Id);
System.Console.WriteLine("更新前：{0}。", oldUser.AddDate);
try
{
DbEntityEntry <SysUser> entry = db.Entry(user);
entry.State = EntityState.Unchanged;
entry.Property("Password").IsModified = true;
}
catch (InvalidOperationException)
{
DbEntityEntry <SysUser> entry = db.Entry(oldUser);
entry.CurrentValues.SetValues(user);
entry.State = EntityState.Unchanged;
entry.Property("Password").IsModified = true;
}
db.Configuration.ValidateOnSaveEnabled = false;
int count = db.SaveChanges();
db.Configuration.ValidateOnSaveEnabled = true;
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.Id == 1);
System.Console.WriteLine("更新后：{0}。", newUser.Password);
}
}
//想想18，也按这种思路改一改先标注newUserExpand.User删除再标注newUserExpand删除
//也可以达到预期
public void Method20()
{
//准备一条数据
using (var context = new DefaultDbContext())
{
var user = new SysUser()
{
UserName = "******",
Password = "******",
UserExpand = new SysUserExpand
{
ExpandValue1 = "user20的补充"
}
};
context.Entry(user).State = System.Data.Entity.EntityState.Added;
context.SaveChanges();
}
SysUserExpand newUserExpand = null;
using (var context = new DefaultDbContext())
{
newUserExpand = context.Set <SysUserExpand>().Include(p => p.User)
.SingleOrDefault(o => o.ExpandValue1 == "user20的补充");
}
using (var context = new DefaultDbContext())
{
context.Entry(newUserExpand.User).State = System.Data.Entity.EntityState.Deleted;
context.Entry(newUserExpand).State = System.Data.Entity.EntityState.Deleted;
context.SaveChanges();
}
}
//假设在同一个上下文中，已有UserExpand，对User和UserExpand进行修改，但只标注userExpand修改如何
//达到目标
public void Method12()
{
//准备一条数据
using (var context = new DefaultDbContext())
{
var user = new SysUser()
{
UserName = "******",
Password = "******",
UserExpand = new SysUserExpand
{
ExpandValue1 = "user12的补充"
}
};
context.Entry(user).State = System.Data.Entity.EntityState.Added;
context.SaveChanges();
}
using (var context = new DefaultDbContext())
{
var userExpand = context.Set <SysUserExpand>().SingleOrDefault(o => o.ExpandValue1 == "user12的补充");
userExpand.User.UserName = "******";
userExpand.ExpandValue1 = "修改的user12的补充";
context.Entry(userExpand).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public BaseRepository(DefaultDbContext dbContext)
{
_dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
//确保数据库已经被创建
_dbContext.Database.EnsureCreated();
_dbset = dbContext.Set <T>();
}
public Repository()
{
//TODO:这里可以移到Global
db = DefaultDbContext.Current;
Table = db.Set <TEntity>();
}
//情景一：在同一个上下文中查询并修改
public void Method1()
{
const string userName = "******";
using (var db = new DefaultDbContext())
{
SysUser oldUser = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新前：{0}。", oldUser.AddDate);
oldUser.AddDate = oldUser.AddDate.AddMinutes(10);
int count = db.SaveChanges();
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.UserName == userName);
System.Console.WriteLine("更新后：{0}。", newUser.AddDate);
}
}
public BaseRepository(DefaultDbContext dbContext)
{
if (dbContext == null)
{
throw new ArgumentNullException(nameof(dbContext));
}
_dbContext = dbContext;
_set = dbContext.Set <T>();
}
public async Task <LoginOutputDto> Login(LoginInputDto loginInputDto)
{
var user = _defaultDbContext.MbpUsers.Where(u => u.LoginName == loginInputDto.LoginName).FirstOrDefault();
if (user != null)
{
if (user.Password != ApplicationHelper.EncryptPwdMd5(loginInputDto.Password))
{
return(new LoginOutputDto()
{
AccessToken = new Jwt(), IsPassPwdCheck = false
});
}
// 如果是管理员权限就给管理员属性,如果是用户就给用户属性,这里只定义两种角色,一种是超管,一种是普通用户,这里的角色只做身份鉴定,不做鉴权用
var token = await _jwtBearerService.CreateJwt(loginInputDto.LoginName, loginInputDto.ClientID, new List <Claim>()
{
new Claim(ClaimTypes.Role, user.IsAdmin?"admin":"user")
});
// 取出用户的菜单权限
var menus = (from tuser in _defaultDbContext.Set <MbpUser>()
join tuserrole in _defaultDbContext.Set <MbpUserRole>()
on tuser.Id equals tuserrole.UserId
join trole in _defaultDbContext.Set <MbpRole>()
on tuserrole.RoleId equals trole.Id
join trolemenu in _defaultDbContext.Set <MbpRoleMenu>()
on trole.Id equals trolemenu.RoleId
join tmenu in _defaultDbContext.Set <MbpMenu>()
on trolemenu.RoleId equals tmenu.Id
select tmenu.Path).ToList();
return(new LoginOutputDto()
{
AccessToken = token, Menus = menus, UserName = user.UserName, Role = user.IsAdmin ? "admin" : "user", IsPassPwdCheck = true
});
}
return(new LoginOutputDto()
{
AccessToken = new Jwt(), IsPassPwdCheck = false
});
}
//针对情景三的异常的处理方案封装
public void Method9()
{
SysUser user = new SysUser {
Id = 1, Password = "******" + DateTime.Now.Second
};
using (var db = new DefaultDbContext())
{
//先查询一次，让上下文中存在相同主键的对象
SysUser oldUser = db.Set <SysUser>().Single(m => m.Id == 1);
System.Console.WriteLine("更新前：{0}。", oldUser.Password);
db.Update <SysUser>(m => new { m.Password }, user);
int count = db.SaveChanges(false);
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.Id == 1);
System.Console.WriteLine("更新后：{0}。", newUser.Password);
}
}
//那么提前加载好导航属性是一个不错的方法
public void Method3()
{
using (var db = new DefaultDbContext())
{
var users = db.Set <SysUser>().Include(p => p.Roles);
foreach (var user in users)
{
System.Console.WriteLine(user.Roles.Count());
}
}
}
//准备一些查询数据
static EfUpdateTest()
{
using (var context = new DefaultDbContext())
{
var user = context.Set <SysUser>().SingleOrDefault(o => o.UserName == "张三");
if (user == null)
{
context.Set <SysUser>().Add(new SysUser()
{
UserName = "******", Password = "******"
});
context.Set <SysUser>().Add(new SysUser()
{
UserName = "******", Password = "******"
});
context.Set <SysUser>().Add(new SysUser()
{
UserName = "******", Password = "******"
});
context.SaveChanges();
}
}
}
//但它仍旧不如DTO方式来的直接明了
public void Method5()
{
using (var db = new DefaultDbContext())
{
var users = db.Set <SysUser>().Select(m => new
{
roleCount = new { nCount = m.Roles.Count }
});
foreach (var user in users)
{
System.Console.WriteLine(user.roleCount.nCount);
}
}
}
//可以通过Set<T>.Add实现新增
public void Method1()
{
using (var context = new DefaultDbContext())
{
var user = new SysUser()
{
UserName = "******",
Password = "******",
UserExpand = new SysUserExpand()
{
ExpandValue1 = "user1的补充"
},
};
context.Set <SysUser>().Add(user);
context.SaveChanges();
}
}
//场景三：场景二下必须查询出原有对象，才能进行更新操作
//能不能不查询，就可以更新对象的属性
//比如修改密码功能，明确只会对密码进行修改
public void Method6()
{
const string userName = "******";
long id;
using (var db = new DefaultDbContext())
{
id = db.Set <SysUser>().Single(m => m.UserName == userName).Id;
}
SysUser user = new SysUser {
Id = id, Password = "******" + DateTime.Now.Second
};
using (var db = new DefaultDbContext())
{
//关闭自动跟踪，方便调试查看
db.Configuration.AutoDetectChangesEnabled = false;
db.Database.Log += (log) => { System.Console.WriteLine(log); };
DbEntityEntry <SysUser> entry = db.Entry(user);
System.Console.WriteLine("上下文操作前状态：{0}", entry.State);
//在操作前上下文必须包含对象
entry.State = EntityState.Unchanged; //或者db.Set<SysUser>().Attach(user);
entry.Property("Password").IsModified = true;
//注意这里的前提是不含版本标识，有版本标识的情况是不支持这种情况的
db.Configuration.ValidateOnSaveEnabled = false;
int count = db.SaveChanges();
db.Configuration.ValidateOnSaveEnabled = true;
System.Console.WriteLine("操作结果：{0}", count > 0 ? "更新成功。" : "未更新。");
SysUser newUser = db.Set <SysUser>().Single(m => m.Id == id);
System.Console.WriteLine("更新后：{0}。", newUser.Password);
}
}
//很明显了，对不在上下文的修改，必须标注user和userexpand都被修改
public void Method14()
{
//准备一条数据
using (var context = new DefaultDbContext())
{
var user = new SysUser()
{
UserName = "******",
Password = "******",
UserExpand = new SysUserExpand
{
ExpandValue1 = "user14的补充"
}
};
context.Entry(user).State = System.Data.Entity.EntityState.Added;
context.SaveChanges();
}
SysUser newUser = null;
using (var context = new DefaultDbContext())
{
newUser = context.Set <SysUser>().Include(p => p.UserExpand)
.SingleOrDefault(o => o.UserName == "user14");
}
newUser.UserName = "******";
newUser.UserExpand.ExpandValue1 = "修改的user14的补充";
using (var context = new DefaultDbContext())
{
context.Entry(newUser).State = System.Data.Entity.EntityState.Modified;
context.Entry(newUser.UserExpand).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
//通过新增UserExpand对象，然后设置UserExpand新增
//不报错，但是达不到预期效果，同时新增了新的User和新的UserExpand
//注意：这里新增UserExpand是包含id和导航User的
public void Method5()
{
//准备一条数据
using (var context = new DefaultDbContext())
{
var user = new SysUser()
{
UserName = "******",
Password = "******",
};
context.Entry(user).State = System.Data.Entity.EntityState.Added;
context.SaveChanges();
}
SysUser newUser = null;
using (var context = new DefaultDbContext())
{
newUser = context.Set <SysUser>().SingleOrDefault(o => o.UserName == "user5");
}
SysUserExpand newUserExpand = new SysUserExpand
{
Id = newUser.Id,
User = newUser,
ExpandValue1 = "user5的补充"
};
newUser.UserExpand = newUserExpand;
using (var context = new DefaultDbContext())
{
context.Entry(newUserExpand).State = System.Data.Entity.EntityState.Added;
context.SaveChanges();
}
}
//只标注User删除，按提示是可以，但因为这里的ID是int，删除的效果是从表主键为null，故无法做到才报错
public void Method15()
{
//准备一条数据
using (var context = new DefaultDbContext())
{
var user = new SysUser()
{
UserName = "******",
Password = "******",
UserExpand = new SysUserExpand
{
ExpandValue1 = "user15的补充"
}
};
context.Entry(user).State = System.Data.Entity.EntityState.Added;
context.SaveChanges();
}
SysUser newUser = null;
using (var context = new DefaultDbContext())
{
newUser = context.Set <SysUser>().Include(p => p.UserExpand)
.SingleOrDefault(o => o.UserName == "user15");
}
//{"The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted."}
//准备一条数据
using (var context = new DefaultDbContext())
{
context.Entry(newUser).State = System.Data.Entity.EntityState.Deleted;
context.SaveChanges();
}
}
protected EntityRepository(DefaultDbContext dbContext)
{
DbContext = dbContext;
EntitySet = DbContext.Set <T>();
}
public Repository(DefaultDbContext contextPassed)
{
context = contextPassed;
dbSet = context.Set <T>();
}
public IActionResult Index()
{
var data = _dbContextBase.Set <OrderItem>().ToList();
return(Ok(data));
}
/// <summary>
/// 删除单个实体
/// </summary>
/// <param name="entity">要删除的实体</param>
/// <returns>返回受影响行数</returns>
public virtual int Delete(T entity)
{
DbContext.Set <T>().Remove(entity);
return(DbContext.SaveChanges());
}
/// <summary>
/// 执行sql查询，返回集合
/// </summary>
/// <typeparam name="TView"></typeparam>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IList <TView> SqlQuery <TView>(string sql, params object[] parameters) where TView : class, new()
{
return(_dbContext.Set <T>().FromSql(sql, parameters).Cast <TView>().ToList());
}
public string Test02()
{
User user = _dbContext.Set <User>().Find(2);
return(user.PasswordHash);
}