none
Stored procedures and transactions in EF6 RRS feed

  • General discussion

  • Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. There are overloads of this method that allow you to override this behavior if you wish. Also in EF6 execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden).

    And whose bright idea was that for gawds sake??? If I wanted the procedure to be a transaction, I'd include BEGIN TRANSACTION, COMMIT and ROLLBACK inside!

    Why does it matter?

    Because this works:

    foreach (var row in db.Articles.Select(a => new { a.Id, a.Title, a.Synopsis }).Take(100)) {
    	string authors = db.ArticleAuthors.Where(aa => aa.ArticleId == row.Id).Select(aa => aa.Creator.FullName).AsEnumerable().Join(", ");
    	string blah = "{0}-{1}".FillIn(row.Title, authors);
    	sb.AppendLine(blah);
    }

    and this

    foreach (var row in db.Articles.Select(a => new { a.Id, a.Title, a.Synopsis }).Take(100)) {
    	string authors = db.GetArticleAuthorList(row.Id);
    	string blah = "{0}-{1}".FillIn(row.Title, authors);
    	sb.AppendLine(blah);
    }

    throws System.Data.SqlClient.SqlException "New transaction is not allowed because there are other threads running in the session.". And it did not before.

    Any way to make this work again without downloading the sources of EF, changing them and using the modified version instead? I have a heavily extended code generation template using ObjectContext and there's no way I'm gonna loose everything and go to DbContext.

    Thanks, Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#


    • Edited by JendaPerl Saturday, March 1, 2014 11:12 AM
    • Changed type Fred BaoModerator Wednesday, March 26, 2014 1:45 AM The discussion type is more suitable for this case
    Saturday, March 1, 2014 11:03 AM

