Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / entity-framework

EFUpsert

0.00/5 (No votes)
16 Feb 2019GPL31 min read 4K   16  
This is a basic Update / Insert (Upsert) Function in Entity Framework

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.

C#
dbSet.Local.FirstOrDefault(expression);

and this one, query the records in your database:

C#
dbSet.SingleOrDefault(expression);

The function defines the DB context from your DB set:

C#
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:

C#
//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. 

C#
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.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)