none
DataContext SubmitChanges is firing for each updated row RRS feed

  • Question

  • Hello All,

    I am came across an issue, where datacontext submitchanges method is hitting DB for each updated row, which is leading to huge performance degradation. Is there a way, I can reduce DB calls for each row?


    Regards, http://www.shwetalodha.blogspot.in/

    Sunday, August 25, 2013 8:57 AM

Answers

  • Hi Shweta,

    As I know, there is no way to force Entity to perform batch updates. What we could do is to vote on our UserVoice site to ask for functionality in a future release.

    #UserVoice

    http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions

    Also I’d like to provide two workarounds to get around this issue. Check this article below for some idea.

    #Entity Framework Bulk Copy

    http://cgeers.com/2011/05/19/entity-framework-bulk-copy/

    Another workaround could be using stored procedure accepting table valued parameter but you need raw ADO.NET for that.

    Best Regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, August 28, 2013 9:44 AM
    Moderator
  • You would need to be careful in using a batch situation.  One of the things that Linq is good at is the situation where I am storing rows with items that are foreign keys related.  I can basically create the "owner" and then the "children" items as long as the FK is known in the datacontext.  It will then insert the owner, pass back the foreign key (if it is generated at the db) so that all children will have that key for their inserts.

    A batch scenario would fail with this.

     

    Lloyd Sheen

    Wednesday, August 28, 2013 5:57 PM

All replies

  • If you think about what Linq is, it is really just a translator of dot.net language into T-SQL (if you are using SQL Server).

    Therefore for each row in the database that you update using Linq, you are in fact generating a T-SQL Update statement for each.

    Linq is not very good for lets say the scenario where you are doing a select of a set of rows and then updating a column for each.  Again it will generate one T-SQL statement for each row.  If you wanted to do this sending an update with a where clause is much more efficient.

    The short of it is that there is no way around it.


    Lloyd Sheen

    Sunday, August 25, 2013 6:19 PM
  • Hi Lloyd,

    I understand your point. I want to know, is there any way, in which I can perform batch updates ?


    Regards, http://www.shwetalodha.blogspot.in/


    Monday, August 26, 2013 7:28 AM
  • Hi Shweta,

    As I know, there is no way to force Entity to perform batch updates. What we could do is to vote on our UserVoice site to ask for functionality in a future release.

    #UserVoice

    http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions

    Also I’d like to provide two workarounds to get around this issue. Check this article below for some idea.

    #Entity Framework Bulk Copy

    http://cgeers.com/2011/05/19/entity-framework-bulk-copy/

    Another workaround could be using stored procedure accepting table valued parameter but you need raw ADO.NET for that.

    Best Regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, August 28, 2013 9:44 AM
    Moderator
  • You would need to be careful in using a batch situation.  One of the things that Linq is good at is the situation where I am storing rows with items that are foreign keys related.  I can basically create the "owner" and then the "children" items as long as the FK is known in the datacontext.  It will then insert the owner, pass back the foreign key (if it is generated at the db) so that all children will have that key for their inserts.

    A batch scenario would fail with this.

     

    Lloyd Sheen

    Wednesday, August 28, 2013 5:57 PM