EFUpsert

• Download latest Repository Archive
• Download local file - 21.3 KB

Introduction

This is a basic Update / Insert (Upsert) Function in Entity Framework.

Have you ever tried updating or inserting records to database using Entity Framework. Well, this is pretty simple considering there's an AddOrUpdate (using systems.migration namespace) but that has certain drawbacks.

What I did is create a simple function with almost similar functionalities, and fast enough to handle recurring database round trips. This was basically tested during my previous problem of updating bulk data's nearly almost million records. So instead of adding try catch for existing records with key validation from database, I'd come out to this one.

This is the most important part of this code, wherein it tracks the local records and prevents the redundant records passing into the local cache be inserted to database.

dbSet.Local.FirstOrDefault(expression);

and this one, query the records in your database:

dbSet.SingleOrDefault(expression);

The function defines the DB context from your DB set:

FieldInfo fieldInfoSet = dbSet
    .GetType()
    .GetField(_set, KeyPropertyBindingFlags);

FieldInfo fieldInfoContext = fieldInfoSet.GetValue(dbSet)
    .GetType().BaseType.GetField(_context, KeyPropertyBindingFlags);

return (DbContext)(fieldInfoContext.GetValue(fieldInfoSet.GetValue(dbSet))
    .GetType()
    .GetProperty(_owner, KeyPropertyBindingFlags)
    .GetValue(fieldInfoContext.GetValue(fieldInfoSet.GetValue(dbSet))));

Once the context have been defined, it will then be used in the upsert function. See sample code:

//for updating the existing records
dbContext.Entry(_exist).State = EntityState.Detached;
dbContext.Entry(entity).State = EntityState.Modified;

//for adding a new seat of records
dbContext.Set<TEntity>().Add(entity);
dbContext.Entry(entity).State = EntityState.Added;

The function consists of simple validation (locally and remotely). The local validation, prevents a duplicates record with the same keys to be inserted to database assuming you have constructed your database correctly.

How to Use It?

Assuming you have an EDMX Table. 

using (var db = new ()) 
    {
        //db optional config
        db.Configuration.AutoDetectChangesEnabled = false;
        db.Configuration.ValidateOnSaveEnabled = false;

        using (var dbContext = db.Database.BeginTransaction(
        System.Data.IsolationLevel.Serializable)
        )
        {
            try
            {
                db.<Database Name>.EFUpsert(
                        x => (your condition),
                        new <Your Model Class>()
                        {
                           //add or updated value here
                        });

                db.SaveChanges();
                dbContext.Commit();
            }
            catch
            {
                dbContext.Rollback();
            }
        }
    }

Easy, right? You can try it.

If you have any corrections or bugs found, let me know.