none
EF6 sometimes ExecuteFunction ends with open transactions RRS feed

  • Question

  • Hey

    first off: THX for reading

    I do have a problem with EntityFramework and StoredProcedures on a Microsoft SQL Server. My Stored Procedure inserts Data in multiple tables (afterwards the Context is destroyed, there is no need in doing this with SaveChanges() since the cache won't be present the next time the Context is used and there is no need to get a Result from the StoredProcedure nor to query the database right after the StoredProcedure was executed).

    The Project where the EntityFramework is used on is a Windows Service hosting a WCF Service.

    On a local Network the StoredProcedure ran without any Exceptions, now when it is used over the Internet, there will be an Exception thrown (EntityCommandExecutionException) complaining about a TRANCOUNT = (EDIT: wrote 1, actaully is 0)0 randomly (complaning about the 0 cause it expects to have an Transaction open, the one that EF wraps around the call). There is no way, the StoredProcedure is causing this issue. (EDIT: Following therefore is the wrong way to check it)I checked for that by introducing a while clause in the StoredProcedure that would "COMMIT TRANSACTION" as long as TRANCOUNT was above 0, also removed the Explicit Transaction completely, the Exception still occured randomly. (randomly => I could not figure out a way to reproduce it, making it occure all the time).

    Somewhere I read about this Error occuring when a timeout is reached on the EntityFramework side, just that ion this case, it cannot be the cause, since the whole process of Resopnding to a Request takes under 10 seconds and the EF timeout is set to 30sec by default (i did not change that timeout, nor do I know how).

    I just need a way to figure out how to make this process stable or just a way to figure out whats happening exactly.

    (Forgot: Even though the EF throws an Exception, the data will be present in the Database after wards always)

    Please throw any ideas you might have at me, THX for your effort.

    (EDIT: So the actual approach here is to save the current Transaction count and test it to be greater before commiting or rollbacking the expliecit transaction in the stored procedure, testing that now)


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.


    • Moved by CoolDadTx Friday, March 4, 2016 5:00 PM EF related
    • Edited by MDeero Monday, March 7, 2016 9:47 AM
    Friday, March 4, 2016 3:42 PM

Answers

  • Why don't you use the EF backdoor to run the sproc and take EF out of it? You can use a begin Transaction in the sproc with a trans complete. Or you can use straight up ADO.NET with SQL Command Objects calling the sproc and using System.Transactions with both options using the EF backdoor.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    • Marked as answer by MDeero Wednesday, March 9, 2016 1:01 PM
    Friday, March 4, 2016 3:55 PM
  • >just wanna understand whats the problem there or how to find out the problem.

    The actual exception message and SQL Error details would be a good start.  If you dump some program state when this happens you might have a better chance of a repro.

    If it happens frequently enough you could use a SQL Trace to see the sequence of queries and commands that lead up to the error.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by MDeero Wednesday, March 9, 2016 1:01 PM
    Friday, March 4, 2016 9:38 PM
  • >So you think the error resides within my StoredProceduer? Maybe you can have a look:

    Yep.  Looks like one of your THROW statements or some other error is occurring.  Then in the CATCH block you are calling ROLLBACK TRANSACTION.  This rolls back the whole transaction and then you exit the stored procedure with @@trancount=0.  It's illegal to do that, so you also get the error that you are catching and seeing in your program.

    But that error is not the root cause. 

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, March 7, 2016 5:02 PM

All replies

  • Your question probably should be moved to the ADO.NET Entity Framework forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 4, 2016 3:51 PM
  • Yes it should... only found that afterwards, sorry for the inconvenience.

    I don't wanna repost, cause it would mean two similar questions.

    As far as I know, I cannot move this myself... if a Moderator could move this, i'd greatly appreciate it.


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.

    Friday, March 4, 2016 3:54 PM
  • Why don't you use the EF backdoor to run the sproc and take EF out of it? You can use a begin Transaction in the sproc with a trans complete. Or you can use straight up ADO.NET with SQL Command Objects calling the sproc and using System.Transactions with both options using the EF backdoor.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    • Marked as answer by MDeero Wednesday, March 9, 2016 1:01 PM
    Friday, March 4, 2016 3:55 PM
  • Thx for the work around idea...

    Just not quite what i am looking for. Just wanna understand whats the problem there or how to find out the problem. (I mean, EF6 was reworked to use more Transactions, even on ExecuteFunction, so there really shouldn't be the problem described here. Also I really much like to use the EF as ORM and not handle any SQL statements myself, not ebven the ones as trivial as EXECUTE [STOREDPROCEDURENAME])

    I might do the workaround though, when nothing comes up in the near future

    THX


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.



    • Edited by MDeero Friday, March 4, 2016 4:03 PM
    Friday, March 4, 2016 4:01 PM
  • >just wanna understand whats the problem there or how to find out the problem.

    The actual exception message and SQL Error details would be a good start.  If you dump some program state when this happens you might have a better chance of a repro.

    If it happens frequently enough you could use a SQL Trace to see the sequence of queries and commands that lead up to the error.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by MDeero Wednesday, March 9, 2016 1:01 PM
    Friday, March 4, 2016 9:38 PM
  • Type:      EntityCommandExecutionException

    Message: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1"

    I am afraid these two don't give the right info. It is NOT the Transaction not being closed, definitely not. You can trust me on that.

    SQL Trace was not done yet, i will try that on Monday. My hopes for some clues are a bit low though, since i already determined (... of course I can be wrong, so I will try anyways) that the actual Error has nothing to do with the SQL Server Instance (or the transaction count, as the Exception states).

    "dump some program state" -> whadda mean? Like logging or like dumping the actual memory? The latter is something i don't know how (or at least never did and don't know where to begin, but shouldn't be that hard i guess) and logging is already at place telling me nothing. The WCF may also be an issue here (for tracing) since it's not very clear on what exactly happens in there. After the Execution of the Command, nothing else gets executed, the Exception is Handled by an ErrorHandler (Extension point in WCF) and serialized... bla bla... all that don't really matter, cause the Exception should not be thrown.

    If you could advice me, what to dump exactly, i kinda need a direction where to look at.

    THX for your replies so far guys

    PS: I relize i have left out a bit of useful info, the SQL Server is only used by this Service, and the Service is only called by one machine and the machine only sends one request at a time and even waits for the responses (so sequential calls, nothing like multiple Transactions opened in Sessions or stuff like that, only one transaction at a time, except for the Transactions that the EntityFramework wraps around every Database manipulating statement)


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.



    • Edited by MDeero Saturday, March 5, 2016 12:25 AM
    Saturday, March 5, 2016 12:19 AM
  • You'll see the code where I had to go to a special sproc to do something right in using EF right in the middle of an insert process.

     public Int32 AddCoP(tblUserCoP cop, List<DTO.DTOKnowArea> kas, List<DTO.DTONeighborhood> nbs,
                                   List<DTO.DTOWExKnowArea> was, List<DTO.DTOModule> mods, Int32 curruserid)
            {
                Int32 ID = 0;
    
                Int32 POCID = 0;
    
                var cm = new ConfigManager();
                string m_constring = cm.GetConnectionStringByInstance("MainWebDB_Net");
    
                //set it to read uncommited 
                var transactionOptions = new TransactionOptions
                {
                    IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
                };
    
                //create the transaction scope, passing our options in 
                using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
                using (var conn = new EntityConnection(mwdb))
                using(var db = new MainWebDBEntities1(conn))
                using(var db1 = new MainWebDBEntities1(conn))
                using(var db2 = new MainWebDBEntities1(conn))
                using(var db3 = new MainWebDBEntities1(conn))
                using(var db4 = new MainWebDBEntities1(conn))
                using(var db5 = new MainWebDBEntities1(conn))
                using(var db6 = new MainWebDBEntities1(conn))
                using(var db7 = new MainWebDBEntities1(conn))
                using (var sqlconn = new SqlConnection(m_constring))
    
                try
                {
                    var addcop = new tblUserCoP();
    
                    addcop.CoP = string.Empty;
                    addcop.CoPTypeID = cop.CoPTypeID;
                    addcop.UserCoPUrlID = cop.UserCoPUrlID;
                    addcop.fdRootFilter = cop.fdRootFilter;
                    addcop.DisplayCoPName = cop.DisplayCoPName;
    
                    addcop.HomePage = GetHomePageURL((Int32) cop.UserCoPUrlID);
    
                    addcop.Overview = cop.Overview;
                    addcop.CoPText1 = cop.CoPText1;
                    addcop.CoPText2 = cop.CoPText2;
                    addcop.CoPText3 = cop.CoPText3;
                    addcop.CoPText4 = cop.CoPText4;
                    addcop.CoPKeywords = cop.CoPKeywords;
                    addcop.RegLocation = cop.RegLocation;
                    addcop.Justification = cop.Justification;
                    addcop.CoPText5 = cop.CoPText5;
                    addcop.TimeZoneID = cop.TimeZoneID;
                    addcop.DelComment = cop.DelComment;
    
                    if (cop.DelRvwDate != Convert.ToDateTime("01/01/0001"))
                        addcop.DelRvwDate = cop.DelRvwDate;
    
                    addcop.WExLocation = cop.WExLocation;
                    addcop.DocMgtDisplayName = cop.DocMgtDisplayName;
                    addcop.CalendarSchedule = cop.CalendarSchedule;
                    addcop.ShortURLName = cop.ShortURLName;
                    addcop.QuestColPostion = cop.QuestColPostion;
                    addcop.VoteColPosition = cop.VoteColPosition;
                    addcop.RetainUsers = cop.RetainUsers;
                    addcop.HeaderLogo = cop.HeaderLogo;
                    addcop.DirBlogTitle = cop.DirBlogTitle;
                    addcop.DirBlogForumID = cop.DirBlogForumID;
                    addcop.DirBlogAllowed = cop.DirBlogAllowed;
                    addcop.SecPriBanner = cop.SecPriBanner;
                    addcop.VSE_KAVersion = cop.VSE_KAVersion;
                    addcop.PromoteMembership = cop.PromoteMembership;
                    addcop.OwnerComments = cop.OwnerComments;
                    addcop.AssignedTo = cop.AssignedTo;
                    addcop.Status = cop.Status;
    
                    db.AddTotblUserCoP(addcop);
                    db.SaveChanges();
    
                    ID = addcop.ID;
    
                    //knowledgearea
    
                    if (kas.Count > 0)
                    {
                        foreach (var knowarea in kas)
                        {
                            if (knowarea.Selected)
                            {
                                var ckna = new tblUserCoPKnowAreas()
                                {
                                    CoPID = ID,
                                    KnowAreaID = knowarea.KNAreaID
                                };
    
                                db1.AddTotblUserCoPKnowAreas(ckna);
                                db1.SaveChanges();
                            }
                        }
                    }
    
                    //neighborhoods
    
                    if (nbs.Count > 0)
                    {
                        foreach (var nehghborhood in nbs)
                        {
                            if (nehghborhood.Selected)
                            {
                                var nbh = new CoP_Neighborhoods()
                                {
                                    CoPID = ID,
                                    NeighborhoodID = nehghborhood.NeighborhoodID
                                };
    
                                db2.AddToCoP_Neighborhoods(nbh);
                                db2.SaveChanges();
                            }
                        }
                    }
    
                    //WeXKnowledgearea
    
                    if (was.Count > 0)
                    {
                        foreach (var wexka in was)
                        {
                            if (wexka.Selected)
                            {
                                var wexk = new tblWExKnAreaRel()
                                {
                                    CoPId = ID,
                                    ThisKnAreaId = wexka.KNAreaID
                                };
    
                                db3.AddTotblWExKnAreaRel(wexk);
                                db3.SaveChanges();
                            }
                        }
                    }
    
                    //Primary Owner
                  
                    var own = new CoP_Owners()
                    {
                        CoPID = ID,
                        POCID = curruserid,
                        PrimaryOwner = true,
                        ReceiveEmail = true,
                        DisplayOnCoP = true,
                        DisplayOrder = 1
    
                    };
    
                    db4.AddToCoP_Owners(own);
                    db4.SaveChanges();
    
                    POCID = own.POCID;
                 
                    // permissions
    
                    var permissions = new tblUserPermissions
                    {
                        CoPID = ID,
                        DateJoin = DateTime.Now,
                        POCID = POCID,
                        TypeID = 3
                    };
    
                    db5.AddTotblUserPermissions(permissions);
                    db5.SaveChanges();
    
                    //modules
    
                    if (mods.Count > 0)
                    {
                        foreach (var module in mods)
                        {
                            if (module.Selected)
                            {
                                var mod = new tblUserCoPModule()
                                {
                                    CoPID = ID,
                                    ModuleID = module.ModuleID,
                                    IsRestricted = module.Restricted
                                };
    
                                db6.AddTotblUserCoPModule(mod);
                                db6.SaveChanges();
                            }
                        }
                    }
    
                    var cophit = compiledQueryCoP.Invoke(db7, ID);
                   
                    if (cophit != null)
                    {
                        cophit.CoP = ID.ToString();
                        cophit.fdRootFilter = ID.ToString();
                        cophit.HomePage = cophit.HomePage + "?Filter=" + ID;
                        db7.SaveChanges(false);
                    }
                    
                    var cmd = new SqlCommand("dbo.yaf_board_create", sqlconn) {CommandType = CommandType.StoredProcedure};
    
                    cmd.Parameters.AddWithValue("@CommunityID", ID);
                    cmd.Parameters.AddWithValue("@BoardName", addcop.DisplayCoPName);
                    cmd.Parameters.AddWithValue("@AllowThreaded", 1);
                    cmd.Parameters.AddWithValue("@UserIDs", POCID.ToString());
                    cmd.Parameters.AddWithValue("@IsHostAdmin", 0);
    
                    sqlconn.Open();
                    cmd.ExecuteNonQuery();
                    
                    transactionScope.Complete();
                    db7.AcceptAllChanges();
                }
                finally
                {
                    conn.Close();
                    sqlconn.Close();
                }
           
                return ID;
            }

    Saturday, March 5, 2016 7:34 PM
  • There are certain exceptions in SQL Server that will automatically rollback the transaction, and the stored procedure might have issued a ROLLBACK. Eg this stored procedure

    create procedure foo
    as
    begin
        if @@trancount > 0 
    	   rollback
    
    end

    So this isn't a EF problem, except that it's kind of tricky to see the root error. 

    The EntityCommandExecutionException should have a SqlException as its InnerExceptoin.  That will have a message and a collection of Errors.  The root cause should be there.

    And in your service code you should unwrap those errors relating to the transaction count, and log the root-cause exception.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, March 5, 2016 10:16 PM
  • @DA924x

    Sorry, i cannot quite understand what you want to tell me with that last port, if it is about the work around, no need to tel me twice, also no need to give me the code, but thx for the extra effort

    @davidbaxterbrowne

    So you think the error resides within my StoredProceduer? Maybe you can have a look:

    CREATE PROCEDURE [dbo].[TheRandomlyFailingProcedureWithEntityFramework]
    	-- just some data
    	@param1 nvarchar(20),
    	@param2 int,
    	@param3 varbinary(MAX)
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    	SET XACT_ABORT ON;
    	SET NOCOUNT ON;
    
    	DECLARE @activeTrans int;
    	SET @activeTrans = @@TRANCOUNT;
    
    	DECLARE @idTable TABLE (id int);
    	DECLARE @msg nvarchar(MAX);
    
    	DECLARE @someID int;
    	DECLARE @table1Id int;
    	DECLARE @table2Id int;
    	DECLARE @table3Id int;
    
    	EXECUTE [FindSomeId] @param1, @someID OUTPUT;
    
    	-- FindSomeId will return -1 when not found
    	IF @someId = -1
    	BEGIN
    		SET @msg = 'No entry for param1 in SomeTable';
    		THROW 50005, @msg, 0;
    	END;
    
    	BEGIN TRANSACTION;
    
    	BEGIN TRY
    		INSERT INTO Table1
    			(param1)
    			OUTPUT inserted.ID
    				INTO @idTable
    			VALUES
    				(@param1);
    		SET @table1ID = (SELECT id FROM @idTable);
    		DELETE FROM @idTable;
    		IF @table1Id IS NULL
    		BEGIN
    			SET @msg = 'Insert Table1 failed';
    			THROW 50004, @msg, 0;
    		END;
    
    		INSERT INTO Table2
    			(param1)
    			OUTPUT inserted.ID
    				INTO @idTable
    			VALUES
    				(@param2);
    		SET @table2ID = (SELECT id FROM @idTable);
    		DELETE FROM @idTable;
    		IF @table2Id IS NULL
    		BEGIN
    			SET @msg = 'Insert Table2 failed';
    			THROW 50004, @msg, 0;
    		END;
    
    		INSERT INTO Table3
    			(param3)
    			OUTPUT inserted.ID
    				INTO @idTable
    			VALUES
    				(@param3);
    		SET @table3ID = (SELECT id FROM @idTable);
    		DELETE FROM @idTable;
    		IF @table3Id IS NULL
    		BEGIN
    			SET @msg = 'Insert Table3 failed';
    			THROW 50004, @msg, 0;
    		END;
    		
    		IF(@@TRANCOUNT > @activeTrans)
    		BEGIN
    			COMMIT TRANSACTION;
    		END;
    	END TRY
    	BEGIN CATCH
    		IF(@@TRANCOUNT > @activeTrans)
    		BEGIN
    			ROLLBACK TRANSACTION;
    		END;
    	END CATCH
    END;

    I don't see any way that this procedure would leave a transaction open.

    What Log4Net has logged on this Exception:

    "System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
       at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
       at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       --- End of inner exception stack trace ---
       at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteNonQuery()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunctionCommand(EntityCommand entityCommand)
       at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass4b.<ExecuteFunction>b__4a()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass4b.<ExecuteFunction>b__49()
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters)"

    I now see that i have read the Exception wrong, it's actually complaining about a transaction beeing closed before it should be... Ooops, sorry about that, i changed the stored procedure accordingly (meaning i introduced the @activeTransactions and test it before commit and rollback) this whole thing still is weird cause i begin transaction explicitly so the outer transaction from EF should be no. 1 and mine no. 2 (the earlier mentioned while loop was only for testing and was not in the actual procedure), and i only commit and rollback once in my stored procedure, so this still should not be the case.

    I am testing this right now, which may take a while since its randomly ocuring


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.





    • Edited by MDeero Monday, March 7, 2016 9:53 AM
    Monday, March 7, 2016 8:41 AM
  • >So you think the error resides within my StoredProceduer? Maybe you can have a look:

    Yep.  Looks like one of your THROW statements or some other error is occurring.  Then in the CATCH block you are calling ROLLBACK TRANSACTION.  This rolls back the whole transaction and then you exit the stored procedure with @@trancount=0.  It's illegal to do that, so you also get the error that you are catching and seeing in your program.

    But that error is not the root cause. 

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, March 7, 2016 5:02 PM
  • lol.... the before posted .net code is not mine ;-)

    i do not have 9 connections, i guess it was just DA924x in the wrong thread.

    So my problem would be solved by applying a name to the transactio and rollback/commit #nameoftrans#?

    mh... i thought the 'ROLLBACK' alone was closing all Transactions and that 'COMMIT/ROLLBACK TRANSACTION' would close the explicitly opened transaction, that was started just before, only

    i will try that, but i guess i can be sure that this is my error...

    THX a bunch

    (This would mean, that transaction control in stored procedures can now (since EF 6 update on ExecuteFunction by wrapping a transaction around it) never work anymore without naming them... cause in that case, maybe i should just get back to EF 5. This seems kinda bogus)

    I will mark the answer after testing, just that of course (murphy's law at work) after i introduced the trace, the partner has stopped testing... so typical ;-)


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.



    • Edited by MDeero Tuesday, March 8, 2016 6:29 AM
    Tuesday, March 8, 2016 6:24 AM
  • "i thought the 'ROLLBACK' alone was closing all Transactions and that 'COMMIT/ROLLBACK TRANSACTION' would close the explicitly opened transaction, that was started just before, only"

    Nope.  It never worked like that.  ROLLBACK TRANSACTION always rolls back the whole transaction.  SQL Server has only partial nested transaction support.

    "This would mean, that transaction control in stored procedures can now (since EF 6 update on ExecuteFunction by wrapping a transaction around it) never work anymore without naming them"

    It works fine.  It's just that error reporting is a bit complicated.  The right thing happened: there was an error, and the transaction rolled back.  You just aren't looking in the right place to see what the original error was.

    And using named transactions doesn't help at all, and using savepoints instead of nested transaction won't work with all errors.

    See http://www.sommarskog.se/error_handling/Part1.html for a full explanation.

    This really has nothing to do with EF, as you will be calling stored procedures in client-scoped transactions in lots of scenarios.  And using a transaction at the client solves more problems than it causes.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, March 8, 2016 1:06 PM
  • >>> It works fine.  It's just that error reporting is a bit complicated.  The right thing happened: there was an error, and the transaction rolled back.  You just aren't looking in the right place to see what the original error was.

    Actually, it is not working fine, there is no error, the data is written, there is no rollback happening (quote from original question: (Forgot: Even though the EF throws an Exception, the data will be present in the Database after wards always)). Thats the thing that is putting me off, since there is no error happening at the (sql) server, it must be the client, and since i just use the EF way of calling StoredProcedures (using the generated code), i still believe that EF is somehow giving the wrong Exception.

    Now i feel like i am back at the beginning: having no clue where to look and how to solve this (except for the SQL-Trace that maybe can help when it finally gets populated with data)


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.


    • Edited by MDeero Tuesday, March 8, 2016 2:04 PM
    Tuesday, March 8, 2016 2:04 PM
  • I think you just need better logging and a repro.

    The only thing I can think of  that would generate that error without rolling back the transaction is if you have too many COMMITs in the stored procedure.  EG this proc will do that:

    create procedure foo
    as
    begin
    
       commit transaction
    end 
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, March 8, 2016 2:42 PM
  • You see my StoredProceedure above... you see there ain't a possibility of too many commits, there is only one, and that (just to try to make it work with EF6 new strategy of wrapping that thing in a transaction) only gets executed when the transaction count is higher than the transaction count was at the time the  StoredProceedure got started.

    (It's BTW total nonesense that EF6 wraps my StoredProcedures inside Transactions, cause those Transactions should never be excplicitly made for EF. Since EF is a ORM, the whole point of using EF is to get an easy to use intrerface for Databases, not to program the Database differently just to get it running via that ORM)

    May I ask you to get a sample project up and running, cause I now think you don't understand the problem I have cause you cannot imagine that there is such an error. I thought the same way and was totally bluffed! That's why I am posting here. My StoredProcedure does not call any other proceedures.

    Regards


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.



    • Edited by MDeero Wednesday, March 9, 2016 7:06 AM
    Wednesday, March 9, 2016 7:02 AM
  • >you see there ain't a possibility of too many commits

    Could be here "EXECUTE [FindSomeId] @param1, @someID OUTPUT; "

    >May I ask you to get a sample project up and running

    I already have.  Twice.

    Do you have a way to reproduce the issue?  The code you posted won't run.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 9, 2016 12:20 PM
  • The Error is (on a local network) not reproduceable... I tried everything now.

    >>> "could be here..." -> no it can't, casue it's only a Select (inside there) and it does not, wouldn't make sense on selects, run any Transaction control statements) (also removed that and created a dummy, nothing changed, random failure over the internet, none over intranet)

    >>> "The code you posted won't run." again: That ain't my code (except the SQL StoredProcedure, which is only an example), that code is from DA924x! I never posted any .NET code in this Thread!

    >>>"Do you have a way to reproduce the issue?" Somewhat: As mentioned in the initial post, this error occurs randomly and only when the Service is called over the internet (on intranet (local Network), it ain't possible to get this Error).

    It seems i really have to revert to the work around and see if the Error still arises then.

    I cannot figure out where this exception is coming from. One would now go over to WCF and the Program at hand to search for the Error, but the thing is that everything works, it is only the Exception getting thrown and caught and then of course returned by my ErrorHandler (to notify about an exception and to set HttpStatus Code 500). If that Exception weren't thrown (or if I could safely assume that the Exception is just BS, then i could filter it out, but that's the stupidest thing to do) the Program would return the success instead.

    I decided to go with the workaround.. since this does not make any sense at all.

    THX again for all the effort, bummer that it cannot work (and it did with EF5 btw)

    Marking helpful answers

    Could you remove the  "and what on earth are you..." since the beginning of that post is useful, the rest just ain't related to my question (but to a post from DA924x?


    Please be so kind to close your Threads when you found an answer, these Threads should help everyone with similar issues.
    You can close a Thread via the"Mark as Answer" link below posts. You can mark your own posts as answers if you were not helped out but found a solution, in such a case, please provide the answer.
    Happy coding
    PS: I assure everyone that I did not ever had the desire to offend anyone.







    • Edited by MDeero Wednesday, March 9, 2016 2:31 PM
    Wednesday, March 9, 2016 12:55 PM
  • @David

    And what on earth are you doing with distributed transaction across 9 separate connections to the same database?

    Look, that code was from EF 3.5 way back in 2008, my first encounter with EF, where one was lucky enough to get EF DB First to work period and it worked like a charm.

    Wednesday, March 9, 2016 3:03 PM
  • >Look, that code was from EF 3.5 way back in 2008

    Ouch.  EF in .NET 3.5 was a real dog.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 9, 2016 6:01 PM
  • >Look, that code was from EF 3.5 way back in 2008

    Ouch.  EF in .NET 3.5 was a real dog.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Yeah it was a dog. I had to remove all the relationships off of the model to even get it to work. I have to put all child objects on the model first before I could put the parent on the model to expose the foreign properties in the child objects. And I had to do this about 3 or 4 times as EF did a turkey on the model with 90 objects on the model. Basically, EF for me at the time became an object code generator using Linq. :)
    Wednesday, March 9, 2016 8:27 PM