All replies

  • Any way to make this work again without downloading the sources of EF, changing them and using the modified version instead? I have a heavily extended code generation template using ObjectContext and there's no way I'm gonna loose everything and go to DbContext.

    LOL! I was told that using DbContext was the preferred way to go with EF 6. Yeah I tried to get some code to work with ObjectContext for using Linq Compiled Query that only uses ObjectContext. I did a cast from DBContext over to ObjectContext and it didn't work. It couldn't even be compiled. Also using EntityConnection to explicitly open and close a connection with this EF provider junk for queries no longer work. So I just went with the Dbcontext and moved on, because otherwise, it would take too much time and effort on a short duration project to get EF 6 to work with ObjectContext. I am not happy with this monkey-wrench that has been tossed into EF 6 along with some other things, but it is what it is.   

    Sunday, March 2, 2014 4:59 PM
  • Hello,

    Is the “GetArticleAuthorList” a store procedure?

    Making a simple demo which is similar with yours and work fine.

    static void Main(string[] args)
    
            {
    
                StringBuilder sb = new StringBuilder();
    
                DFDBEntities db = new DFDBEntities();
    
                foreach (Order o in db.Orders.Take(100))
    
                {
    
                    string value = db.ProGetOrderName(o.OrderID).FirstOrDefault();
    
                    string text = string.Format("{0}--{1}", o.OrderID, value);
    
                    sb.AppendLine(text);
    
                }
    
                #endregion
    
            }
    

    The SP:

    CREATE PROCEDURE [dbo].[ProGetOrderName]
    
                    @OrderID int
    
    AS
    
                    SELECT OrderName from [Order] where [Order].OrderID = @OrderID
    
    RETURN 0
    

    I use VS2013, .Net Framework 4.5 and Entity Framework 6 with Database First Model, ObjectContext. Could you please share yours and some more inforamtion? I guess it may has nothing with the store produce.

    You can have a try.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 3, 2014 8:45 AM
    Moderator
  • It's a stored procedure defined like this

    CREATE PROCEDURE [dbo].[GetArticleAuthorList](
    	@ArticleId int,
    	@AuthorList nvarchar(4000) OUTPUT
    ) AS
    BEGIN
    	SET @AuthorList = ''
    	
    	SELECT @AuthorList = @AuthorList + C.FirstName + ' ' + C.LastName + ', '
    	  FROM dbo.Creators as C
    	  JOIN dbo.ArticleCreators as AC ON AC.CreatorId = c.Id
    	  JOIN dbo.CreatorTypes as CT ON CT.Id = AC.CreatorTypeId
    	 WHERE CT.Name = 'Autor'
    	   and AC.ArticleId = @ArticleId
    	 ORDER BY AC.OrderNo ASC, C.FullName ASC
    	 
    	 IF (@AuthorList <> '')
    		SET @AuthorList = SUBSTRING(@AuthorList,1,len(@AuthorList)-1)
    END
    

    and the generated method is 

    public global::System.String GetArticleAuthorList(global::System.Int32 articleId)
     {
    	global::System.String authorList;
    	ObjectParameter articleIdParameter;
    	articleIdParameter = new ObjectParameter("ArticleId", articleId);
    
    	ObjectParameter authorListParameter;
    	authorListParameter = new ObjectParameter("AuthorList", typeof(global::System.String));
    
    int _resul_t;
    
    try {
    	
    	_resul_t = base.ExecuteFunction("GetArticleAuthorList", new ExecutionOptions(MergeOption.NoTracking,true), articleIdParameter, authorListParameter);
    	authorList = Convert.IsDBNull(authorListParameter.Value) ? default(global::System.String) : (global::System.String)authorListParameter.Value;
    	return authorList;
    }
    	
    	finally {
    		
    	}
    }
    

    I tried to select the whole entity ... did not make a difference, still throws "New transaction is not allowed because there are other threads running in the session."

    I tried it with a resultset returning procedure like this

    GO
    
    CREATE PROCEDURE dbo.FetchArticleAutorsOrdered(@ArticleId int)
    AS
    BEGIN
    	SELECT CreatorId, CASE WHEN (C.[FirstName] = '' OR C.[FirstName] IS NULL) THEN C.[LastName] ELSE C.[FirstName] + ' ' + C.[LastName] END AS FullName
          FROM [dbo].[ArticleAuthors] as AA
          JOIN [dbo].Creators as C ON C.Id = AA.CreatorId
         WHERE AA.ArticleId = @ArticleId
         ORDER BY  AA.[OrderNo] ASC, C.[LastName] ASC, C.FirstName
    END
    
    public IEnumerable<CreatorsFullname> FetchArticleAutorsOrdered(global::System.Int32 articleId)
     {
    	ObjectParameter articleIdParameter;
    	articleIdParameter = new ObjectParameter("ArticleId", articleId);
    
    IEnumerable<CreatorsFullname> _resul_t;
    
    try {
    	
    	_resul_t = base.ExecuteFunction<CreatorsFullname>("FetchArticleAutorsOrdered", articleIdParameter).WatchProcedureResults();
    
    	return _resul_t;
    }
    	
    	finally {
    		
    	}
    }
    


    foreach (var row in db.Articles//.Select(a => new { a.Id, a.Title, a.Synopsis })
    	.Take(100)) {
    	string authors = db.FetchArticleAutorsOrdered(row.Id).Select(a => a.FullName).AsEnumerable().Join(", ");
    
    	string blah = "{0}-{1}".FillIn(row.Title, authors);
    	sb.AppendLine(blah);
    }
    

    and that works. So it seems the 

    public ObjectResult<TElement> ExecuteFunction<TElement>(string functionName, params ObjectParameter[] parameters);

    does something different than the

    public virtual int ExecuteFunction(string functionName, params ObjectParameter[] parameters);

    Gotta have to have a look inside EF6 to find out what and if there's any way around that. Not sure what yet since there's an ExecuteInTransaction() call in both. 

    Thanks, Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Monday, March 3, 2014 9:35 AM
  • OK, so the difference is that EF6 wraps stored procedures returning resultsets in a transaction only if the active ExecutionStrategy retries execution and the default one does not. The stored procedures that do not return a resultset are wrapped in the bogus and annoying transaction always.

    Apparently the authors thought that if it doesn't return a resultset, it must be modifying the data. Seems I'm gonna have to fork :-(


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Monday, March 3, 2014 11:15 AM
  • As I do not plan to use a different ExecutionStrategy and do not want to make too many changes to the original, I'll probably just change the "startLocalTransaction: true" to "startLocalTransaction: false" on line 3626 of ObjectContext.cs, but it seems to me that the best solution would be to extend the ExecutionOptions to contain a property that'd allow me to specify whether to wrap the procedure in transaction Never, IfExecutionStrategyAllowsRetries or Always and add an overload of int ExecuteFuction(...) that'd accept the ExecutionOptions. Most nullipotent and idempotent procedures do not need to be wrapped in a transaction.

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Monday, March 3, 2014 11:49 AM
  • Apparently the authors thought that if it doesn't return a resultset, it must be modifying the data. Seems I'm gonna have to fork :-(

    http://www.c-sharpcorner.com/UploadFile/ff2f08/prevent-dead-lock-in-entity-framework/

    I use transactions on queries all the time in multi user applications that use EF no matter what version it is or what approach of database or model first -- etc. 

    I can see why the queries would be wrapped in a transaction for the developer. Also I do transactions within transactions, because if I have to do another read within a transaction that is doing a read, the other read is opening its one connection in another  method that has been called that goes into a transaction to separate itself from the method that made the call that was already in a transaction.  It works like a charm.

    Monday, March 3, 2014 11:58 AM
  • I use transactions ... where they are needed. Blindly wrapping a local transaction around any and all stored procedure calls on the other hand is silly.

    In either case it seems that having a custom build of EF is more pain than I expected :-(

    It's possible to wrap the whole loop in a transaction which prevents the attempt to introduce a transaction as the query results are being enumerated, which seemed to be a solution (the loop is a heavily simplified example), but while I can wrap my grids in an extra transaction allowing me to call GetSomething style procedures while rendering the grid, there are cases in which this doesn't work.

    So many problems for such a simple, ill-thought-through change. Thank you!

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Monday, March 3, 2014 3:19 PM
  • So many problems for such a simple, ill-thought-through change. Thank you!

    I am not happy with EF 6 and what they have done with forcing the usage of Dbcontext. Now, you can't even delete an entity from the DB using Model First as easily as it was done with ObjectContext. I can't find any documentation on how to do a delete in EF 6 using Model first and a DBcontext. It should be simple and it's not. I am afraid I may have to resort to calling a sproc with straight up ADO.NET to do the delete of a record by its ID from the table.  :(

    Monday, March 3, 2014 6:27 PM
  • OK, so I guess this is the best solution considering my situation. I added this into the object context:

    private KosmasEntities _secondaryContext;
    private int BaseExecuteFunction(string functionName, params ObjectParameter[] parameters) {
    	return base.ExecuteFunction(functionName, parameters);
    }
    public override int ExecuteFunction(string functionName, params ObjectParameter[] parameters) {
    	try {
    		return base.ExecuteFunction(functionName, parameters);
    	} catch (System.Data.Entity.Core.EntityException ex) {
    		if (ex.HResult == -2146233087 && ex.InnerException is System.Data.SqlClient.SqlException && ((System.Data.SqlClient.SqlException)ex.InnerException).ErrorCode == -2146232060) {
    			if (_secondaryContext == null) _secondaryContext = new KosmasEntities();
    			return _secondaryContext.BaseExecuteFunction(functionName, parameters);
    		}
    		throw;
    	}
    }
    

    (Of course you'd use the name of your class in place of the KosmasEntities.)

    Not nice, but works :-(


    http://jendaperl.blogspot.com<br/> A Perl developer in the world of C#

    Tuesday, March 4, 2014 10:58 AM
  • Or

    foreach (var row in db.Articles.Select(a => new { a.Id, a.Title, a.Synopsis }).Take(100).ToList()) { ... }

    You just can't start a new transaction while reading results from an open cursor.

    David


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

    Tuesday, March 4, 2014 3:54 PM
  • The whole point is that I do not want to start any transaction at all. Never did. I use transactions inside stored procedures where they make sense, but that's it. Unless strictly necessary transactions should stay inside the database server, inside a single query. Once the transaction leaves the database, you run a much higher risk of queries waiting for each other and deadlocks. It makes sense to enclose the execution of the statements generated by SaveChanges in a transaction. It makes sense to enclose the non-idempotent stored procedures in transactions in case the ExecutionStrategy supports retries, but all other transactions should only ever be used if and only if the developer explicitely asks for them. Fullstop.

    In either case the project is way too big to have a reasonable chance of finding all cases when this breaking change can break things so handling the exception is the only viable solution.

    Jenda


    http://jendaperl.blogspot.com<br/> A Perl developer in the world of C#

    Tuesday, March 4, 2014 4:14 PM
  • That's good feedback.  The best place for EF feedback is on Codeplex.

    http://entityframework.codeplex.com/discussions

    David


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

    Tuesday, March 4, 2014 5:04 PM