TransactionScope around Many to Many Insert with Entity Framework returns TimeoutException

Question

Why do I get an DbUpdateException - there are no further details - when I try to insert a new pupil to an existing schoolclassCode?

This is a many to many relation.

var schoolclassCode = await context.SchoolclassCodes.SingleAsync(s => s.Id == pupil.SchoolclassCodeId);
schoolclassCode.Pupils.Add(pupil);
context.Entry(schoolclassCode).State = EntityState.Modified;
int count = await context.SaveChangesAsync();

Do I have to insert the pupil firstly in context.Pupils.add(pupil) ?

I thought I can do the insert of the pupil and set into relation to a schoolclasscode in ONE go by doing

schoolclassCode.Pupils.Add(pupil);

and then set the schoolclassCode as modified.

How is Insert an entity done in many to many relation WITH an existing principal/parent entity?

UPDATE

System.Data.Entity.Infrastructure.DbUpdateException was unhandled by user code
  HResult=-2146233087
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=mscorlib
  StackTrace:
       bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       bei System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
       bei TGB.Repository.PupilRepository.<AddPupil>d__8.MoveNext() in c:\Repository\TGB\TGB.Repository\PupilRepository.cs:Zeile 29.
    --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
       bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       bei System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
       bei Business.IntegrationTests.PupilRepositoryTests.CreatePupil() in c:\TGB\IntegrationTests\PupilRepositoryTests.cs:Zeile 31.
  InnerException: System.Data.Entity.Core.UpdateException
       HResult=-2146233087
       Message=An error occurred while updating the entries. See the inner exception for details.
       Source=EntityFramework
       StackTrace:
            bei System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.<UpdateAsync>d__0.MoveNext()
         --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
            bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
            bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
            bei System.Data.Entity.Core.Objects.ObjectContext.<ExecuteInTransactionAsync>d__3d`1.MoveNext()
         --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
            bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
            bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
            bei System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStoreAsync>d__39.MoveNext()
         --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
            bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
            bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
            bei System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<ExecuteAsyncImplementation>d__9`1.MoveNext()
         --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
            bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
            bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
            bei System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesInternalAsync>d__31.MoveNext()
       InnerException: System.Data.SqlClient.SqlException
            HResult=-2146232060
            Message=Timeout abgelaufen. Das Zeitlimit wurde vor dem Beenden des Vorgangs überschritten oder der Server reagiert nicht.
            Source=.Net SqlClient Data Provider
            ErrorCode=-2146232060
            Class=11
            LineNumber=0
            Number=-2
            Procedure=""
            Server=MyAccount\SQLEXPRESS
            State=0
            StackTrace:
                 bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                 bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                 bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                 bei System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
                 bei System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
                 bei System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
              --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
                 bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 bei System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
                 bei System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.<ExecuteAsync>d__0.MoveNext()
              --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
                 bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 bei System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.<UpdateAsync>d__0.MoveNext()
            InnerException: System.ComponentModel.Win32Exception
                 HResult=-2147467259
                 Message=Der Wartevorgang wurde abgebrochen
                 ErrorCode=-2147467259
                 NativeErrorCode=258
                 InnerException: 

UPDATE 2

public class SchoolclassCode
{
    public SchoolclassCode()
    {
        Pupils = new HashSet<Pupil>();
    }

    public int Id { get; set; }
    public ISet<Pupil> Pupils { get; set; }

}

public class Pupil
{
    public Pupil()
    {
         SchoolclassCodes = new HashSet<SchoolclassCode>();
    }

    public int Id { get; set; }
    public ISet<SchoolclassCode> SchoolclassCodes { get; set; }

    [NotMapped]
    public int SchoolclassCodeId { get; set; }
}

Actually the many to many relationship should work by convention but still I explicitly setup the configuration betweeen SchoolclassCode and Pupil due to the DbUpdateException behavior which I could not explain.

public class SchoolclassCodeConfiguration : EntityTypeConfiguration<SchoolclassCode>
{
    public SchoolclassCodeConfiguration()
    {
        base.Property(p => p.SchoolclassNumber).IsRequired().HasMaxLength(10);
        base.Property(p => p.SubjectName).IsRequired().HasMaxLength(10);
        base.Property(p => p.ClassIdentifier).IsOptional().HasMaxLength(2);
        base.HasMany(p => p.Pupils)
        .WithMany(p => p.SchoolclassCodes)
        .Map(x =>
        {
            x.MapLeftKey("SchoolclassCodeId");
            x.MapRightKey("PupilId");
            x.ToTable("SchoolclassCodePupil");
        });
    }
}

