C# .NET Core DbContext not returning data that is in the database

Question

I have the following code:

public void someMethod(){
    ...

    var accounts = myRepo.GetAccounts(accountId)?.ToList(); 

    ...

    foreach (var account in accounts)
    {
        account.Status="INACTIVE";
        var updatedAccount = myRepo.AddOrUpdateAccounts(account);
    }
}

public Account AddOrUpdateAccounts(Account account){
    //I want to compare account in the Db and what is passed in. So get the account from DB
    var accountFromDb = myRepo.GetAccounts(account.Id); //this doesn't return whats in the database.

    //here accountFromDb.Status is returned as INACTIVE, but in the database the column value is ACTIVE
    ...
    ...
}

public IEnumerable<Account> GetAccounts(int id){
    return id <= 0 ? null : m_Context.Accounts.Where(x => x.Id == id);
}

Here, inside someMethod() I am calling GetAccounts() that returns data from the Accounts table.

Then I am changing the Status of the account, and calling AddOrUpdateAccounts().

Inside AddOrUpdateAccounts(), I want to compare the account that was passed in and whats in the database. When I call GetAccounts(), it returned a record with STATUS="INACTIVE". I haven't done SaveChanges(). Why didn't GetAccounts() returned the data from the database? In the Db the status is still "ACTIVE"

Answer 1

The repository method should return IQueryable<Account> rather than IEnumerable<Account> as this will allow the the consumer to continue to refine any criteria or govern how the account(s) should be consumed prior to any query executing against the database:

I would consider:

public IQueryable<Account> GetAccountsById(int id){
    return m_Context.Accounts.Where(x => x.Id == id);
}

Don't return #null, just the query. The consumer can decide what to do if the data is not available.

From there the calling code looks like:

var accounts = myRepo.GetAccounts(accountId).ToList(); 

foreach (var account in accounts)
{
    account.Status="INACTIVE";
}

Your addOrUpdate wouldn't work:

public Account AddOrUpdateAccounts(Account account){
    ...
    var account = myRepo.GetAccounts(account.Id); //this doesn't return whats in the database.

You pass in the Account as "account" then try declaring a local variable called "account". If you remove the var keyword you would load the DbContext's record over top your modified account and your changes would be lost. Loading the account into another variable isn't necessary as long as the account is still associated with the DbContext.

Edit: After changing the var account = ... statement to look like:

public Account AddOrUpdateAccounts(Account account){
    ...
    var accountToUpdate = myRepo.GetAccounts(account.Id); //this doesn't return whats 

accountToUpdate will show the modified status rather than what is in the database because that DbContext is still tracking the reference to the entity that you modified. (account) For instance if I do this:

var account1st = context.Accounts.Single(x => x.AccountId == 1);
var account2nd = context.Accounts.Single(x => x.AccountId == 1);
Console.WriteLine(account1st.Status); // I get "ACTIVE"
Console.WriteLine(account2nd.Status); // I get "ACTIVE"
account1st.Status = "INACTIVE";
Console.WriteLine(account2nd.Status); // I get "INACTIVE"

Both references point to the same instance. It doesn't matter when I attempt to read the Account the 2nd time, as long as it's coming from the same DbContext and the context is tracking instances. If you read the row via a different DbContext, or use AsNoTracking() with all of your reads then the account can be read fresh from the database. You can reload an entity, but if those variables are pointing at the same reference it will overwrite your changes and set the entity back to Unmodified. This can be a little confusing when watching an SQL profiler output because in some cases you will see EF run a SELECT query for an entity, but the entity returned has different, modified values than what is in the database. Even when loading from the tracking cache, EF can still execute queries against the DB in some cases, but it returns the tracked entity reference.

/Edit

When it comes to saving the changes, it really just boils down to calling the SaveChanges on the DbContext that the account is associated. The "tricky" part is scoping the DbContext so that this can be done. The recommended pattern for this is the Unit of Work. There are a few different ones out there, and the one I recommend for EF is Mehdime's DbContextScope, however you can implement simpler ones that may be easier to understand and follow. Essentially a unit of work encapsulates the DbContext so that you can define a scope that repositories can access the same DbContext, then commit those changes at the end of the work.

At the most basic level:

public interface IUnitOfWork<TDbContext> : IDisposable where TDbContext : DbContext
{
    TDbContext Context { get; } 

    int SaveChanges();
}

public class UnitOfWork : IUnitOfWork<YourDbContext>
{
    private YourDbContext _context = null;
    TDbContext IUnitOfWork<YourDbContext>.Context
    {
        get { return _context ?? (_context = new YourDbContext("YourConnectionString"); }
    }

    int IUnitOfWork<YourDbContext>.SaveChanges()
    {
        if(_context == null)
            return 0;
       return _context.SaveChanges();
    }

    public void Dispose()
    { 
        try
        {
           if (_context != null)
              _context.Dispose();
        }
        catch (ObjectDisposedException)
        { }
    }
}

With this class available, and using dependency injection via an IoC container (Autofac, Unity, or MVC Core) you register the unit of work as Instance per Request so that when the controller and repository classes request one in their constructor, they receive the same instance.

Controller / Service:

private readonly IUnitOfWork<YourDbContext> _unitOfWork = null;
private readonly IYourRepository _repository = null;

public YourService(IUnitOfWork<YourDbContext> unitOfWork, IYourRepository repository)
{
   _unitOfWork = unitOfWork ?? throw new ArgumentNullException("unitOfWork");
   _repository = repository ?? throw new ArgumentNullException("repository");
}

Repository

private readonly IUnitOfWork<YourDbContext> _unitOfWork = null;

public YourService(IUnitOfWork<YourDbContext> unitOfWork)
{
   _unitOfWork = unitOfWork ?? throw new ArgumentNullException("unitOfWork");
}

private YourDbContext Context { get { return _unitOfWork.Context; } }

Big Disclaimer: This is a very crude initial implementation to explain roughly how a Unit of Work can operate, it is no way production suitable code. It has limitations, specifically around disposing the DbContext but should serve as a demonstration. Definitely look to implement a library that's already out there and addresses these concerns. These implementations properly manage the DbContext disposal and will manage a scope beyond the context, like a TransactionScope so that their SaveChanges is required even if the unitOfWork.Context.SaveChanges() is called.

With a unit of work available to the Controller/Service and Repository, the code to use the repository and update your changes becomes:

var accounts = myRepo.GetAccountsById(accountId).ToList(); 
foreach (var account in accounts)
{
    account.Status="INACTIVE";
}

UnitOfWork.SaveChanges();

With a proper unit of work it will look more like:

using (var unitOfWork = UnitOfWorkFactory.Create())
{
    var accounts = myRepo.GetAccountsById(accountId).ToList(); // Where myRepo can resolve the unit of work via locator.
    foreach (var account in accounts)
    {
        account.Status="INACTIVE";
    }

    unitOfWork.SaveChanges();
}

This way if you were to call different repos to fetch data, perform a number of different updates, the changes would be committed all in one call at the end and rolled back if there was a problem with any of the data.