EF6 dbContext SaveChanges: how to catch rollback?

Question

The database returns the ID of the new record after I call SaveChanges(). And the IDs increments each time I pass a new object into my Add method, so I know the database (Azure) is in the loop. But nothing is actually being inserted into the database because there was a rollback. Other than doing .Find(newId) and testing for null, which means another trip to the DB, is there any way to catch this?

This happened because I had inadvertently created a relationship which required an insert into a related table. What bugs me is that the transaction failed silently. If I had been using ADO.NET and a paramaterized stored procedure it would have thrown an exception and returned something useful.

I am new to EF / Code First and working on a greenfield project so have the luxury of building the database with code first. It's something of an adventure so far.

UPDATE: Providing code per Aron's request. Also, more background. I was using NLogMvc which has its own context to write messages to the database. There may have been an issue there with the distributed transaction Aron mentioned. I have restructured the solution in any case, removed NLogMvc, and shifted to Ninject.Extensions.Loggings.nlog2

I know that what was happening on the database was that I had failed to clean up some old code that required in insert into a related table. SQL Server would have thrown an error rolled back the transaction.

FINAL UPDATE TO THE QUESTION: All my assumptions as to what might be causing the problem were wrong. Turns out I inadvertently screwed up by creating an instance of TransactionScope in the repository constructor. See my answer to my own question (below) for the details.

The code:

        public int AddUpdateSlideshow(SlideshowDto slideshow)
    {
        if (!_user.IsInRole("Admin")) return -1;
        bool isNewShow = false;
        var now = DateTime.Now;
        try
        {
            using (var context = new ApplicationDbContext())
            {
                context.Database.Log = s => _logger.Info(s);

                int slideshowId;
                if (slideshow.SlideshowId == 0)
                {
                    isNewShow = true;
                    var newSlideshow = new Slideshow
                    {
                        Name = slideshow.Name,
                        Description = slideshow.Description,
                        DateAdded = now,
                        AddedBy = _userName,
                        DateLastUpdated = now,
                        UpdatedBy = _userName,
                        IsActive = slideshow.IsActive
                    };

                    context.Slideshows.Add(newSlideshow);

                    context.SaveChanges();

                    slideshowId = newSlideshow.SlideshowId;
                }
                else
                {
                    Slideshow dbUpdate =
                        context.Slideshows.Find(
                            slideshow.SlideshowId);
                    if (dbUpdate != null)
                    {
                        slideshowId = slideshow.SlideshowId;
                        dbUpdate.Name = slideshow.Name;
                        dbUpdate.Description = slideshow.Description;
                        dbUpdate.IsActive = slideshow.IsActive;
                        dbUpdate.DateLastUpdated = now;
                        dbUpdate.UpdatedBy = _userName;
                        context.SaveChanges();

                        // Number of objects written to the database.
                        // Zero means the update failed.
                        int result = context.SaveChanges();
                        slideshowId = result == 0 ? result : slideshow.SlideshowId;
                    }
                    else
                    {
                        var errorMessage = string.Format(
                            "The {0} method in {1} threw an ArgumentNullException exception. The user was {2}.",
                            MethodBase.GetCurrentMethod().Name, _className, _userName);
                        _logger.Error(errorMessage);

                        throw new ArgumentNullException("slideshow");
                    }
                }

                // The database will return new id value BUT
                // if for some reason the transaction fails the
                // changes will be rolled back and we won't have the
                // new record. So we need to confirm that we do in
                // fact have one.
                if (isNewShow)
                {
                    var newShow = context.Slideshows.Find(slideshowId);
                    if (newShow == null)
                    {
                        slideshowId = 0;
                    }                        
                }

                return slideshowId;
            }
        }
        catch (Exception ex)
        {
            var errorMessage = string.Format("The {0} method in {1} threw an exception. The user was {2}.",
                MethodBase.GetCurrentMethod().Name, _className, _userName);
            _logger.Error(errorMessage, ex);

            return -1;
        }
    }

Answer 1

Finally resolved this by building out a repository that worked as expected for some other entities, then doing some a-b comparisons with the log files.

In the case of the failed insert, in the repository constructor I had created a TransactionScope instance for a private member variable. I was intending to use a transaction for something else, however, as you can see from my code, I did not use it for the insert method that failed.

I did not create a TransactionScope instance in the constructor of the repository with the insert method that worked.

I used the new EF6 logging facility for both transactions. Note the difference in the SQL EF sends to the database:

Insert that failed: INSERT [dbo].[Media_Slideshow]([Name], [Description], [DateAdded], [AddedBy], blah blah blah Closed connection at 6/8/2014 12:37:24 PM -05:00 Slideshow ID is 3

Insert that succeeded: Started transaction at 6/8/2014 12:41:35 PM -05:00 INSERT [dbo].[Blog]([Title], [BlogContent], [DateAdded], [AddedBy], blah, blah, blah Committed transaction at 6/8/2014 12:41:35 PM -05:00 Closed connection at 6/8/2014 12:41:35 PM -05:00 Blog ID is 5

So, for reasons that I do not at this point understand and don't have time to investigate, instantiating TransactionScope in the repository constructor resulted in the insert failing.

As noted in my question: 1. The object I was trying to insert had no navigation properties. 2. I did not (intentionally) use TransactionScope on the method that failed. 3. I did not (intentionally) use TransactionScope on the method that succeeded. 4. SaveChanges() returned the id created by the database for both inserts but in the first case nothing was inserted into the database. 5. There was no other context open. 6. The method that failed did not throw an exception; everything looked just peachy until the controller attempted to retrieve the record with the new id. The ONLY way I was able to diagnose this problem was with the logger.

And as can be clearly observed by looking at the log, EF does create a transaction under the covers which in the case of the failed insert I somehow screwed up by instantiating TransactionScope.