none
How to clear the internal context cache after doing direct SQL? RRS feed

  • Question

  • Hi Guys,

    Due to the fact that Entity Framework does not have efficient batch delete operations (or update and insert), I have resorted to implementing some important functions using direct SQL rather than loading all the entities from the database in order to delete them (or update them if I just need to quick change on field on lots of objects at once). The problem of course is that due to the Identity Map managed by the Entity Framework internally, once I issue those commands on my context the cached objects now represent stale data and won't be accurate if I do something else that might load the same objects.

    I have built an implementation for our web site such that the DbContext instance is created once per request, and re-used throughout the request. This is much more efficient than creating it and destroying it all the time and hiding it behind a generic 'repository', because then my code can take advantage of the Identity Map and if code further down the pipe requests the same object, it comes from the identify map and not necessarily from the database (can avoid extra DB select calls). But the downside of course is that when I do direct SQL, I now have stale data.

    What I would like to be able to do is simply tell the DbContext when I am done issuing my calls to somehow flush the Identity Map so that future calls will hit the database to get fresh data, but I can't work out if that is even possible?

    I could toss the context that I am using and create a new one, which would get an all new identity map, but the problem is that the function that does the delete could be called by some other code that might be using the same object context and have a reference to it already, so I cannot simply dispose of it. If I just de-reference it and not dispose of it, it will still work for the old code, but then it won't get disposed until it is garbage collected, and I can potentially end up with issues where objects loaded from the old context try to be used with the new context (not sure what would happen there).

    Of course I could just change my delete code and update code to NOT use direct SQL, but then I run into the original performance problem I had before, which I am trying to avoid.

    Any suggestions?

    Friday, July 8, 2011 7:54 PM

All replies

  • Hi Kendall,

    I think you can try to use "Reload()" method which can be used to overwrite the current values of the entity with the values now in the database.

    http://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbentityentry.reload(v=vs.103).aspx

    http://blogs.msdn.com/b/adonet/archive/2011/02/03/using-dbcontext-in-ef-feature-ctp5-part-9-optimistic-concurrency-patterns.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 11, 2011 8:03 AM
    Moderator
  • That won't work because I have already deleted the original entity, along with all related entities. I am thinking perhaps the best solution is to tell the Entity Framework that Cascading Deletes will be performed so it will do the Cascading Delete on the related entities that are loaded in memory, and my direct SQL code will take care of any related entities not currently loaded.
    Monday, July 11, 2011 9:21 PM
  • Your idea is good. I know a way to let database to tell client what has changed. CacheDepandency, http://msdn.microsoft.com/en-us/library/system.web.caching.cachedependency.aspx , but we need to do a lot of work to make it works.

    There is a GetDatabaseValues() method to get database values, if the instance is not exsited in database, it will throw an Exception, we can catch it, but the performance is bad, we need to loop all entities.


    I am fish.
    Wednesday, July 13, 2011 1:09 PM
  • Hi Kendall,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 5:36 AM
    Moderator
  • I never found a solution to this problem and gave up. I am now porting all our code to Telerik OpenAccess, because this was a blocker for me (not to mention the horrible performance of Entity Framework we ran into).
    Tuesday, July 19, 2011 6:31 AM
  • Hi Kendall,

    I will consult the Product Team about this problem and I'll let you know the feedback as soon as possible. Thanks for understanding.

    Have a nice day


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 19, 2011 3:26 AM
    Moderator
  • Well I have given up on ORM's completely. OpenAccess, although a TON faster than EF, still has a bunch of issues. I have tried Mindscape Lightspeed (the fastest ORM I have tried to date), but they are all too slow and add a ton of overhead. I ended up porting out code to use a mini-ORM in the form of PetaPoco, which is super fast and does just what I need without the bloated layers a full blown ORM brings that will simply kill your web site (literally, it killed our site when we deployed just the warehousing code using Entity Framework because there was so much overhead).
    Friday, August 19, 2011 5:38 PM