Does Ef Upsert Have to Be Done Manually

Does EF upsert have to be done manually?

public void InsertOrUpdate(DbContext context, UEntity entity)
{
    context.Entry(entity).State = entity.Id == 0 ?
                                   EntityState.Added :
                                   EntityState.Modified;
    context.SaveChanges();
}

http://forums.asp.net/t/1889944.aspx/1

Entity Framework 6 Upsert

First I should point out that the logic in the (non-EntityState) example you posted doesnt look like I would expect it to - at least based on my understanding, which may be wrong :)

*Disclaimer - I've hacked this out in a text editor, please excuse any bugs.

If we take this as your requirement:

Essentially all I want to do is update the record if dlAccount.GId == dlG.Id or insert it if it does not exist.

Then I would expect the non-EntityState version to look like this:

using (var db = new Entities.DB.DConn())
{
    //...
    foreach (Account account in accounts)
    {
        Entities.DB.Account dlAccount = null;

        Entities.DB.Account exisitngAcct = db.Accounts.Where(x => x.GId == dlG.Id).FirstOrDefault(); //x.GId is NOT ad primary key
        if (exisitngAcct != null)
        {
            //If there is an EXISTING account, it will already be tracked by EF so no need to attach it.
            dlAccount = exisitngAcct;                
        }
        else
        {
            //No account exists, so we need to create one, and ADD it to our EF context as a new Entity 
            dlAccount = new Entities.DB.Account();
            db.Accounts.Add(dlAccount);
        }

        dlAccount.GId = dlG.Id;
        dlAccount.AccountName = account.NameAtFI;
        dlAccount.AccountNumber = account.AcctNumber;
        dlAccount.AcctType = account.AcctType;
        dlAccount.AsOfDate = account.DateCreated;
        dlAccount.IsDeleted = false;
        dlAccount.DateModified = DateTime.UtcNow.ToUniversalTime();

        db.SaveChanges();
    }
}

Assuming the above is what you need, and also assuming that we have a good reason not to just use EF tracking as above, then manually handling EF state would look something like this:

using (var db = new Entities.DB.DConn())
{
    //...
    foreach (Account account in accounts)
    {
        Entities.DB.Account exisitngAcct = db.Accounts.FirstOrDefault(x => x.GId == dlG.Id).FirstOrDefault(); //x.GId is NOT ad primary key

        //NB. Since we're already pulling up the record with EF, there is *probably* no measurable advantage in not just using EF tracking at this point (unless this is a HUUUGE list of objects)
        //    in which case we should use the .AsNoTracking() modifier when we load the records (and they should be loaded in batches/all at once, to reduce DB hits)

        Entities.DB.Account dlAccount = new Entities.DB.Account();
        if(exisitngAcct == null)
        {
            db.Entry(dlAccount).State = EntityState.Added;
        }
        else
        {
            dlAccount.Id = exisitngAcct.Id; //We have to set the PK, so that EF knows which object to update
            db.Entry(dlAccount).State = EntityState.Modified;
        }

        dlAccount.GId = dlG.Id;
        dlAccount.AccountName = account.NameAtFI;
        dlAccount.AccountNumber = account.AcctNumber;
        dlAccount.AcctType = account.AcctType;
        dlAccount.AsOfDate = account.DateCreated;
        dlAccount.IsDeleted = false;
        dlAccount.DateModified = DateTime.UtcNow.ToUniversalTime();

        db.SaveChanges();
    }
}

Entity Framework Code-First: How to manually update the database?

In the first scenario where you changed the Code First Model, before you went and modified the database manually, the answer is to open the (Nuget) Package Manager Console and type:

update-database -verbose

Except - because in this case you are removing a column this will report that it's about to delete something, and it won't delete anything without you explicitly saying that's OK. So you type:

update-database -f -verbose

Now this will delete the column you had in your Model. -verbose says to show you the SQL it runs. If you're scared of just letting it delete things and rather inspect the SQL before it runs, use:

update-database -f -script

That will instead dump the SQL out to a script you can look over, and run manually yourself. It includes a SQL line that updates EF Code First's own understanding of the database.

In the case where you went on and deleted the column in the database manually, you now have a more complex scenario on your hands. There is a table in the database created by Entity Framework, called EdmMetadata in older versions and _MigrationHistory in newer versions. That table contains a hash of the entire database that now does not match the database itself. Running migrations (update-database) is now going to break over and over because of the mismatch until you resolve it. The hash isn't human-friendly and won't help you. You can resolve it in multiple ways:

1. You can add a Manual Migration with add-migration <name>. In this case I might name it with add-migration DeletePurchaseColumn or whatever the name of the column was. In the generated C# Schema Migrations code, in the Up() method, comment out the DeleteColumn line handling the column you already deleted. When you next run update-database, the code will: look at the hash, look at the list of Manual Migrations, see there's a newer Manual Migration than the state of the database, run Automatic Migrations up to the generated hash stored in the Manual Migration C# code, run the Manual Migration, then update the hash in the database. If it doesn't get any SQL errors, anyway. This is the most common way I resolve this problem.

