EF 4.1 Replace a row?

I have an issue where inside a class I want to update or add a row depending on the record existing inside the DB. In that, rather than having "Create/Update" methods I have a "Save(entity)" method.

The idea is the entity is checked against the database, if it exists then it's an update if it doesn't then its obviously a create.

Using EF 4.1 the problem is that once I read the row from the database via the same context, then this in turn creates a memory cache of that row. When I then try and replace the row via say an attach/add routine it will obviously throw an exception around the row already existing etc (as its trying to force both currentRow and newRow into the same table and fails in reconcillation).

My work-around for this is basically to use the .Remove() on the context which in marks the row for removal from memory and then re-add it in the same USING transaction.

 var ctx = new SecurityContext(this.ConnectionString);
        using(ctx)
        {
            var dbEntry = (ctx.Accounts.Where(a => a.AccountId == entity.AccountId || a.Username == entity.Username)).FirstOrDefault();
        开发者_开发知识库    ctx.Accounts.Remove(dbEntry);

            if (dbEntry != null)
            {
                ctx.Entry(entity).State = EntityState.Added;
            } else
            {
                ctx.Accounts.Add(entity);
                ctx.Entry(entity).State = EntityState.Added;
            }


            ctx.SaveChanges();
        }

My Question is - is this the typical route to take? or is there much smarter / cleaner ways?

I believe this code should work, using Attach rather than Add:

var  ctx = new SecurityContext(this.ConnectionString); 
using(ctx) 
{ 
    ctx.Accounts.Attach(entity);
    ctx.Entry(entity).State = ctx.Accounts.Any(
        a => a.AccountId == entity.AccountId || 
        a.Username == entity.Username) ? 
        EntityState.Modified : EntityState.Added;
    ctx.SaveChanges(); 
} 

Forgive the weird wrapping - wanted to make it fit on the page without scrolling.

Ok, me thinks it was a case of me being a dumbass.. In that i realised the conflict may have occured when two records were being created the exact same except the AccountId were different (given they were generated via caller class). I've modifed the DAO to be the below and now it works.

Pick the code apart if you like :)

    public class AccountDAO : BaseDAO<Account>
{
    public AccountDAO(string connectionString) : base(connectionString)
    {
    }
    public override Account Save(Account entity)
    {
        var ctx = new SecurityContext(this.ConnectionString);
        using(ctx)
        {
            //var dbEntry = (ctx.Accounts.Where(a => a.AccountId == entity.AccountId || a.Username == entity.Username)).FirstOrDefault();
            var dbEntry = (ctx.Accounts.Any(a => a.AccountId == entity.AccountId || a.Username.Contains(entity.Username)));
            //ctx.Accounts.Remove(entity);
            if(!dbEntry)
            {
                ctx.Accounts.Add(entity);
                ctx.Entry(entity).State = EntityState.Added;
            } else
            {
                var currEntity = Read(entity);
                entity.AccountId = currEntity.AccountId;
                ctx.Accounts.Add(entity);
                ctx.Entry(entity).State = EntityState.Modified;
            }

            ctx.SaveChanges();
        }
        return entity;
    }
    public override Account Read(Account entity)
    {
        using (var ctx = new SecurityContext(this.ConnectionString))
        {
            var newEntity = (ctx.Accounts.Where(a => a.AccountId == entity.AccountId || a.Username.Contains(entity.Username))).FirstOrDefault();
            return newEntity;
        }            
    }
    public override void Delete(Account entity)
    {
        using (var ctx = new SecurityContext(this.ConnectionString))
        {
            var ent = Read(entity);
            ctx.Entry(ent).State = EntityState.Deleted;
            ctx.Accounts.Remove(ent);
            ctx.SaveChanges();
        }     
    }
}

see sample

 private void Save(Action<Controls.SaveResult> saveResult)
    {
        if (SavableEntity.EntityState != EntityState.Unmodified)
        {
            if (SavableEntity.EntityState == EntityState.Detached || SavableEntity.EntityState == EntityState.New)
                this.SetIsBusy("Creating new...");
            else
                this.SetIsBusy("Saving changes...");
            DomainContext.SavePartial(SavableEntity, p =>
            {
                this.ReleaseIsBusy();
                if (p.HasError)
                {
                    var res = new Controls.SaveResult() { HasErrors = true };
                    if (saveResult != null)
                        saveResult(res);
                    if (this.EntitySaved != null)
                    {
                        EntitySaved(this, new SavedEventArgs() { result = res });
                    }
                    p.MarkErrorAsHandled();
                }
                else
                {
                    Messenger.Default.Send<T>(this.SavableEntity);
                    var res = new Controls.SaveResult() { HasErrors = false };
                    if (saveResult != null)
                        saveResult(res);
                    if (this.EntitySaved != null)
                        EntitySaved(this, new SavedEventArgs() { result = res });
                    if (this.CloseAfterSave)
                        this.RaiseRequestToClose();
                    this.RaisePropertyChanged("Title");
                }
                RaisePropertyChanged("SavableEntity");

            }, false);
        }

    }

It might be how you worded the question, but you seem to have a strange approach.

1. If you are fetching and updating an entity in the same tier of an application, you shouldn't recreate your context when you save, just hang on to a reference to the context that fetches the entity and it will track the changes to your entity, and you just call SaveChanges() on that same context. You are fighting against its fundamental design otherwise.

2. You should get in the habit of wrapping you SaveChanges() in a transaction. If SaveChanges() triggers changes / inserts to more than 1 row in the database, you run the risk of saving partial changes. You should always save everything or nothing.

   using (TransactionScope ts = new TransactionScope()) {
   ctx.SaveChanges();
   ts.Complete(); }

3. If you are developing a 3 tier app using perhaps wcf for the middle tier, and therefore serializing the entity from a client, you could simply add a new property "IsNew" which the client passes through. If not new, you should use Attach(). eg If IsNew, then ctx.Accounts.Add(entity), else ctx.Accounts.Attach(entity)

4. Assuming the above, if you have an IsNew entity, but want to ensure it doesn't exist as a final check in your middle tier (I assume you client would have already attempted to let the user edit the existing entity if it exists). You should first put a uniqueness constraint on the database, as this is your final defence against duplicates. Second, you can take the approach you have already, where you check if the entity exists in the database, and then either merge the entities by hand if that is your required functionality, or throw an exception / concurrency exception that forces the client to reload the real entity and they can modify that one.

Point 4 is fairly complex, and there are a number of approaches, which are too complex for me to bother trying to describe. But beware, if you take your approach where you check it exists, then decide to add/attach, make sure you wrap that in a transaction, as otherwise there is a chance the new entity will be added by another user/process in between when you check (with your Where()) and SaveChanges().