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();
        }
    }

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 !!!