locked
entity framework 6.0 transaction issue RRS feed

  • Question

  • Hi

    Got an issue when using sqlcommand in entity framework 6.0 in our web application. As an default setting, it will add a transaction to a sql command, however, when there are multiple users,  it threw this error : New transaction is not allowed because there are other threads running in the session. 
    Should we disable the default setting? or other solution?

    Thank you
    • Moved by CoolDadTx Tuesday, March 13, 2018 1:47 PM EF related
    Tuesday, March 13, 2018 1:33 PM

All replies

  • What kind of architecture design is EF being used in? N-tier, Web program, desktop program or what? 
    Tuesday, March 13, 2018 2:25 PM
  • It is intranet web program, EF works fine except this, we use DbContext.Database.ExecuteSqlCommand() to call a store procedure (there is no transaction in the store procedure itself), and found that this error was thrown when several users call this function together.
    Tuesday, March 13, 2018 2:58 PM
  • Myself personally, I use the EF backdoor to call a stored procedure and take EF out of the picture. I mean the sproc has to be so complex that functionality cannot be achieved by using Linq and EF as an ORM. Or I start using Entity SQL, which only has read abilities. 

    When using the EF backdoor, I am using the EF connection, but at that point, I am using ADO.NET, SQL Command objects, etc. and ect. in the traditional sense,  and EF is not involved. But I am able to use the objects on the virtual model or just use the DTO pattern, when using the backdoor.

    Tuesday, March 13, 2018 3:32 PM
  • Thank you, 

    Using EF connection and ADO.NET is OK. Just want to know, why default setting of ExecuteSqlCommand is using transaction, there is concurrency issue, Is this issue from entity framework or from others.

    Tuesday, March 13, 2018 5:24 PM
  • Like I said, stop using EF to run the sproc, go to the EF backdoor and run the sproc using straight-up ADO.NET and SQL Command objects in the traditional sense not involving EF. 

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

    It works in EF 6 with the IObjectContextAdapter.

    Tuesday, March 13, 2018 6:52 PM
  • Hi ni.chris,

    Prior to EF6 Entity Framework insisted on opening the database connection itself (it threw an exception if it was passed a connection that was already open). Since a transaction can only be started on an open connection, this meant that the only way a user could wrap several operations into one transaction was either to use a TransactionScope or use the ObjectContext.Connection property and start calling Open() and BeginTransaction() directly on the returned EntityConnection object. In addition, API calls which contacted the database would fail if you had started a transaction on the underlying database connection on your own.

    For more information, please refer to:

    https://msdn.microsoft.com/en-gb/data/dn456843.aspx?f=255&MSPPError=-2147217396

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 14, 2018 7:33 AM