LOG ERRORS happening on SaveChanges inside my Attach Pupil method

UPDATE [dbo].[SchoolclassCode]
SET [SchoolclassNumber] = @0, [SubjectName] = @1, [Color] = @2, [ClassIdentifier] = @3, [SchoolyearId] = @4
WHERE ([Id] = @5)

-- @0: '7' (Type = String, Size = 10)

-- @1: 'Math' (Type = String, Size = 10)

-- @2: '5' (Type = Int32)

-- @3: 'a' (Type = String, Size = 2)

-- @4: '1' (Type = Int32)

-- @5: '1' (Type = Int32)

-- Executing asynchronously at 24.06.2015 16:57:57 +02:00

-- Completed in 8 ms with result: 1

INSERT [dbo].[Pupil]([FirstName], [LastName], [Postal], [City], [Street])
VALUES (@0, @1, NULL, NULL, NULL)
SELECT [Id]
FROM [dbo].[Pupil]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()


-- @0: 'Max' (Type = String, Size = 25)

-- @1: 'Mustermann' (Type = String, Size = 25)

-- Executing asynchronously at 24.06.2015 16:49:30 +02:00

-- Completed in 2 ms with result: SqlDataReader



INSERT [dbo].[SchoolclassCodePupil]([SchoolclassCodeId], [PupilId])
VALUES (@0, @1)

-- @0: '1' (Type = Int32)

-- @1: '1' (Type = Int32)

-- Executing asynchronously at 24.06.2015 16:49:30 +02:00

-- Completed in 2 ms with result: 1



Committed transaction at 24.06.2015 16:49:30 +02:00

Closed connection at 24.06.2015 16:49:30 +02:00

The thread 0x60 has exited with code 259 (0x103).
The thread 0x1178 has exited with code 259 (0x103).
The thread 0xdc0 has exited with code 259 (0x103).
A first chance exception of type 'System.NullReferenceException' occurred in Business.IntegrationTests.dll

THE REAL PROBLEM

are the TransactionScope before and after each test which I commented out and THEN everything worked fine!

Why do I get this Exception when I use this code - Other tests + TransactionScope work fine !!! -

public abstract class IntegrationTestsBase
    {
        protected TransactionScope TransactionScope;

        [TestInitialize]
        public void TestSetup()
        {
            TransactionScope = new TransactionScope();
        }

        [TestCleanup]
        public void TestCleanup()
        {
            TransactionScope.Dispose();
        }
    }

Homogenous

A DbUpdateException usually indicates that something went wrong at the database level, not at the EF level. Normally, the inner exception will have more interesting details. Can you post the full exception stack trace? – Mazdaism

Very good recognized! See my updated question. There is not much about the real error. But the error source is the sql data provider not EF. – Homogenous

I also updated the question with both participating Entities in case there is still something wrong... – Homogenous

I can't translate, but it sounds like you have a timeout? Timeout abgelaufen. Das Zeitlimit wurde vor dem Beenden des Vorgangs überschritten oder der Server reagiert nicht.. Maybe you can try logging the SQL statements that were generated to see what's wrong with them, by adding this: context.Database.Log = s => Debug.WriteLine(s); – Mazdaism

Updated question with logging details and corrected the logging details with Update SchoolclassCode too! – Homogenous

Funny that I did not see this error before maybe it showed now because of my Reboot...: int count = await context.SaveChangesAsync(); The count of SaveChanges returns THREE ??? Why THREE I thought its the affected Rows and not Affected Tables because 3 tables are accessed actually... – Homogenous

The SQL logs you posted show that all the statements completed ok. Are you still getting the error? – Mazdaism

The data was inserted correctly @Mazdaism I gotta google about the SaveChanges() return value. Seems I got something wrong about it :-) Thanks for the help + logging hint! – Homogenous

EVERYTHING back the problem is now known why it suddely worked! Please read my updated question! – Homogenous

Let us continue this discussion in chat. – Homogenous

Answer 1

After I have read this link: Get TransactionScope to work with async / await

I knew I am in that situation:

"In .NET Framework 4.5.1, there is a set of new constructors for TransactionScope that take a TransactionScopeAsyncFlowOption parameter. According to the MSDN, it enables transaction flow across thread continuations."

TransactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);

The correct parameter to the TransactionScope will make my Integration Test run GREEN now :-) Happy TDD !!!

Homogenous