none
Entity framework stored procedure issues with entity framework 6 RRS feed

  • Question

  • {System.Data.Entity.Infrastructure.CommitFailedException: An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information. ---> System.Data.SqlClient.SqlException: The transaction operation cannot be performed because there are pending requests working on this transaction.

    my code which is working fine in Entity framework 5 but not in 6

    ((IObjectContextAdapter)SoInDBContext.Instance).ObjectContext.ExecuteFunction("SomeFunction", idParameter, fbIdParameter, emailParameter);

    I am not using any transaction in the SP.

    Thursday, July 24, 2014 12:57 PM

All replies

  • Hello,

    Would you mind share your stored procedure with us? From your description, it seems that you are using DbContext. Do you create a global DbContext? If it is, I do not suggest you to do this, creating a global DbContext in an application is not a good idea since the DbContext class is not thread-safe. Trying to use the Using block and should create the dbcontext every time you want to use it

    Since I do not have your exact project environment, I just make a small demo to test the code provided by you while it could work successfully whenever using EF 5 or EF 6 as:

    ObjectParameter OrderID = new ObjectParameter("OrderID", 1);
    
    var result = ((IObjectContextAdapter)db).ObjectContext.ExecuteFunction<GetOrder_Result>("GetOrder", OrderID);
    

    And you could also refer to this thread which discusses the similar issue:

    http://stackoverflow.com/questions/3266295/net-entity-framework-and-transactions

    If I misunderstand, please let me know.

    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.

    Friday, July 25, 2014 1:55 AM
    Moderator
  • Hi,

    Thanks alot for your reply... For every request i create a new context. I found out that in Entity framework 6 by default it uses transactions for every crud operations https://entityframework.codeplex.com/workitem/1612 and in execute function there is no way to disable it, only possible with executeCommand. 

    I agree with your suggestion of using statement. But how using statement will fit in this below example can you plz suggest. Stored procedure is a plan insert statement

    Below is my code to create a context

    public static SoInDBContext Instance
            {
                get
                {
                    if (instance == null)
                        instance = new SoInDBContext();

                    try
                    {
                        instance.SaveChanges();
                    }

                   
                    catch (Exception)
                    {
                       
                    }
                    finally
                    {
                        if (instance == null)
                            instance = new SoInDBContext();
                    }


                    return instance;
                }
            }

    And below is my code to call a stored procedure through function import in EF

     public static bool CreateUpdateUserProfile(SoIn.Entities.UserProfile userProfile)
            {
                bool result = false;
                try
                {
                    var idParameter = userProfile.Id != null ?
                        new ObjectParameter("Id", userProfile.Id) :
                        new ObjectParameter("Id", typeof(System.Guid));

                  var firstNameParameter = userProfile.FirstName != null ?
                        new ObjectParameter("FirstName", userProfile.FirstName) :
                        new ObjectParameter("FirstName", typeof(string));

                    var lastNameParameter = userProfile.LastName != null ?
                        new ObjectParameter("LastName", userProfile.LastName) :
                        new ObjectParameter("LastName", typeof(string));

                    var imageURLParameter = userProfile.ImageURL != null ?
                        new ObjectParameter("ImageURL", userProfile.ImageURL) :
                        new ObjectParameter("ImageURL", typeof(string));

    ((IObjectContextAdapter)SoInDBContext.Instance).ObjectContext.ExecuteFunction("CreateUpdateUserProfile", idParameter, firstNameParameter, lastNameParameter, imageURLParameter);

           result = true;
                }
                catch (Exception ex)

    {

            }

    }

    Friday, July 25, 2014 10:27 AM
  • For every request i create a new context

    Well, that's going to be a problem for you if you create a context and within that context you open a new context. That's viewed as a distributed transaction and it can't be done unless the machine hosting SQL Server is using MSDTC.

    Friday, July 25, 2014 1:14 PM
  • i am using SQL Azure
    Friday, July 25, 2014 1:17 PM
  • i am using SQL Azure

    How do you know the machine hosting SQL Azure is using MSDTC. If this is a Web application hosted on Azure, then how do you know the Web server has MSDTC enabled? Both machines have to have MSDTC enabled?

    And are you opening context within a context?

    Friday, July 25, 2014 2:48 PM