locked
EntityFramework problem with SQL TRIGGER RRS feed

  • Question

  • I've seen some questions regarding EntityFramework and Triggers but i don't seem to find a decent solution.

    Software:
    - Windows 7 Professional (32-bit);
    - SQL SERVER Enterprise 2008 R2;
    - Visual Studio 2010 Premium (im using Entity Framework 4.0 - LINQ - to manage the Data Access);

    Problem: I have an Entity in my system called Notification. A Notification has a List<> fo Details. In SQL this represents a 2 tables: tableNotification and tableDetails. After a Notification has terminated (Notifications have States assiociated that manage this), there's a trigger that validates its been terminated and deletes the Details and Notification (in this order).
    What my code does is call a Method called TerminateTotalNotification(). In my code the only thing i do in LINQ is alter the Notification State. When i hit SaveChanges(), BOOOOM!

    Is there a way to delay the execution of the database Trigger to wait for the end of SaveChanges() ?????

    Hope this i was clear on the problem. Any feedback is welcome.

    Thanks in advance,
    SuperJB

    Friday, August 20, 2010 5:03 PM

Answers

  • Unfortunately, no; triggers run on the database, independent of the application.

    But, I would advise you to step through your code and determine the exact point where the trigger is fired and seek a remedy from there.

    Is it really necessary to have the SaveChanges in the two methods? Can you workaround it?

    As a last resort, you may consider moving your trigger code into a stored procedure which you invoke from your EF code.

    Saturday, August 21, 2010 8:22 PM
  • OK, so i think i finally found away to resolve this situation. 
    I changed my code so the method is executed within a scope of a DbTransaction:

     

    ...
    ...

    MyApplicationEntities _myApplicationConn = DataAccess.GetMyApplicationConnection();
    DbTransaction _dbTransaction = null;
    using (_dbTransaction = _myApplicationConn.Connection.BeginTransaction())
    {
         try
         {
              ...
              ...     SaveChanges();
              ...
              ...     SaveChanegs();
              ...
              AcceptAllChanges();
              _dbTransaction.Commit();
         }

         catch (TransactionAbortedException ex)

         {

               Console.WriteLine("Transaction

    Aborted Exception: {0}", ex.ToString());

               return false;

         }

         catch(Excepetion ex)

         {

               Console.WriteLine("Exception: {0}",  ex.ToString());

               return false;

         }

    }

     

     

     

     

     

     

     

     

     

     


    I've tried a couple of tests and the trigger seems to fire without a problem after the scope is terminated. I was going to try the StoredProcedure approach but it just didn't seem "right". There had to be a way that .NET Transactions where capable with SQL Triggers / Transactions.
    Thanks anyway for the help


    SuperJB

    Monday, August 23, 2010 10:56 AM
  • To work with Stored Procedure in EntityFramework, you  could take a look at following marterials:

    How to: define a model with stored procedure:
    http://msdn.microsoft.com/en-us/library/bb896279.aspx

    video: Stored Procedures in Entity Framework:
    http://msdn.microsoft.com/en-us/vcsharp/dd565862.aspx

    Hope it could help.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Tuesday, August 24, 2010 10:40 AM

All replies

  • Yes, amend your trigger to AFTER UPDATE.
    Friday, August 20, 2010 5:17 PM
  • Thanks for the response but my trigger is already "AFTER UPDATE"

    I tried to be clear about my problem but i forget to mention some things:
    - To finalize a "Notification" i call a certain method;
    - In this method i start a Transaction associated with my EntityConnection;
    - during the transaction, i call 2 other methods in wich i have a SaveChanges();
    - only after i go through these to methods do i have an AcceptAllChanges() and Transaction is commited;

    - The main problem is that my Trigger is fired during the Transaction, before AcceptAllChanges is hit.

    Does anyone know of a way to "tell" Entity Framework the execution of triggers?

     

    Thanks again,
    SuperJB

    Saturday, August 21, 2010 11:10 AM
  • Unfortunately, no; triggers run on the database, independent of the application.

    But, I would advise you to step through your code and determine the exact point where the trigger is fired and seek a remedy from there.

    Is it really necessary to have the SaveChanges in the two methods? Can you workaround it?

    As a last resort, you may consider moving your trigger code into a stored procedure which you invoke from your EF code.

    Saturday, August 21, 2010 8:22 PM
  • OK, so i think i finally found away to resolve this situation. 
    I changed my code so the method is executed within a scope of a DbTransaction:

     

    ...
    ...

    MyApplicationEntities _myApplicationConn = DataAccess.GetMyApplicationConnection();
    DbTransaction _dbTransaction = null;
    using (_dbTransaction = _myApplicationConn.Connection.BeginTransaction())
    {
         try
         {
              ...
              ...     SaveChanges();
              ...
              ...     SaveChanegs();
              ...
              AcceptAllChanges();
              _dbTransaction.Commit();
         }

         catch (TransactionAbortedException ex)

         {

               Console.WriteLine("Transaction

    Aborted Exception: {0}", ex.ToString());

               return false;

         }

         catch(Excepetion ex)

         {

               Console.WriteLine("Exception: {0}",  ex.ToString());

               return false;

         }

    }

     

     

     

     

     

     

     

     

     

     


    I've tried a couple of tests and the trigger seems to fire without a problem after the scope is terminated. I was going to try the StoredProcedure approach but it just didn't seem "right". There had to be a way that .NET Transactions where capable with SQL Triggers / Transactions.
    Thanks anyway for the help


    SuperJB

    Monday, August 23, 2010 10:56 AM
  • To work with Stored Procedure in EntityFramework, you  could take a look at following marterials:

    How to: define a model with stored procedure:
    http://msdn.microsoft.com/en-us/library/bb896279.aspx

    video: Stored Procedures in Entity Framework:
    http://msdn.microsoft.com/en-us/vcsharp/dd565862.aspx

    Hope it could help.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Tuesday, August 24, 2010 10:40 AM
  • I am glad for you.
    Wednesday, August 25, 2010 3:18 PM
  • I am updating my single object of inventory model in database and after updation of inventory record update trigger should execute but its not working and throw error below.

    _context.Update(inventory).State = EntityState.Modified;

    _context.SaveChanges();


    DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 1178 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

     Can anyone explain wrong happening with me?

    Tuesday, July 3, 2018 2:37 PM
  • I am updating my single object of inventory model in database and after updation of inventory record update trigger should execute but its not working and throw error below.

    _context.Update(inventory).State = EntityState.Modified;

    _context.SaveChanges();


    DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 1178 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

     Can anyone explain wrong happening with me?

    The object being saved is not in a modified state, and what you are trying to do is an update. But for whatever reason, like the record by its primary-key property that is specified in the object the record cannot be found,   or the object's state is not really in a modified state at the time of the save.

    So EF is telling you that you tried to update a record,  no record was updated, which should have affected 1 record if the update was successful. . But 0 records were affected and EF expected 1 or more records to be affected and  that didn't happen. Zero records were affected,  EF blew-up and is telling you that no record was updated/affected, which is an indication to you that something is wrong and it didn't happen as you expected.

    Wednesday, July 4, 2018 12:09 AM