EF Core query not reflecting updated property without saving

user_5301879 0
Viewed 63

With this code snippet, I query for an entity in my SQL database, and then update a property on that entity.

This query should filter out that entity because I just modified it, but the first one does not. For some reason, the second query does return what I expect.

In my scenario, the ProductionOrderStep entity is a one-to-many relationship to the ProductionOrder entity.

I do not want to add a call to SaveChanges.

Is this a bug in EF Core? Or is this intended behaviour?

[HttpGet("test")]
public void Test()
{
    var productionOrderStepId = 356664;
    var productionOrderId = 305712;

    var pos = context.ProductionOrderStep
                     .Where(x => x.ProductionOrderStepId == productionOrderStepId)
                     .FirstOrDefault();
    pos.Status = 2; // Changes status from 1 to 2

    var incorrectResult = context.ProductionOrder
                                 .Where(x => x.ProductionOrderId == productionOrderId)
                                 .SelectMany(x => x.ProductionOrderSteps)
                                 .Where(pos => pos.Status < 2)
                                 .ToList();

    var correctResult = context.ProductionOrder
                               .Where(x => x.ProductionOrderId == productionOrderId)
                               .SelectMany(x => x.ProductionOrderSteps)
                               .ToList()
                               .Where(pos => pos.Status < 2)
                               .ToList();
}

public class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options): base(options)
    {}

    public virtual DbSet<ProductionOrder> ProductionOrder { get; set; }
    public virtual DbSet<ProductionOrderStep> ProductionOrderStep { get; set; }
}

public class ProductionOrderStep
{
    public int ProductionOrderStepId { get; set; }
    public int ProductionOrderId { get; set; }
    public int Status { get; set; }

    public virtual ProductionOrder ProductionOrder { get; set; }
}

public class ProductionOrderStepConfiguration : IEntityTypeConfiguration<ProductionOrderStep>
{
    public void Configure(EntityTypeBuilder<ProductionOrderStep> builder)
    {
        builder.HasOne(d => d.ProductionOrder)
               .WithMany(p => p.ProductionOrderSteps)
               .HasForeignKey(d => d.ProductionOrderId);
    }
}

public class ProductionOrder
{
    public int ProductionOrderId { get; set; }

    public virtual ICollection<ProductionOrderStep> ProductionOrderSteps { get; set; }
}
entity-framework-core sql c#
1 Answers

It is intended behaviour. A basic breakdown of the behaviour you are seeing:

var incorrectResult = context.ProductionOrder  
    .Where(x => x.ProductionOrderId == productionOrderId)
    .SelectMany(x => x.ProductionOrderSteps)
    .Where(pos => pos.Status < 2)
    .ToList();

This builds an SQL query that will attempt to load any ProductionOrderSteps for a given ProductionOrder where their Status < 2. That WHERE execution step goes to SQL. Since you have updated an entity and not committed the change to the database, that query will not know about your change so EF will not return your expected row.

var correctResult = context.ProductionOrder
    .Where(x => x.ProductionOrderId == productionOrderId)
    .SelectMany(x => x.ProductionOrderSteps)
    .ToList()
    .Where(pos => pos.Status < 2)
    .ToList();

In this case, you are telling EF to run a query to load all Production Order Steps for a given Production Order. Since your updated entity is already tracked, EF will return that updated reference along with the other Production Order Steps that it might load from the DB. THe Where condition is done in-memory so the Status will reflect your updated change.

user_4234970
Related