In Entity Framework Core 6, how to update a wide table without returning all columns to memory?

In Microsoft Entity Framework Core 6.0, will using a .Find() after a selective select improve performance?

Here is the situation: the main entity (table) in our system has over 100 columns. Often only 2~5 columns need to be loaded to preform the needed business logic. The business logic might or might now require saving something to that entity.

The question is which is faster:

// Load All 100 columns into memory    
var targetRow = _context.MainTable.FirstOrDefault( /* where condition */);

/*  Preform business logic using 4 values from Entity */
if( /* save is needed */)
{
    targetRow.Status = /* New Value */
    _context.SaveChanges()
}

Or is it faster to return only the needed columns and use .Find() if the Save is called for:

// Load only 5 columns into memory    
var targetRow = _context.MainTable.Where( /* where condition */).Select( r => new { r.id, r.col1, r.col2, r.col3, r.col4, r.col5);

/*  Preform business logic using 4 values from Entity */
if( /* save is needed */)
{
    var trackedEntity = _context.MainTable.Find(targetRow.id);
    trackedEntity.Status = /* New Value */
    _context.SaveChanges()
}

While the second looks like it takes two round trips to the DB, Microsoft says this about Find():

Finds an entity with the given primary key values. If an entity with the given primary key values is being tracked by the context, then it is returned immediately without making a request to the database. Otherwise, a query is made to the database for an entity with the given primary key values and this entity, if found, is attached to the context and returned. If no entity is found, then null is returned.

Question #1: in the second example does EF Core load enough data in the Where().Select() for the Find() to return immediately, or must it make another round trip first?

Question #2: if .Find() does require another full round trip, is there any way to use EF to update the row in this example without loading all 100 columns?

First of all several notes:

1. The fastest way would be using either some kind of stored procedure or 3rd party library which will map (if possible) your logic into a single query
2. You should measure your setup (we don't know anything about data structure, data size, load profiles etc.) yourself (in theory you should not fetch unnecessary data but in practice I have seen situations when there were no meaningful difference for the app).

As for the question - selecting an anonymous object will not result in tracking the entity because there is no entity, i.e.:

var targetRow = _context.MainTable
    .Where( /* where condition */)
    .Select( r => new { r.id, r.col1, r.col2, r.col3, r.col4, r.col5})
    .ToList();

var trackedCount = ctx.ChangeTracker.Entries().Count();

trackedCount should be zero (if not other requests were made on the context). So Find will need to make a roundtrip. This is covered in the Tracking and custom projections.

One thing you can do is to use Attach. Based on my experiments EF does not track the "manually" created instances either (was not able to find the docs covering this), so you can/need do something like:

var targetRow = _context.MainTable
    .Where( /* where condition */)
    .Select( r => new MainTable { Id = r.id, ...}) // construct correctly
    .ToList();
// ...
if(toUpdate)
{
   _context.MainTable.Attach(targetRow);
   targetRow.Something = ....;
   _context.SaveChanges();
}

Read more:

• Change Tracking in EF Core

• Tracking vs. No-Tracking Queries