2. You can run manual SQL to return the DB to the way Entity Framework expects (the way it was before you manually modified it, which brings it back in line with the hash) by inspecting what you had before and what your db currently looks like. In this case, you'd add the column back, so EF can be the one to delete it.

3. If you're very far away from what the hash is and what the database actually looks like, you should consider starting Migrations over by just wiping out any Manual Migrations in code and the EF version history table in the database, and starting over with enable-migrations.

Trouble

You can still run into trouble, for example if you have an arrangement in the db schema that is hard for EF to recognize, represent or match. Self-referencing tables can cause trouble, as can anything even slightly interesting about Primary Keys - for example sharing a key between tables requires careful C# work to match the code to the schema.

The simplest way out is to back up the database, try a series of add-migration Manual Migrations, and zen the results. For example, if there's a column in the database that's bugging EF like crazy, for example with a data type that isn't well-supported or a complicated key-sharing arrangement, but, it just doesn't matter much because you won't be accessing it anyway, you can get it into the hash and then stop thinking about it by generating a Manual Migration with add-migration, commenting out the AddColumn code (or Rename or Update) for that column, and moving on. The hash generated in the Manual Migration will capture EF's good-enough impression of the column, and you'll be able to get on with your work. If you ever do access that column from code though, there's a reasonable chance EF will explode. So, you should add a comment to the column with that warning.

If none of the above works, you are now in the ugliest part of Entity Framework Code First. You need to eliminate the hash table and reverse engineer the db into code files.

The good news on the second step, reverse engineering the db into code, is that Microsoft has released a tool into beta that will do this for you.

Walk-through of reverse-engineering a db, and EF Power Tools

You can skip many of the first steps there since they're just setting up a DB and adding some nonsense to it so they can demonstrate what you need to do: Reverse Engineer a db.

Can't make EF Code First work with manually changed data base

Try to add this to startup of your application (you can put it to App_Start):

Database.SetInitializer<EFDbContext>(null);

It should turn off all logic related to handling the database from EF. You will now be fully responsible for keeping your database in sync with your model.

Update a record without first querying?

You should use the Attach() method.

Attaching and Detaching Objects

Update Row if it Exists Else Insert Logic with Entity Framework

If you are working with attached object (object loaded from the same instance of the context) you can simply use:

if (context.ObjectStateManager.GetObjectStateEntry(myEntity).State == EntityState.Detached)
{
    context.MyEntities.AddObject(myEntity);
}

// Attached object tracks modifications automatically

context.SaveChanges();

If you can use any knowledge about the object's key you can use something like this:

if (myEntity.Id != 0)
{
    context.MyEntities.Attach(myEntity);
    context.ObjectStateManager.ChangeObjectState(myEntity, EntityState.Modified);
}
else
{
    context.MyEntities.AddObject(myEntity);
}

context.SaveChanges();

If you can't decide existance of the object by its Id you must execute lookup query:

var id = myEntity.Id;
if (context.MyEntities.Any(e => e.Id == id))
{
    context.MyEntities.Attach(myEntity);
    context.ObjectStateManager.ChangeObjectState(myEntity, EntityState.Modified);
}
else
{
    context.MyEntities.AddObject(myEntity);
}

context.SaveChanges();

Entity Framework Update check if the record has changed

If anybody is interested, here is what I did. I created the following method to check if any field has changed before saving changes.

private Dictionary<Type, bool> IsEntityModified()
{
    Dictionary<Type, bool> entity = new Dictionary<Type, bool>();
    var items = _bentities.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);
    foreach (ObjectStateEntry entry in items)
    {
        foreach (string propName in entry.GetModifiedProperties())
        {
            string oldsetterValue, newsetterValue = null;
            // Get orginal value 
            oldsetterValue = entry.OriginalValues[propName].ToString();
            // Get new value 
            newsetterValue = entry.CurrentValues[propName].ToString();

            if (oldsetterValue != newsetterValue)
            {
                entity.Add(entry.Entity.GetType(), true);
            }
        }
    }
    return entity;
}

How to only update specific fields in Entity Framework (Core)

You have to update entity returned by the EF.

string apiResponse = await response.Content.ReadAsStringAsync();

var data = JsonConvert.DeserializeObject<List<Customers>>(apiResponse);

foreach (Customers customer in data)
{
    var current = db.Customers.Find(customer.BranchId);
    if (current != null)
    {
        db.Entry(current).CurrentValues.SetValues(customer);
    }
    else
    {
        db.Customers.Add(customer);
    }

    db.SaveChanges();
}

---

Related Topics