[ACCEPTED]-Entity Framework mergeoption notracking bad performance-linq-to-entities

Accepted answer
Score: 181

If you disable change tracking by setting 41 the NoTracking merge option you save the performance 40 costs of attaching objects to the contexts 39 but on the other hand you also lose identity 38 management.

This means that potentially much 37 more objects - many with the same key - will 36 be materialized.

Example: Suppose you have 35 a User entity with a Roles collection as navigation 34 property. Also suppose you have 1 million 33 users in the database and all users are 32 in the same 10 roles, i.e. every user has 31 a roles collection with 10 elements. If 30 you run the following query...

var users = context.Users.Include("Roles").ToList();

...the number 29 of materialized and instantiated objects 28 depends on the merge option:

  • If you don't 27 use NoTracking you will have 1.000.010 objects in 26 memory, namely 1 million users, but only 25 10 roles because identity mapping will ensure 24 that only 1 role per key is materialized 23 and attached to the context. The same 10 22 role instances are used for all user's Roles collection.

  • If 21 you use NoTracking however, EF won't attach objects 20 to the context, hence identity management 19 is disabled and you will have 11.000.000 18 objects in memory: 1 million users and 10 17 role instances per user, i.e. 10 million 16 role objects. So, you have more than 10 15 times as many materialized objects as when 14 the objects are attached to the context.

Object 13 materialization is classified with "moderate" performance costs:

Operation: Materializing the objects
Relative Cost: Moderate
Frequency: Once for each object that a query returns.

Comments: The 12 process of reading the returned DbDataReader 11 object and creating objects and setting 10 property values that are based on the values 9 in each instance of the DbDataRecord class. If the object already exists in the ObjectContext and the query uses the AppendOnly or PreserveChanges merge options, this stage does not affect performance.

In 8 other words: If the query uses the NoTracking merge 7 option, this stage does affect performance and it might be possible 6 that the performance benefits of disabled 5 change tracking are destroyed by the drawbacks 4 of disabled identity management and multiplied 3 object materialization.

Since EF Core 5.0 2 there is the additional option 'NoTrackingWithIdentityResolution' which disables 1 general tracking but will do identity resolution.

More Related questions