多表Linq更新查询

本文关键字：查询 更新 Linq 多表 | 更新日期: 2023-09-27 18:12:55

我需要将以下查询转换为Linq:-

UPDATE INOUT
SET TIME_FLD1 = A.SCODE,
    TIME_FLD2 = B.STATUS
FROM SHIFTSCHEDULE A,
     SHIFT B,
     INOUT C
WHERE A.COMPANY = B.COMPANY
  AND A.SCODE = B.SCODE
  AND A.CODE = C.CODE
  AND A.SHIFT_DATE = C.DATE1

通常我这样做更新:-

    var data = ctx.INOUT.Where(m=>m.COMPANY == COMPANY).Select(m=>m).FirstOrDefault();
    ctx.INOUT.Attach(rec);    
    ctx.Entry(data).State = EntityState.Modified;
    ctx.SaveChanges();

谁能解释一下Sql查询到底是做什么的?如何在Linq中实现?

多表Linq更新查询

我想这就是你要找的:

        //Select your data
        var data = from A in contextDB.SHIFTSCHEDULE
                    from B in contextDB.SHIFT
                    from C in contextDB.INOUT
                    where   A.COMPANY == B.COMPANY
                            && A.SCODE == B.SCODE 
                            && A.CODE == C.CODE 
                            && A.SHIFT_DATE == C.DATE1
                    select new { A, B, C};
        //interate througt collection and update
        foreach (var row in data)
        {
            row.C.TIME_FLD1 = row.A.SCODE;
            row.C.TIME_FLD2= row.B.STATUS;
        }
        //save changes
        contextDB.SaveChanges();

Linq to SQL不能同时更新多个记录。这很可惜，但这是真的，所以你应该得到你的集合，然后逐个更新实体。

SQL查询是从多个表更新的，它的LINQ版本应该是这样的:

var data = (from A in contextDB.SHIFTSCHEDULE
        from B in contextDB.SHIFT
         from C in contextDB.INOUT
        where A.COMPANY==B.COMPANY
        && A.SCODE==B.SCODE && A.CODE==C.CODE && A.SHIFT_DATE==C.DATE1
        select new {A,B,C}).FirstOrDefault();
if(data != null)
{
  data.TIME_FLD1= data.SCODE,
  data.TIME_FLD2= data.STATUS
 contextDB.SubmitChanges();
 }
相关文章：