0

I'm having an issue with SQLite DB access. I get wrong data returned. This is a single app running as a "service" that is accessed by stations via TCP/IP sockets Client/Server. This app does all of the DB Access. The "server" is using .net 6.x and EF Core 6.x with SQLite.

What I see is that I'll get the previous data from the DB as opposed to the current state.

When I use a linq on the context as follows:

Account account = custDB.Accounts.AsNoTracking()
        .Where(a => a.AcctNo == accountNum)
        .FirstOrDefault(); 

I'll get whats on Disk fine but after a DB update I still get the same initial values.

I tried using .AsNoTracking() in my Linq Select and I got the current data in the DB every time. But when I Update the DB the new data is not written to DB and I get no errors thrown.

Here's the sequence:

  • Lookup record using linq:
Account account = custDB.Accounts.AsNoTracking()
        .Where(a => a.AcctNo == accountNum)
        .FirstOrDefault(); 
  • Perform tasks to update returned object account
  • pass updated account object to update routine
    private void UpdateAccountTable(Account account)
    {
      Account acct;
      try
      {
        acct = custDB.Accounts.Where(a => a.ID == account.ID).Single();
      }
      catch (Exception ex)
      {
        throw new DbUpdateException(Message);
      }

      try
      {
        acct.AcctNo = account.AcctNo;
        acct.Active = account.Active;
        acct.Address1 = account.Address1;
        acct.City = account.City;
        acct.IsAPIAcct = account.IsAPIAcct;
        acct.Name = account.Name;
        acct.Phone = account.Phone;
        acct.SerialNo = account.SerialNo;
        acct.State = account.State;
        acct.SWSIMID = account.SWSIMID;
        acct.SWSIMPhrase = account.SWSIMPhrase;
        acct.Zip = account.Zip;
        acct.IsVerified = account.IsVerified;
        acct.CleanHash = account.CleanHash;
        acct.OverRideHash = account.OverRideHash;
        acct.Zip4 = account.Zip4;
        custDB.SaveChanges();
      }
      catch (Exception ex)
      {
        throw new DbUpdateException(Message);
      }
    }

There are no errors thrown. Results are it looks good programmatically, but the DB is never updated with the new values.

Now if I remove the .AsNoTracking() in the first select linq the DB is correctly updated.

What am I doing wrong.

Larry
  • 177
  • 1
  • 1
  • 10
  • Side tangent, do you really need two try/catch blocks with duplicate codes? – beautifulcoder Mar 28 '23 at 22:05
  • Do not share DbContext - recreate it, or at least call `DbContext.ChangeTracker.Clear();` – Guru Stron Mar 28 '23 at 22:05
  • @beautifulCoder, The code presented is a quick edit removing extraneous code. I could've consolidated that here. There is a lot more code between the 2 try blocks. – Larry Mar 29 '23 at 13:17

3 Answers3

0

The AsNoTracking is meant for queries that do not change the data. Therefore, calling SaveChanges does not take effect.

https://entityframeworkcore.com/querying-data-asnotracking

No tracking query executes quickly because there is no need to setup change tracking information. It is useful when the results are used in a read-only scenario.

beautifulcoder
  • 10,832
  • 3
  • 19
  • 29
0

EF Core uses change tracking to perform DML, so any inserts will result in some data present in change tracker. Data present in change tracker can result in stale data returned (either without actually hitting the database or skipping the mapping AFAIK, also see this answer for some details)

DbContext was designed to be lightweight and quite cheap to be created and thrown away, usual pattern is having one context instance per request/scope (for example in ASP.NET Core you usually will have one per HTTP Request). Without seeing the whole solution it is hard to tell how it would be better to modify it so it follows this pattern. If it is not currently possible due to amount of refactoring - you can also look into manually clearing change tracker via ChangeTracker.Clear (custDB.ChangeTracker.Clear();), for example after each SaveChanges.

Guru Stron
  • 102,774
  • 10
  • 95
  • 132
0

For those that recommended I do a Using context, yes this was the issue. I opened the context during an Open DB method and kept it open. This is a read many times type table, write infrequently. After further testing I found I had to pass the context to my UpdateAccountTable method parms otherwise the DB was "locked." Now I see correct updates when expected and get updated data back correctly as well.

    private void UpdateAccountTable(PostalCustDB custDB, Account account) {...}

Thanks everyone.

Larry
  • 177
  • 1
  • 1
  • 10