title: Entity Framework "The current transaction cannot be committed and cannot support operations that write to the log file."
body: <p>A piece of software I'm working on is used to upload data from CSV files into a database. Interaction with the database is using EF6. To submit data, there is a Stored Procedure named "InsertRow" which accepts an InterfaceID (string), the row of data(string), the delimiter character(string), and has output paramaters: Error Code (int) and Error Message (0). The SP definition looks like this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[InsertRow]
  (
    @InterfaceID NVARCHAR(MAX) ,
    @Row NVARCHAR(MAX) ,
    @Delimiter NVARCHAR(MAX) ,
    @ErrorCode INT OUTPUT ,
    @ErrorMessage NVARCHAR(MAX) OUTPUT    
  )
  </code></pre>
  
  <p>It's called from Entity like this: </p>
  
  <pre><code>public virtual int InsertRow(string interfaceID, string row, string delimiter)
      {
          var errorCode = new ObjectParameter("ErrorCode", typeof(int));
  
          var errorMessage = new ObjectParameter("ErrorMessage", typeof(string));
  
          var interfaceIDParameter = interfaceID != null ?
              new ObjectParameter("InterfaceID", interfaceID) :
              new ObjectParameter("InterfaceID", typeof(string));
  
          var rowParameter = row != null ?
              new ObjectParameter("Row", row) :
              new ObjectParameter("Row", typeof(string));
  
          var delimiterParameter = delimiter != null ?
              new ObjectParameter("Delimiter", delimiter) :
              new ObjectParameter("Delimiter", typeof(string));
  
          return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("InsertRow", interfaceIDParameter, rowParameter, delimiterParameter, errorCode, errorMessage);
      }
  </code></pre>
  
  <p>The Procedure works only if a temporary table has been created (done before this is called), so the EF context needs to remain open while all rows are submitted. This is not a problem and is being managed. The procedure performs some validation and obviously some conversion from string to the correct data types. The output parameters Error Code and Error Message get populated with error info for the application to handle. The procedure ends with a catch that does this:</p>
  
  <pre><code>    BEGIN CATCH 
      SET @ErrorCode = ERROR_NUMBER()  
      SET @ErrorMessage = ERROR_MESSAGE()
      INSERT INTO ErrorLog values (GetDate(),@InterfaceID, @Row, @Deliminator, ERROR_NUMBER(), ERROR_MESSAGE())
  END CATCH;
  </code></pre>
  
  <p>Most of the time, this works. However, in some circumstances, the Error Code and Error Message are not being set, and instead an EntityCommandExecutionException is thrown with the following Inner Exception message:</p>
  
  <blockquote>
    <p>"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."</p>
  </blockquote>
  
  <p>I'm struggling to understand why some errors that are thrown get caught here and handled correctly, while others fail. An example of this is when submitting the string "date text" as a part of the Row that should be entered into a DateTime field. This should be caught and reported, but instead I get the generic exception.</p>
  
  <p>It's also worth noting that I have removed the insert statement from the Catch block too. The exception's message then changes to </p>
  
  <blockquote>
    <p>"Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."</p>
  </blockquote>
  
  <p>Has anyone seen this issue before? What are we missing here that can help us to resolve it? What can cause the transaction to be uncommittable? We need to effectively capture these messages so we can report back to the user the issues with their files.</p>