EF DbContext. How to avoid caching?

Question

Spent a lot of time, but still cann't understand how to avoid caching in DbContext.

I attached below entity model of some easy case to demonstrate what I mean.

The problem is that dbcontext caching results. For example, I have next code for querying data from my database:

using (TestContext ctx = new TestContext())
{
   var res = (from b in ctx.Buildings.Where(x => x.ID == 1)
             select new
             {
                b,
                flats = from f in b.Flats
                        select new
                        {
                           f,
                           people = from p in f.People
                           where p.Archived == false
                           select p
                        }
             }).AsEnumerable().Select(x => x.b).Single();

}

In this case, everything is fine: I got what I want (Only persons with Archived == false).

But if I add another query after it, for example, query for buildings that have people that have Archived flag set to true, I have next things, that I really cann't understand:

1. my previous result, that is res, will be added by data (there will be added Persons with Archived == true too)
2. new result will contain absolutely all Person's, no matter what Archived equals

the code of this query is next:

using (TestContext ctx = new TestContext())
{
   var res = (from b in ctx.Buildings.Where(x => x.ID == 1)
             select new
             {
                b,
                flats = from f in b.Flats
                        select new
                        {
                           f,
                           people = from p in f.People
                           where p.Archived == false
                           select p
                        }
             }).AsEnumerable().Select(x => x.b).Single();


    var newResult = (from b in ctx.Buildings.Where(x => x.ID == 1)
              select new
              {
                  b,
                  flats = from f in b.Flats
                          select new
                          {
                             f,
                             people = from p in f.People
                             where p.Archived == true
                             select p
                           }
               }).AsEnumerable().Select(x => x.b).Single();
            }

By the way, I set LazyLoadingEnabled to false in constructor of TestContext.

Does anybody know how to workaround this problem? How can I have in my query what I really write in my linq to entity?

P.S. @Ladislav may be you can help?

Answer 1

You can use the AsNoTracking method on your query.

var res = (from b in ctx.Buildings.Where(x => x.ID == 1)
         select new
         {
            b,
            flats = from f in b.Flats
                    select new
                    {
                       f,
                       people = from p in f.People
                       where p.Archived == false
                       select p
                    }
         }).AsNoTracking().AsEnumerabe().Select(x => x.b).Single();

I also want to note that your AsEnumerable is probably doing more harm than good. If you remove it, the Select(x => x.b) will be translated to SQL. As is, you are selecting everything, then throwing away everything but x.b in memory.