Geek Answers Handbook

Entity framework `AsNoTracking` does not work with anonymous projection

In the order below, I am trying to retrieve data using Anonymous Projection , and I would like not to track the entities that are being called.

Note. I already went through the existing stack question, but could not find a working solution for me.

 using (var db = new Entities()) { db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false; var myprojection = db.Table1 .AsNoTracking() .Include(gh=>gh.Table2) //Update .Include(gh=>gh.Table3) //Update .Select(x => new { table1= x, table2= x.Table2.Where(g => Some Condition), table3= x.Table3.Where(g=>Some Condition) }) .ToList(); var result = myprojection.Select(g =>g.table1).FirstOrDefault(); }

When I use AsNoTracking() data from the internal tables (table 2, 3) is lost during the conversion in this row var result = myprojection.Select(g =>g.table1).FirstOrDefault();

Edit

If I AsNoTracking() , everything works fine.

1) How to use projection and AsNoTracking in the entity structure?

2) Any other option to remove tracking for this request?

Are there any possible workarounds?

+6
source share
4 answers

First of all, it makes no sense to use db.Configuration.ProxyCreationEnabled = false and AsNoTracking() at the same time.

If you use db.Configuration.ProxyCreationEnabled = false , change tracking will be disabled for all requests.

But your problem with internal tables 2 and 3 is not caused by AsNoTracking() .

 db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false;

Turn off lazy loading and I think this is good for performance. If you enable it, you will get the expected result, but the Entity Framework will make an additional SQL query to the database for each row in myprojection . You should do the following:

 using (var db = new Entities()) { db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false; var myprojection = db.Table1 .Include(x=>x.Table2).Include(x=>x.Table3) .Select(x => new { table1= x, table2= x.Table2.Where(g => Some Condition), table3= x.Table3.Where(g=>Some Condition) }) .ToList(); var result = myprojection.Select(g =>g.table1).FirstOrDefault(); }

Using .Include(x=>x.Table2).Include(x=>x.Table3) in your query will force Entity Framerwork to load the related tables 2 and table 3 for a single database query.

+1
source

Use ToList() for your navigation properties. Please note that it will still be implemented in the database. ( EF6 )

 // .. table2 = x.Table2.Where(g => Some Condition).ToList(),

Update:

With EF4, you probably need to map table2 manually:

  table2 = x.Table2.Where(g => CONDITION).Select(x => new {Foo = x.Bar}),
+1
source

Perhaps you can try the following:

 using (var db = new Entities()) { db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false; var myprojection = db.Table1 //.AsNoTracking() //remove.AsNoTracking() from here .Select(x => new { table1= x, table2= x.Table2.Where(g => Some Condition), table3= x.Table3.Where(g=>Some Condition) }) .AsNoTracking()//add .AsNoTracking() here .ToList(); var result = myprojection.Select(g =>g.table1).FirstOrDefault(); }

NOTE. I am not trying to do this. Just an idea, I hope this helps you.

0
source

When I use AsNoTracking () data from internal tables (table 2,3), it is lost during conversion in this row var result = myprojection.Select (g => g.table1) .FirstOrDefault ();

Selecting Table1, in an anonymous type, you will not have access to table2 or table3, although the navigation properties will not be displayed. Objects lose their relationship when you select an anonymous type, you need to select table two and three or not pull it into an anonymous type.

Just put your items directly in the table, where and then make your call

  var myprojection = db.Table1.Join(db.Table2.Where(x => yourcondition), t1 => t1.id, t2 => t2.t1id, (t1, t2) => t1 );

then you need to make another join for table3

but it's probably best to make a call in two queries

0
source

More articles: