PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Proper way to save changes in an entity loop-VBForums
Results 1 to 5 of 5

Thread: Proper way to save changes in an entity loop

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    692

    Proper way to save changes in an entity loop

    vb.net Code:
    1. Using objContext As New CustomerEntities
    2.     Dim objAccounts = From A In objContext.Accounts Where A.JDX = 1
    3.     For Each objAccount As Account In objAccounts
    4.         objAccount.OwnerState = "TN"
    5.         objContext.SaveChanges()
    6.     Next
    7. End Using
    I made my first attempt like the above example and it failed because I want to update in the loop. That makes sense. I found a message where someone said in order to do what I want, I must create a List(Of T) and do the .ToList method. Then iterate though that list.
    vb.net Code:
    1. Using objContext As New CustomerEntities
    2.     Dim objAccounts As List(Of Account) = (From A In objContext.Accounts Where A.JDX = 1).ToList
    3.     For Each objAccount As Account In objAccounts
    4.         objAccount.OwnerState = "TN"
    5.         objContext.SaveChanges()
    6.     Next
    7. End Using
    This works but is this the proper method?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Proper way to save changes in an entity loop

    What actually happened when you executed the first code snippet? My guess is that it had an issue with modifying data during enumeration of the query results, but that's only a guess.

    The usual thing to do in that circumstance is to call ToList to execute the query and get all the data, then make all the changes, then save all the changes in a single batch. I have never wanted to save multiple changes like that one by one, so I've never found myself having to make this decision.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    692

    Re: Proper way to save changes in an entity loop

    As usual, thanks for the input. The first one gave me an exception. "New transaction is not allowed because there are other threads running in the session." I think you're right. Or something to that effect. It made sense to me. "Hey! I'm using the phone so you can't use the phone to make another phone call until I'm done."

    So if you did need to save each one, one at a time, you would subscribe to this method?

    I'm torn. This is a slow process and prone to failure. If I don't do them one at a time I'll need to make sure I handle all possible exceptions and make sure that each exit is covered by a Save. I also only need 2 columns. I might just create my own object to contain just those and then update by ID. Hmmm

    Anyway, thanks for your input. I appreciate it.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Proper way to save changes in an entity loop

    LINQ to Entities presumably uses a data reader under the hood so your first code snippet is akin to something like this:
    vb.net Code:
    1. Using reader = selectCommand.ExecuteReader()
    2.     While reader.Read()
    3.         updateCommand.Parameters("ID") = reader("ID")
    4.         updateCommand.Parameters("OwnerState") = "IN"
    5.         updateCommand.ExecuteNonQuery()
    6.     End While
    7. End Using
    EF presumably explicitly prevents you from committing changes back to the database while the result set of the query is being read. By calling ToList, as you are in your second code snippet, you are reading the entire result set of the query first, before saving any changes. Surmising the inner workings from the behaviour, I think that it's safe to say that yes, that second option is definitely the way to go if you want to save changes one-by-one.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    692

    Re: Proper way to save changes in an entity loop

    You rock. Thanks buddy.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width