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.

Answer 2

have you tried something like:

ctx.Persons.Where(x => x.Flat.Building.Id == 1 && x.Archived == false);

===== EDIT =====

In this case I think you approach is, imho, really hazardous. Indeed you works on the data loaded by EF to interpret your query rather than on data resulting of the interpretation of your query. If one day EF changes is loading policy (for example with a predictive pre-loading) your approach will "send you in then wall".

For your goal, you will have to eager load the data you need to build your "filterd" entity. That is select the building, then foreach Flat select the non archived persons.

Another solution is to use too separate contexts in an "UnitOfWork" like design.