Web Services 在ServiceStack OrmLite中,如何使用多个连接表更新表?

zi8p0yeb  于 2023-10-24  发布在  其他
关注(0)|答案(1)|浏览(138)

我正在写一个Servicestack更新查询与连接多个表在C#。我已经搜索了网络,并通过所有相关的文档和网站,但无法找到足够的细节更新与连接查询。
我想用连接三个不同的表和where子句来更新一个表。我已经尝试了实体框架的所有方法,但无法做到这一点。我可以通过Db.ExecuteSql(SqlQuery)来做到这一点,但我想通过实体框架来做到这一点。我的查询如下。
我想更新心率表连接的UpdateStatus与PatientDetails、DeviceSession、PatientSession和HeartRate表,where子句为HeartRate。timestamp =“@starttime”和PatientId =“@PatientId”
SqlQuery =

UPDATE HeartRate  SET UpdateStatus = 1
WHERE  HeartRateID IN ( SELECT hr.HeartRateID
FROM PatientDetails pd join PatientSession ps on pd.PatientDetailsId = ps.ByPatientId
 join DeviceSession ds on  ps.PatientSessionId =  ds.ByPatientSessionId     join HeartRate hr on  ds.DeviceSessionID = hr.ByDevSessionID
 WHERE
  pd.PatientId = '@PatientId'
  AND
  hr.Timestamp = '@starttime'
  order by hr.Timestamp Asc )

我需要下面的东西(它是错误的和不完整的)。

Db.UpdateOnly(
    new HeartRate { UpdateStatus = 1  },
    ps => new { ps.UpdateStatus  },
.SqlJoinBuilder<DeviceSession, PatientSession>((ds2, ps) => ds2.ByPatientSessionId == ps.PatientSessionId)
.Join<PatientSession, PatientDetails>((ps2, pd) => ps2.ByPatientId == pd.PatientDetailsId)
.Where<HeartRate, PatientDetails>((lthr2, pd2) => (lthr2.Timestamp == @starttime) && pd2.PatientId == PatientId)
.OrderBy(lthr2 => lthr2.Timestamp));
ldfqzlk8

ldfqzlk81#

//试试这个

var model = from v in Db.HeartRate
                  where
                  (
                      from pd in Db.PatientDetails
                      join ps in Db.PatientSession on pd.PatientDetailsId equals ps.ByPatientId
                      join ds in Db.DeviceSession on ps.PatientSessionId equals ds.ByPatientSessionId
                      join hr in Db.HeartRate on ds.DeviceSessionID equals hr.ByDevSessionID
                      where pd.PatientId == PatientId && hr.Timestamp == starttime
                      select new { HeartRateID = hr.HeartRateID }
                  ).ToList().Contains(v.HeartRateID)
                  select v;
        foreach (var item in model)
        {
            item.UpdateStatus = 1;
            Db.SaveChanges();
        }

相关问题