-2

I have inspected EF Core 6 through SQL Server Profiler which is shocking from a performance point of view.

Here nothing profiled which is very good:

var user = App.Ctx.LoginUsers;

This code nothing profiled which is also very good:

user = App.Ctx.LoginUsers;

Here, the code profiled which is also good:

var users = App.Ctx.LoginUsers.ToList();

but with this code, when profiled, is very bad because it's already in context memory above:

users = App.Ctx.LoginUsers.ToList();

If every query is rounding from SQL Server even if it's in context memory then performance will be disaster?

But we have expected to round-trip to SQL server only for differential data which has been changed

  1. Inserted to be added to dbcontext memory

  2. only Modified to update in dbcontext memory

  3. and only Deleted to remove from dbcontext memory

and provide data any ask data from dbcontext memory.

In this way, we thought performance would have boosted drastically.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Sanjeeb
  • 1
  • 1

2 Answers2

3

if every query is rounding from sql server

Yes, that's what queries do. They fetch data from the database. If you want to access the data that was loaded in previous queries, use DbSet< TEntity >.Local, or store them in your own collection.

The DbContext Change Tracker stores the entities retrieved from your database, but it's not designed as a read-through cache. If you run another query, another query will be sent to the database.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

What do you mean by "disaster"?

By default, EF will track references that it has already fetched. Do no confuse this with caching as being for a performance reason. Loading entire sets of entities as tracked references is not a good idea. Not because it still means extra round trips to the database despite that those instances are already tracked, but because the more instances that the EF DbContext is tracking, the more memory is in use and the longer it can take to fetch additional data since those operations will automatically look to associate any already tracked instances for any relationships for the data being returned.

If you fetch a significant amount of data and don't need that data to be tracked by the DbContext (I.e. you don't intend to update it so you don't need change tracking ) then use AsNoTracking().

var users = App.Ctx.LoginUsers.AsNoTracking().ToList();

This will still fetch all users from the DB but the Context will not be tracking these instances.

If you know you have loaded the desired data already, or want to check and use any pre-loaded and tracked instances before going to the database, then use the Local set from the DbSet to tell EF to just go to the tracking instances:

// Look for a tracked instance:
var user = App.Ctx.LoginUsers.Local.SingleOrDefault(x => x.UserId == userId);
if (user == null)
   user = App.Ctx.LoginUsers.Single(x => x.UserId == userId);

This is a common strategy when you know some data might already be tracked and you are dealing with detached entities. (I.e. a user loaded with AsNoTracking or deserialized) The Local call checks the tracking store for that entity, so no round trip to the DB, then if we don't find it we can load and track it from the DB.

The other detail to be aware of is that while queries against the DbContext that would return an already tracked instance still trigger an SQL query, the tracked instance is not updated by the data returned by that query. For instance if you load your Users into the DbContext, then some other process not using that DbContext instance goes and modifies one or more of the users in the database, fetching those users from the DbContext will return the tracked data as it was when it was loaded. An SQL query will be run against the database, however any modified data state does not automatically update any of the already tracked entities.

Steve Py
  • 26,149
  • 3
  • 25
  • 43