EF CORE 增删改查+事务

微软官方文档 描述
新增数据

使用 DbSet.Add 方法添加实体类的新实例，就是往DbSet.Add(添加被new 的新实例，数据绑定在新实例里 )
如果是多条循环添加

for (int i = 0; i < addlist.Count; i++)
{
    //添加 Value
    SySGlobalMultipleLanguageValue entityGlobalMultipleLanguageValue = new SySGlobalMultipleLanguageValue();
    entityGlobalMultipleLanguageValue.LookupType = "SY_S_DEPARTMENT.DEPT_CODE";
    entityGlobalMultipleLanguageValue.LookupValue = addlist[i].GetType().GetProperty("Dept_Code").GetValue(addlist[i]).ToString();
    entityGlobalMultipleLanguageValue.Meaning = addlist[i].GetType().GetProperty("Name").GetValue(addlist[i]).ToString();
    entityGlobalMultipleLanguageValue.Language = "zh-cn";
    entityGlobalMultipleLanguageValue.Creator = userid;
    entityGlobalMultipleLanguageValue.CreateDate = DateTime.Now;
    Entry(SySGlobalMultipleLanguageValues).State = EntityState.Modified;
    expectCount++;
    SySGlobalMultipleLanguageValues.Add(entityGlobalMultipleLanguageValue);
}

删除

  int expectCount = 0;  //记录游标
  bool blResult = false;
  /*
  //单个 删除
  var blog = SySDepartments.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(Deltcode)).First();
  Remove(blog);
  */
  //批量删除  可以用where 扩大范围 ；ToArrayAsync方法选择
  var departments= Contexts.SySDepartments.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(Deltcode)).ToArrayAsync();
  SySDepartments.RemoveRange(departments.Result);
  expectCount += departments.Result.Length;  --Result.Length返回行数

更新修改

把查询的结果实例化 ，修改实例化的数据，再把实例化更新后的数据保存

 int expectCount = 0;   //记录游标
 bool blResult = false; //默认状态
 //查选数据 First() 存入实体类
 var departments = Contexts.SySDepartments.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(CurrentCode[0].ToString())).First();
 
//更改 刚查询的实体类
 departments.UpperDept = ChangetCode[0].ToString();               //修改值
 departments.LineId = Convert.ToInt32(ChangetCode[1].ToString()); //修改值
 departments.Modifier = userid;									  //修改值
 departments.ModifyDate = DateTime.Now;							  //修改值
 expectCount++;
 Entry(departments).State = EntityState.Modified;				  //实体状态 更新

//把更改后的实体类保存
 SaveChanges();

查询数据
微软官方文档 描述

//单个实体查询
var list =  Contexts.SySDepartments
			.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(CurrentCode[0].ToString()))
			.ToLiSt();
			
//多表连接查询	
var querylist = (from Department in Set<SySDepartment>().Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ"))  
            join Value in Contexts.Set<SySGlobalMultipleLanguageValue>().Where(i => i.LookupValue.Equals(Deltcode))
on Department.UpperDept equals Value.LookupValue
            select new { 
                Value.Meaning, 
                Department.UpperDept,
                Department.DeptCode
            }).ToList();
		

事务对执行 新增、删除、修改 的指令
好处： 比如当 新增、删除、修改 ，有5条数据 ，也就就是说有5个受影响的行，但是实际执行没有5个受影响的行，尽管没报错执行成功但是数据不准确，有缺少，就代表执行是有误的；事务可以Rollback 回滚执行前的数据，预处理机制，防止数据混乱；

新增、删除、修改 ，有5条数据 ，事务 SaveChanges() 保存 ，返回3条数据 影响数，
返回3条数据 影响数 不等于 游标记录数 那么就 Rollback()回滚 否则 Commit()提交

public bool UpdatenameRepository(string DeptCode,string txtName,string userid)
{
    int expectCount = 0;  //游标
    bool blResult = false;

    var globalmultiplelanguagevalues = Contexts.SySGlobalMultipleLanguageValues.Where(i => i.LookupValue.Equals(DeptCode)).First();

    globalmultiplelanguagevalues.Meaning = txtName;
    globalmultiplelanguagevalues.Modifier = userid;
    globalmultiplelanguagevalues.ModifyDate = DateTime.Now;
    globalmultiplelanguagevalues.Language = "zh-cn";
    expectCount++;
    Entry(globalmultiplelanguagevalues).State = EntityState.Modified;

    using (var transaction = Database.BeginTransaction())
    {
        try
        {
            int count = SaveChanges();
            blResult = count == expectCount;
            if (blResult)
                transaction.Commit();
            else
                transaction.Rollback();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            var ms = ex;
        }
    }
    return blResult;
}

demo

public bool DeltDepartmentRepository(string Deltcode)
{
    int expectCount = 0;  //记录游标
    bool blResult = false;
    /*
    //单个 删除
    var blog = SySDepartments.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(Deltcode)).First();
    Remove(blog);
    */
    //批量删除
    var departments= Contexts.SySDepartments.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(Deltcode)).ToArrayAsync();
    SySDepartments.RemoveRange(departments.Result);
    expectCount += departments.Result.Length;

    var globalmultiplelanguagevalues = Contexts.SySGlobalMultipleLanguageValues.Where(i => i.LookupValue.Equals(Deltcode)).ToArrayAsync();
    SySGlobalMultipleLanguageValues.RemoveRange(globalmultiplelanguagevalues.Result);
    expectCount += globalmultiplelanguagevalues.Result.Length;

    /*if (departmententity.Count >= 0 && globalentity.Count >= 0)
        iROCKExceptionManager.HandleException(iROCKExceptionPolicy.CreatingAndThrown, Error.OperationGroupNOCanNotNullOrEmpty);
    */
    //事务
    using (var transaction =  Database.BeginTransaction())
    {
        try
        {
            int count = SaveChanges();
            blResult = count == expectCount;
            if (blResult)
                transaction.Commit();
            else
                transaction.Rollback();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            var ms = ex;
        }
    }
    return blResult;
}

