EF6, injected dbContext caching issue

Question

I have an EF ie EF6 project, in which I inject my DBContext and I have few LinQ query on it as shown.. GetUnSentMessages() brings cached data back, as when I change value in the database, it does not reflect in my query result.. Could you please suggest, where am I going wrong and what's the solution.. Thank you

update - GetUnSentMessages() picks up unsent messages.. UpdateMessageStatus() - is called after the unsent messages have been sent.

So if I go back and change any value for a record in the SMSMessage table, its not picked up by GetUnsentMessages query..It still bring back the old data..ie its caching things..

namespace SMSSender.Repositories
{
    public class SenderRepository : ISenderRepository
    {
        private SMSEntities entities;
        private ILog logger;

        public SenderRepository(SMSEntities entities, ILog logger)
        {
            this.entities = entities;
            this.logger = logger;
        }

        public IEnumerable<SMSMessage> GetUnSentMessages()
        {
            return entities.SMSMessage.Where(item => item.TimeSent == null && item.Deleted == 0 && item.StatusID == 0).ToList();
        }

        public void UpdateMessageStatus(int messageId, string mobileNo, short status)
        {
            var message = entities.SMSMessage.Where(item => item.MessageID == messageId && item.MobileNo == mobileNo).FirstOrDefault();
            if (message != null)
            {
                message.StatusID = status;
                message.TimeSent = DateTime.Now;
                entities.Entry(message).State = System.Data.Entity.EntityState.Modified;
                entities.SaveChanges();
            }
        }
    }
}

update 2 - Changed my code to this after @Jaroslav Surala suggested article and it works fine, not sure if its the best/right approach -

namespace SMSSender.Repositories
{
    public class SenderRepository : ISenderRepository
    {
        private SMSEntities entities;
        private ILog logger;

        public SenderRepository(SMSEntities entities, ILog logger)
        {
            this.entities = entities;
            this.logger = logger;
        }

        public IEnumerable<SMSMessage> GetUnSentMessages()
        {
            return entities.SMSMessage.AsNoTracking().Where(item => item.TimeSent == null && item.Deleted == 0 && item.StatusID == 0).AsNoTracking().ToList();
        }

        public void UpdateMessageStatus(int messageId, string mobileNo, short status)
        {
            var message = entities.SMSMessage.AsNoTracking().Where(item => item.MessageID == messageId && item.MobileNo == mobileNo).FirstOrDefault();
            if (message != null)
            {
                message.StatusID = status;
                message.TimeSent = DateTime.Now;
                entities.Set<SMSMessage>().AddOrUpdate(message);
                entities.SaveChanges();
            }
        }
    }
}

}

Is the SaveChanges statement being executed? There is an if() around it... And what does the SOX window show inside your tables, or what happens after a restart? — H H, Aug 03 '17 at 15:54
Hi, Not sure what you mean by SOX window, if you restart the service or app, it picks up the changes fine.. but if its running in a loop and I do a change in the db( via SSMS ), its not reflected in my return of GetUnSentMessages() function.. — Pravi, Aug 03 '17 at 15:59

Jaroslav Surala · Accepted Answer · 2017-08-03T16:26:51.543

1

The problem is that EF6 use IdentityMap pattern. You must create new DB contetxt after update. Here http://codethug.com/2016/02/19/Entity-Framework-Cache-Busting/ is nice article about it.

edited Aug 03 '17 at 16:26

answered Aug 03 '17 at 16:04

Jaroslav Surala

93
1
1
8

update2 code changes made it work fine.. thank you..if I had retained this line of code(entities.Entry(message).State = System.Data.Entity.EntityState.Modified;), I had some attach error - similar to "Attaching an entity of type 'SMSMessage' failed because another entity of the same type already has the same primary key value", hence changed it.. – Pravi Aug 03 '17 at 17:15
Yes, if you do Update of the entity you store it to the cache. So you must after update entity detach the updated entity like this entities.Entry(message).State = EntityState.Detached; Here https://stackoverflow.com/questions/27423059/how-do-i-clear-tracked-entities-in-entity-framework is descibed way how to detach all changes which was done to entities – Jaroslav Surala Aug 03 '17 at 17:36

score 0 · Answer 2 · answered Aug 03 '17 at 16:39

The moment your DbContext is initialised, all subsequent changes that you have made in SSMS, as noted by your comments, will not be reflected, in that instance of your DbContext unless you do a refresh.

I hope the following code snippet can provide a more straightforward logical flow, that you can tweak to fit your application.

using(var SMSEntities = new SMSEntities()
{
    // Your first query
    var messages = entities.SMSMessage
        .Where(item => item.TimeSent == null && item.Deleted == 0 && item.StatusID == 0)
        .ToList();

    // To simulate whatever you're doing in SSMS here.
    LongRunningMethod();

    // At this point, even when you re-query, you will not be getting the updated values.
    messages = entities.SMSMessage
        .Where(item => item.TimeSent == null && item.Deleted == 0 && item.StatusID == 0)
        .ToList();

    // To get updated values, do this.
    SMSEntities.Entry(messages).Reload();
    // Messages here will be updated with what you did in SSMS.
    messages = entities.SMSMessage
        .Where(item => item.TimeSent == null && item.Deleted == 0 && item.StatusID == 0)
        .ToList();
}

The explanation is this, EF will still make a database query as usual. Now, after getting the results back from Database, EF realises that there is a list of messages with a set of IDs tied to your context(SMSEntities) that you have obtained via your very first query. EF will instead return the old entities, which is what you perceived as caching.

You can reload to force a refresh or simply, new up a DbContext instance.

I don't want to new up dbcontext as I am Depenedency injecting it.. if you look at my constructor..that is what i mean in my title "Injecting dbcontext caching issue", I can understand that its returning old entities..cheers — Pravi, Aug 03 '17 at 16:44
I realise that, which is why the entity refresh is my main suggestion. You need to `Reload()` if you want updated messages. New-ing up the `DbContext` is just another option. — IvanJazz, Aug 03 '17 at 16:47
In my situation, how do i reload it, what's the best scenario of doing this, injecting or new-ing up, I want some suggestions on that.. please..thank you. — Pravi, Aug 03 '17 at 16:49
That depends on your application, can't comment too much as we've only got to see your repository thus far. Firstly, it's not usual for you to change your DB in SSMS and still have your application's `DbContext` alive for so long that it needs refreshing to get latest data. Secondly, the `Reload()` method, serves as a useful method, when your application logic knows your `DbContext` HAS to be refreshed, say, executing raw sql queries that changes the data in your table. In this case your application logic will not know when you're going to change things externally(SSMS). — IvanJazz, Aug 03 '17 at 16:59

EF6, injected dbContext caching issue

2 Answers2