EF eqivalent的行受到影响SqlCommand.ExecuteNonQuery的 [英] EF eqivalent for rows affected of SqlCommand.ExecuteNonQuery

问题描述

在审批工作流程我要确保提醒邮件被发送一次。

使用SqlCommand.ExecuteNonQuery我可以通过测试返回值确保这一点。 什么是使用EF推荐的解决方案？ 根据该文件ObjectContext.SaveChanges不返回等效值。

SqlCommand的例子： （该TransactionScope的用于回滚数据库更新的情况下，Sendmail的失败。）

昏暗的sql =UPDATE LeaveApprovalRequests设置状态='提醒'＆
          WHE​​RE ID = 3，国家与LT;＆GT;'提醒'
使用范围作为新的TransactionScope
    使用CNX作为新的SqlConnection（My.Settings.connectionString）
        cnx.Open（）
        DIM在cmd作为新的SqlCommand（SQL，CNX）
        如果1 = cmd.ExecuteNonQuery然后
             发送邮件（）
        结束如果
        scope.Complete（）
    结束使用
结束使用

通过启用乐观并发（使用ConcurrencyMode =固定在RowVersion财产）和追赶OptimisticConcurrencyException我能够确定，如果对象是在卖场实际更新。 现在的TransactionScope（如果使用的SendMail不回滚数据库更新）抛出一个死锁错误。 为什么呢？

使用范围作为新的TransactionScope
  利用CTX作为新ApprovalEntities
    尝试
      昏暗的批准= ctx.LeaveApprovalRequests。
        凡（功能（R）r.ID = 3，r.State =创建
        ）.FirstOrDefault
      如果审批没什么然后
        Console.WriteLine（找不到）
        退出小组
      结束如果
      Threading.Thread.Sleep（4000）
      approval.State =提醒
      ctx.SaveChanges（）
      发送邮件（）
    抓住EX作为OptimisticConcurrencyException
      退出尝试
    结束尝试
  结束使用
  scope.Complete（）
结束使用

解决方案

与莫尔塔扎讨论的结果，我回答我的问题如下：

的SaveChanges返回的对象的数目，它打算更新，而不是它在商店做更新的数量。因此，它必须一起使用以OptimisticConcurrencyException以确定是否改变已成功。我们必须考虑其他性能比一个旨在改变可引起OptimisticConcurrencyException。

读取的实体并以相同的T​​ransactionScope更新它导致死锁。

有关我的任务仅发送一封电子邮件，如果我能够从创建状态更改为提醒我用以下解决方案：

拆分ApprovalRequest实体两个以1：1的关联，在退出OptimisticConcurrencyException，发送邮件使用的TransactionScope的SaveChanges

ApprovalRequests
  ID（PK）
  被要求
  ...
  RowVersion（ConcurrencyMode =固定）

ApprovalRequestStates
  ApprovalRequest_ID（PK，FK）
  国家（ConcurrencyMode =固定）

利用CTX作为新ApprovalEntities
    昏暗的批准= cxt.ApprovalRequests.Where ...
    朦胧状态= ctx.ApprovalRequestStates。
        凡（功能（R）r.ApprovalRequest_ID = approval.ID而r.State =创建
        ）.FirstOrDefault（）
    如果状态是没有什么然后退出小组
    state.State =提醒
    Threading.Thread.Sleep（3000）
    使用范围作为新的TransactionScope
        尝试
            ctx.SaveChanges（）
            发送邮件（）
            scope.Complete（）
        抓住EX作为OptimisticConcurrencyException
            退出尝试
        结束尝试
    结束使用
结束使用

当心！更新子实体通过其父引用时，它会导致父母的DB更新过 - 在这种情况下，引发不必要的OptimisticConcurrencyException。 因此，我没有用：ApprovalRequests.ApprovalRequestStates.State =提醒

In an approval workflow I want to ensure that reminder emails are sent exactly once.

With SqlCommand.ExecuteNonQuery I can ensure this by testing the return value. What is the recommended solution using EF? According to the documentation ObjectContext.SaveChanges does not return an equivalent value.

SqlCommand example: (The TransactionScope is used to rollback the DB update in case SendMail fails.)

Dim sql = "UPDATE LeaveApprovalRequests SET State = 'Reminded'" &
          " WHERE ID=3 AND State <>'Reminded'"
Using scope As New TransactionScope
    Using cnx As New SqlConnection(My.Settings.connectionString)
        cnx.Open()
        Dim cmd As New SqlCommand(sql, cnx)
        If 1 = cmd.ExecuteNonQuery Then
             SendMail()
        End If
        scope.Complete()
    End Using
End Using

By enabling optimistic concurrency (using ConcurrencyMode=Fixed on a RowVersion property) and catching the OptimisticConcurrencyException I am able to identify if the object was actually updated in the store. Now the TransactionScope (used to rollback the DB update if SendMail fails) throws a deadlock error. Why?

Using scope As New TransactionScope
  Using ctx As New ApprovalEntities
    Try
      Dim approval = ctx.LeaveApprovalRequests.
        Where(Function(r) r.ID = 3 And r.State = "Created"
        ).FirstOrDefault
      If approval Is Nothing Then
        Console.WriteLine("not found")
        Exit Sub
      End If
      Threading.Thread.Sleep(4000)
      approval.State = "Reminded"
      ctx.SaveChanges()
      SendMail()
    Catch ex As OptimisticConcurrencyException
      Exit Try
    End Try
  End Using
  scope.Complete()
End Using

解决方案

As a result of the discussion with Morteza I answer my question as follows.

SaveChanges returns the number of objects it intends to update, not the number it did update in the store. Thus it must be used together with OptimisticConcurrencyException to determine if the change succeeded. One must consider that other properties than the one intended to change can cause a OptimisticConcurrencyException.

Reading an entity and updating it in the same TransactionScope causes a deadlock.

For my Task "Only send an email if I am able to change the State from created to reminded" I use the following solution:

Split the ApprovalRequest entity in two with a 1:1 association, exit on OptimisticConcurrencyException, send mail in TransactionScope with SaveChanges.

ApprovalRequests
  ID (PK)
  RequestedBy
  ...
  RowVersion (ConcurrencyMode=Fixed)

ApprovalRequestStates
  ApprovalRequest_ID (PK, FK)
  State (ConcurrencyMode=Fixed)

Using ctx As New ApprovalEntities
    Dim approval = cxt.ApprovalRequests.Where ...
    Dim state = ctx.ApprovalRequestStates.
        Where(Function(r) r.ApprovalRequest_ID = approval.ID And r.State = "Created"
        ).FirstOrDefault()
    If state Is Nothing Then Exit Sub
    state.State = "Reminded"
    Threading.Thread.Sleep(3000) 
    Using scope As New TransactionScope
        Try
            ctx.SaveChanges()
            SendMail()
            scope.Complete()
        Catch ex As OptimisticConcurrencyException
            Exit Try
        End Try
    End Using
End Using

Beware! Updating a child entity when referencing it via its parent causes a DB update of the parent too - in this case throwing an unwanted OptimisticConcurrencyException. Thus I did not use: ApprovalRequests.ApprovalRequestStates.State = "Reminded"

这篇关于EF eqivalent的行受到影响SqlCommand.ExecuteNonQuery的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！