Why does my Attach() method trigger a "Entity already exists" InvalidOperationException?-CodePudding

This has stumped me for a few hours. I'm rewriting a Winforms desktop app to support an ASP.NET Core website. The app stores some tables locally in a LiteDB cache, and calls a "using" DBContext to get data.

The desktop app uses a TaxAccount abstract class, which is inherited by Household and Business.

On client search, the app calls GetAccount() to display a single user account. Since the DB can be slow, the cache is updated in the background. Here's the method.


        /// <summary>
        /// Retrieve a single account from cache. Later, replace the account object with object from server.
        /// </summary>
        /// <param name="accountID"></param>
        /// <returns></returns>
        public TaxAccount GetAccount(int accountID)
        {
            var accounts = Cache.GetCollection<TaxAccount>();
            var account = accounts.FindById(accountID);

            if (GetSingleAccountTask == null || GetSingleAccountTask.IsCompleted)
            {
                GetSingleAccountTask = Task.Run(() => UpdateAccount(account));
            }

            return account;

            void UpdateAccount(TaxAccount account)
            {
                using (var serverContext = new ApplicationDbContext(ServerOptions))
                {
                    var found = serverContext.Accounts
                        .Include(X => X.Users)
                        .FirstOrDefault(X => X.Id == account.Id);

                    account = found;

                    if (found != null)
                    {
                        accounts.Update(found);
                    }
                    else
                    {
                        accounts.Delete(account.Id);
                    }

                }
            }
        }

I'd like to update single properties of the TaxAccount entity. To do so, I use Attach(taxAccount), this ideally should update just the property I want.

        public void UpdatePrivateLink(TaxAccount taxAccount, string link)
        {
            // Retrieve collection from cache.
            var accounts = Cache.GetCollection<TaxAccount>();
            using (var serverContext = new ApplicationDbContext(ServerOptions))
            {
                // Attach taxAccount to server context.
                serverContext.Attach(taxAccount);
                taxAccount.PrivateFolderLink = link;
                // Update server.
                serverContext.SaveChanges();
                // Update cache.
                accounts.Update(taxAccount);
            }    
        }

This doesn't work. It creates a System.InvalidOperationException : The instance of entity type 'Household' cannot be tracked because another instance with the key value '{Id: 1}' is already being tracked. BUT I CAN'T FIND THE ENTITY.

Here's the list of things I've tried:

changing the Get() query to .AsNoTracking() does nothing.
serverContext.ChangeTracker.Clear() does nothing.
serverContext.Entry(taxAccount) returns a state of EntityState.Detached
there is no metadata in serverContext.ChangeTracker.ToDebugString()
serverContext.Find(taxAccount.Id) makes a database hit
retrieving directly from the LiteDB cache using accounts.FindbyId(taxAccount.Id) creates the same error.

What's worse, if I create a new Household() with the same id, then all of a sudden it does work!

var account = new Household() { Id = taxAccount.Id };
serverContext.Attach(account);
account.PrivateFolderLink = link;
serverContext.SaveChanges();

// Then we have to save in cache.
taxAccount.PrivateFolderLink = link;
accounts.Update(taxAccount);

This work-around makes no sense to me. Why does EF think taxAccount is tracked on a brand-new DbContext? Why can't I get rid of this tracking without creating a new object?

Would appreciate advice.

EDIT:

serverContext.Accounts.Local contains no elements.

EDIT: This test is the simplest implementation that still fails.


        public void AttachTest(int accountID, string link)
        {
            var accounts = Cache.GetCollection<TaxAccount>();
            var acct = accounts.FindById(accountID);

            using (var serverContext = new ApplicationDbContext(ServerOptions))
            {
                serverContext.Attach(acct);
                acct.PrivateFolderLink = link;
                serverContext.SaveChanges();
            }
        }

For full debugging: I'm testing on .NET 5.0 console app, the EF version is 5.0.13 hosted on a .NET Standard 2.1 library.

Here's the TaxAccount model I'm using.

    public abstract class TaxAccount
    {
        [Key]
        public int Id { get; set; }

        [MaxLength(200)]
        public string Name { get; set; }

        public bool Archived { get; set; } = false;
        public string PrivateFolderLink { get; set; }

        public List<AppUser> Users { get; set; }
    }

    public class Household : TaxAccount
    {
    }

    public class Business : TaxAccount
    {
        [EmailAddress, MaxLength(500)]
        public string Email { get; set; }
        [MaxLength(500)]
        public string Phone { get; set; }
        [MaxLength(1000)]
        public string Address { get; set; }
    }

In my ApplicationDbContext, the only fluent logic is to mark the discriminator.

            // Tax Account abstract class.
            builder.Entity<TaxAccount>().HasDiscriminator()
                .HasValue<Household>(nameof(Household))
                .HasValue<Business>(nameof(Business))
                .IsComplete(true);

            builder.Entity<TaxAccount>()
                .Property("Discriminator")
                .HasMaxLength(50);

CodePudding user response：

After some experimenting this morning, I figured it out! Luckily, it has nothing to do with the cache or other DbContexts!

The class TaxAccount has an List<AppUser>, which has a property Accounts, which is an List<TaxAccount>. This many-to-many relationship is creating a cycle within the Attach() method that EF Core doesn't deal well with. To prove this, I wrote two tests, both of which worked!

public void AttachTest(int accountID, string link)
        {
            var accounts = Cache.GetCollection<TaxAccount>();
            var acct = accounts.FindById(accountID);
            // We set the Users relation to be null.
            acct.Users = null;

            using (var serverContext = new ApplicationDbContext(ServerOptions))
            {
                serverContext.Attach(acct);
                acct.PrivateFolderLink = link;
                serverContext.SaveChanges();
            }
        }

public void AttachTestNullUsers(int accountID, string link)
        {
            var accounts = Cache.GetCollection<TaxAccount>();
            var acct = accounts.FindById(accountID);
            // For each user, the Accounts is null, this also breaks the relationship.
            acct.Users.ForEach(X => X.Accounts = null);

            using (var serverContext ...
        }

Now, there's two follow-up questions to this:

Is this better than creating a new instance of TaxAccount and attaching that?
How does this deal with INSERT and UPDATE operations for the many-to-many relationship?

Probably not. Setting acct.Users = null is an unintended outcome, and it'll be easy to forget to restore that relationship once the command is done. OTOH, initializing a new TaxAccount(taxAccount.Id) is a light operation with no effects on the base object.
Poorly, after some testing, Attach() is not a good idea if you want add or remove many-to-many objects. Lookup then update is your best option here.

CodePudding user response：

From the Microsoft Wiki:

Begins tracking the given entity and entries reachable from the given entity using >the Modified state by default, but see below for cases when a different state will >be used.

Generally, no database interaction will be performed until SaveChanges() is called.

My guess is, that the Account is tracked until the changes are saved.