Modified local EF Core Entity still fetched by a new query

Question

Lets consider these two entity classes:

public class Student
{
    public int Id { get; set; }
    
    public int? TeacherId { get; set; }
    
    public virtual Teacher { get; set; }
}

public class Teacher
{
    public int Id { get; set; }
    
    public virtual ICollection<Student> Students { get; set; }
}

And lets consider this Entity Framework Core scenario:

var studentSet = dbContext.Set<Student>();

// Get a student
var specialStudent = await studentSet.FindAsync(42);

// Exclude it from the relationship
specialStudent.TeacherId = null;

// Get all the remaining student in the relation ship
var assignedStudents = await studentSet.Where(s => s.TeacherId != null).ToListAsync();

// Do some modifications on assigned students

// Only at the very end of the scenario: commit all the changes to the database in one call
await dbContext.SaveChangesAsync();

My issue is that my specialStudent is part of the assignedStudents, even if its TeacherId is null as expected.

I guessed that Entity Framework Core would run the query on the database, compare the results with its cached entities, fetch the missing one, and kind of re-run the query to exclude from the results the modified entities that does not fit the query.

I guess the last part of my guessing was wrong.

Is it possible to achieve what I want?

I don't know if it is relevant am using Npgsql.EntityFrameworkCore.PostgreSQL in version 3.1.4 and my DbContext is configured to use Proxy (optionsBuilder.UseLazyLoadingProxies()).

Answer 1

I did not understand why Panagiotis Kanavos said in the comments that I should NOT do this. The transactions exist for this purpose and Entity Framework let us use it very easily. I can't see why it would break EF's way of working.

This is why I will not mark this as the accepted answer right away, so he/someone else can explain why it is bad. But in the meantime , this did the job for me:

// Initialize the transaction
await dDbContext.Database.BeginTransactionAsync();

var studentSet = dbContext.Set<Student>();

// Get a student
var specialStudent = await studentSet.FindAsync(42);

// Exclude it from the relationship
specialStudent.TeacherId = null;

// Apply the changes locally so it can be reflected in the next calls 
// Since there is a transaction "in progress", the changes are not commited to the database
await dbContext.SaveChangesAsync();

// Get all the remaining student in the relationship
// This time, mySpecialStudent is no longer part of the results
var assignedStudents = await studentSet.Where(s => s.TeacherId != null).ToListAsync();

// Do some modifications on assigned students

// Save the changes to thoses entities
await dbContext.SaveChangesAsync();

// Only at the very end of the scenario commit the transaction to push the data to the database
await dbContext.Database.CurrentTransaction.CommitAsync();