EF DbContext. How to avoid caching?

Question

A lot of time has passed, but still cannot figure out how to avoid caching in DbContext.

I have attached an entity model below for some simple case to demonstrate what I mean.

The problem is that the dbcontext caching results. For example, I have the following code to request 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 => xb).Single(); } 

In this case, everything is in order: I have what I want (only people with archive == false).

But if I add another request after it, for example, a request for buildings that have people who have the Archived Flag flag set, I have the following things that I really can't understand:

• my previous result, that is, res , will be added with data (there will be added faces with archived == true)
• the new result will contain absolutely everything Person, regardless of whether Archived is equal to

The following code for this request:

 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 => xb).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 => xb).Single(); } 

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

Does anyone know how to solve this problem? How can I get in my request what I really write in my linq for an object?

PS @Ladislav maybe you can help?

Answer 1

You can use the AsNoTracking method in your request.

 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 => xb).Single(); 

I also want to note that your AsEnumerable probably does more harm than good. If you delete it, Select(x => xb) will be translated into SQL. Like you, you select everything and then throw away everything except xb 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 come up, imho, really dangerous. Indeed, you are working with data loaded by EF in order to interpret your request, not data obtained from the interpretation of your request. If one day EF changes is a download policy (for example, with predictive preload), your approach will "send you to the wall."

For your purpose, you will need to load the data necessary to create your filterd object. This selects the building, then foreach Flat selects unarchived entities.

Another solution is to use too different contexts in the UnitOfWork design.