J Flex
J Flex

Reputation: 460

EF core 6 result doesn't match the database

I execute a query and I don't always get the same result.

I executed the snippet below step by step with breakpoints. The use case for this code is waiting until a certain process is no longer busy before continuing the execution.

string slug = "abc";
Entity entity = await _resource.GetQueryable()
    .Where(x => x.Slug == slug)
    .FirstOrDefaultAsync();
// entity.IsBusy is true

int retries = 0;
while(entity.IsBusy)
{
    if (retries > 10)
    {
        throw new SyncIsBusyException();
    }
    retries++;

    // Now I manually execute an SQL query on the database.
    // update Entities set IsBusy = 'false'

    Thread.Sleep(3000);
    entity = await _resource.GetQueryable()
        .Where(x => x.Slug == slug)
        .FirstOrDefaultAsync();
    // entity.IsBusy is still true (not in the DB)

    string test = await _resource.GetQueryable()
        .Where(x => x.Slug == slug)
        .Select(x => x.IsBusy)
        .FirstOrDefaultAsync();
    // test is false (which is correct)

    // test if creating a new variable changes things
    var test1 = await _resource.GetQueryable()
        .Where(x => x.Slug == slug)
        .FirstOrDefaultAsync();
    // test1.IsBusy is true (which is again incorrect)
}

The resource:

public virtual IQueryable<TEntity> GetQueryable()
{
    var queryable = _dbContext.Set<TEntity>().AsQueryable();
    return queryable;
}

It looks like some kind of caching, but I don't have any set up. I can also see the SQL query being executed in the console. When I execute this generated SQL query manually on the DB I get the correct and expected result (IsBusy false). I can fix this bug by adding a bool isBusy = entity.IsBusy; above the while and then using that. But I'd still like to know the underlying issue here.

Upvotes: 0

Views: 659

Answers (1)

CodeCaster
CodeCaster

Reputation: 151738

Is Slug the primary key of your entity?

The change tracker will cache the entity by its primary key, unless you clear the local cache (dbContext.ChangeTracker.Clear()) or query it with .AsNoTracking() in the first place.

The latter does prevent updates to the entity.

A Select query projects a subset of the entity, so that query is executed against the store.

Upvotes: 1

Related Questions