Invalidating/Disabling Entity Framework cache

Question

I see there are plenties of question on EF cache, but I have found no solution yet to my problem.

The straight question is

How do I completely disable Entity Framework 6 cache? Or, can I programmatically tell EF to forget about the cache because something happened to data?

Background

First, I have inherited an application made of a strange mixture of EF (model-first to define entities) and plain old SQL (to manipulate data). What I did was to refactor the application in order to:

• Make simple queries (like GetAll() for an entity) use EF6 LINQ
• Leave complex data manipulation in SQL, using DbContext.Database.Connection when needed
• Add Spring.Web support to enable DI and transactions (not yet)

At the current point, I have reorganized the code so that the main function of the application (running complex SQL queries on huge datasets) works as it did before, but then lookup domain entity manipulation is done smarter using as most Entity Framework as possible

As most....

One of the pages I inherited is a multi-checkbox page I'm going to show you for best understanding. I won't discuss the previous implementor's choice, because it's cheaper to fix my current problem and later refactor code than blocking development for a broken feature.

This is how the page looks like

Basically the Controller method is the following

    [HttpPost]
    public ActionResult Index(string[] codice, string[] flagpf, string[] flagpg, string[] flagammbce, string[] flagammdiv, string[] flagammest,
        string[] flagintab, string[] flagfinanz, string[] flagita, string[] flagest, string pNew){
            Sottogruppo2015Manager.ActivateFlagFor("pf", flagpf);
            Sottogruppo2015Manager.ActivateFlagFor("pg", flagpg);
            Sottogruppo2015Manager.ActivateFlagFor("ammbce", flagammbce);
            Sottogruppo2015Manager.ActivateFlagFor("ammdiv", flagammdiv);
            Sottogruppo2015Manager.ActivateFlagFor("ammest", flagammest);
            Sottogruppo2015Manager.ActivateFlagFor("intab", flagintab);
            Sottogruppo2015Manager.ActivateFlagFor("finanz", flagfinanz);
            Sottogruppo2015Manager.ActivateFlagFor("ita", flagita);
            Sottogruppo2015Manager.ActivateFlagFor("est", flagest);

            return RedirectToAction("Index", new { pNew });
 }

Each string[] parameter is a column in the table. The ActivateFlagFor method runs two queries in sequence

UPDATE table SET --param1-- = 0;
UPDATE table SET --param1-- = 1 where id in (--param2--)

When the cache kicks in

The following is the behaviour:

• I first load the page issuing a LINQ select: checks match the ones and zeroes in columns
• I change one or more checks and submit
• The controller issues the queries to update checks in the DB
• Before redirecting (!means new request!) to reload the page, I check the DB and the changes are applied
• The page reloads issuing the same LINQ select above: old checks are displayed

I am sure that this is a caching problem, because reloading the application fixes the problem. Since the main feature of the application is totally SQL based, changes to lookup tables are reflected into main operation and that's the correct behaviour.

I understand that EF caching is a great feature for performance, but in my case I just don't want it, at least until I migrate the whole application to LINQ DML (probably impossible).

How I use the DbContext

Of course some of you may ask "how do you use your DbContext?" "do you dispose of it correctly?".

• I haven't yet integrated Spring transactions in my Manager classes
• Each object that performs actions on the database is a I<Entity>Manager extending BaseManager
• DbContext is a request-scoped Spring object. I already asked about disposing request-scoped objects but I currently implemented a workaround that, while bad, correctly disposes of the DbContext at the end of the request.

Example code

public class ExampleManagerImpl : BaseManager, IExampleManager
{
    public void ActivateFlagFor(string aFlag, string[] aList)
    {
        string sql = "UPDATE table SET flag" + aFlag + " = 0";
        RunStatementV1(sql);

        if (aList != null && aList.Any())
        {
            sql = "UPDATE table SET flag" + aFlag + " = 1 WHERE id in (" + aList.ToCsvApex() + ")";
            RunStatementV1(sql);
        }
    }

    public IList<Models.Example> GetAll()
    {
        return DataContext.example.ToList(); //I don't dispose of the DataContext willingly
    }
}

and

public abstract class BaseManager {

    public DbContext DataContext { get; set; } //Autowired

    protected void RunStatementV1(string aSqlStatement)
    {
        IDbConnection connection = DataContext.Database.Connection;
        if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken) connection.Open(); //Needed because sometimes I found the connection closed, even if I don't dispose of it
        using (IDbCommand command = connection.CreateCommand())
        {
            command.CommandText = aSqlStatement;
            command.ExecuteNonQuery();
        }

    }
}

What I tried

• How to stop entity framework caching : disabling lazy loading didn't work
• https://stackoverflow.com/a/22818783/471213: Detach seems to solve the problem but I'd need to apply it to dozen of entities, in order to revert someday in the future

Answer 1

If you want to completely ignore EF6's cache for data retrieval, then add AsNoTracking() to the end of your query (before calling ToList() or doing anything else that would execute the query.

MSDN on AsNoTracking()

Please note that doing so will neither check the cache for existing data, nor will it add the results of the database call to the cache. Additionally, Entity Framework will not automatically detect changes to entities that you retrieve from the database. If you do want to change any entities and save them back to the database, you'll need to attach the changed entities before calling SaveChanges().

Your method is currently:

public IList<Models.Example> GetAll()
{
    return DataContext.example.ToList();
}

It would change to:

public IList<Models.Example> GetAll()
{
    return DataContext.example.AsNoTracking().ToList();
}

If you are interested in other options for dealing with EF's cache, I've written a blog post about EF6 Cache Busting.

Answer 2

I had this trouble too, but I could fix it.

I'm using Repository Pattern and using default DI of .Net Core. I've been using AddSingleton(...), but it's wrong to use with DbContext.

So, I've changed to AddScoped, like I read from docs: Scoped lifetime services are created once per request.

It has solved my problem.

You must read this section from ms docs: Service Lifetimes and Registration Options