public bool AddDepartmentRepository(List<object> addlist, string userid)
{
    bool blResult = false;
    var expectCount = 0;
    for (int i = 0; i < addlist.Count; i++)
    {
        //添加 Department
        SySDepartment entityDepartment = new SySDepartment();
        entityDepartment.Facility = "APAT";
        entityDepartment.Site = "SZ";
        entityDepartment.DeptCode = addlist[i].GetType().GetProperty("Dept_Code").GetValue(addlist[i]).ToString();
        if (!addlist[i].GetType().GetProperty("Upper_Dept").GetValue(addlist[i]).ToString().Equals(addlist[i].GetType().GetProperty("Dept_Code").GetValue(addlist[i]).ToString()) && addlist[i].GetType().GetProperty("Upper_Dept").GetValue(addlist[i]).ToString() != string.Empty)
        {
            entityDepartment.UpperDept = addlist[i].GetType().GetProperty("Upper_Dept").GetValue(addlist[i]).ToString();
        }
        entityDepartment.LineId = Convert.ToInt32(addlist[i].GetType().GetProperty("Line_ID").GetValue(addlist[i]));
        entityDepartment.Creator = userid;
        entityDepartment.CreateDate = DateTime.Now;
        expectCount++;
        Entry(SySDepartments).State = EntityState.Modified;
        SySDepartments.Add(entityDepartment);

        //添加 Value
        SySGlobalMultipleLanguageValue entityGlobalMultipleLanguageValue = new SySGlobalMultipleLanguageValue();
        entityGlobalMultipleLanguageValue.LookupType = "SY_S_DEPARTMENT.DEPT_CODE";
        entityGlobalMultipleLanguageValue.LookupValue = addlist[i].GetType().GetProperty("Dept_Code").GetValue(addlist[i]).ToString();
        entityGlobalMultipleLanguageValue.Meaning = addlist[i].GetType().GetProperty("Name").GetValue(addlist[i]).ToString();
        entityGlobalMultipleLanguageValue.Language = "zh-cn";
        entityGlobalMultipleLanguageValue.Creator = userid;
        entityGlobalMultipleLanguageValue.CreateDate = DateTime.Now;
        Entry(SySGlobalMultipleLanguageValues).State = EntityState.Modified;
        expectCount++;
        SySGlobalMultipleLanguageValues.Add(entityGlobalMultipleLanguageValue);
    }

    using (var transaction = Database.BeginTransaction())
    {
        try
        {
            int count = SaveChanges();
            blResult = count == expectCount;
            if (blResult)
                transaction.Commit();
            else
                transaction.Rollback();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            var ms = ex;
        }
    }
    return blResult;
}

public bool UpdateDepartmenRepository(List<object> CurrentCode, List<object> ChangetCode, string userid)
{
    int expectCount = 0;
    bool blResult = false;

    var departments = Contexts.SySDepartments.Where(x => x.Facility.Equals("APAT") && x.Site.Equals("SZ") && x.DeptCode.Equals(CurrentCode[0].ToString())).First();
    var globalmultiplelanguagevalues = Contexts.SySGlobalMultipleLanguageValues.Where(i => i.LookupValue.Equals(CurrentCode[0].ToString())).First();

    //修改到新的节点
    //update_department.DeptCode = current_department.DeptCode;
    if (!ChangetCode[0].ToString().Equals("DO") && !ChangetCode[0].ToString().Equals(departments.DeptCode))
    {
        departments.UpperDept = ChangetCode[0].ToString();
        departments.LineId = Convert.ToInt32(ChangetCode[1].ToString());
        departments.Modifier = userid;
        departments.ModifyDate = DateTime.Now;
        expectCount++;
        Entry(departments).State = EntityState.Modified;
       

        globalmultiplelanguagevalues.Modifier = userid;
        globalmultiplelanguagevalues.ModifyDate = DateTime.Now;
        expectCount++;
        Entry(globalmultiplelanguagevalues).State = EntityState.Modified;
        
    }
    using (var transaction = Database.BeginTransaction())
    {
        try
        {
            int count = SaveChanges();
            blResult = count == expectCount;
            if (blResult)
                transaction.Commit();
            else
                transaction.Rollback();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            var ms = ex;
        }
    }
    return blResult;
}

public bool UpdatenameRepository(string DeptCode,string txtName,string userid)
{
    int expectCount = 0;
    bool blResult = false;

    var globalmultiplelanguagevalues = Contexts.SySGlobalMultipleLanguageValues.Where(i => i.LookupValue.Equals(DeptCode)).First();

    globalmultiplelanguagevalues.Meaning = txtName;
    globalmultiplelanguagevalues.Modifier = userid;
    globalmultiplelanguagevalues.ModifyDate = DateTime.Now;
    globalmultiplelanguagevalues.Language = "zh-cn";
    expectCount++;
    Entry(globalmultiplelanguagevalues).State = EntityState.Modified;

    using (var transaction = Database.BeginTransaction())
    {
        try
        {
            int count = SaveChanges();
            blResult = count == expectCount;
            if (blResult)
                transaction.Commit();
            else
                transaction.Rollback();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            var ms = ex;
        }
    }
    return blResult;
}

demo下载