Entity Framework DbSet Helper functions for Insert and Update

Posted on July 13, 2012 by Michael Roma

Here are three DbSet helper functions that help with the following:

  • Insert - helper function will set all null fields to a standard default value based on type
  • Update - helper function will set the modified state for fields that are not null. This allows the update to only update fields specified
  • InsertOrUpdate - helper funciton that will determine if insert or update needs to happen based on the existence of the id value on the record

Note: All entities must inherit from TableBase that contains a key value of id. Database Context and Model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Entity;
using System.Data.Objects;
using System.ComponentModel.DataAnnotations;

namespace EFTest1.Data
{
    public class Db : DbContext
    {
        // define be sets
        public DbSet Customers { get; set; }      

        public Db(): base(@"Server=localhost\sqlexpress;Database=eftest;Trusted_Connection=True;") { }                
    }


    // Base table entity
    public abstract class TableBase
    {
        [Key]
        public int id { get; set; }
    }


    // Customer entity
    public class Customer: TableBase
    {                    
        // customer fields   
        public string Name { get; set; }        
        public int? Type { get; set; }       
    }
}

DbSet Helper Functions

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Entity;

namespace EFTest1.Data
{
    public static class MyDbSetExtensions
    {
        // function that determine if insert or update needed based on ID value exists
        public static T InsertOrUpdate(this DbSet db, T o, DbContext c)
            where T : TableBase
        {
            // check if id does not have a vlaue
            if (o.id == 0)
            {
                // insert
                return db.AddWithDefaults(o);
            }

            // if a value, update
            else
            {
                // update
                return db.UpdatePartial(o, c);
            }
        }

        // DBSet helper function to insert a record, sets defaults for null fields
        public static T AddWithDefaults(this DbSet db, T o) 
            where T : TableBase
        {
            // go through each property in the model
            foreach (var p in typeof(T).GetProperties()
                .Where(x => x.Name != "id").ToList())
            {
                // get the value
                var v = p.GetValue(o, null);

                // check if null
                if (v == null)
                {
                    // check type, set apporitate default
                    if (p.PropertyType == typeof(string))
                        p.SetValue(o, "", null);
                    else if (p.PropertyType == typeof(int?))
                        p.SetValue(o, 0, null);
                    else if (p.PropertyType == typeof(decimal?))
                        p.SetValue(o, 0, null);
                    else if (p.PropertyType == typeof(DateTime?))
                        p.SetValue(o, DateTime.Parse("1/1/1900"), null);
                    else if (p.PropertyType == typeof(bool?))
                        p.SetValue(o, false, null);
                }
            }

            // set the record to be added
            db.Add(o);

            // return this record
            return o;
        }

        // DBSet helper function to set modified for all fields that are non null
        public static T UpdatePartial(this DbSet db, T o, DbContext context)
            where T : TableBase
        {
            // attach the record to be updated
            var entity = db.Attach(o);

            // go through each property in the model
            foreach (var p in typeof(T).GetProperties()
                .Where(x => x.Name != "id").ToList())
            {
                // get the value
                var v = p.GetValue(o, null);

                // Assume null means that the property wasn't passed from the client
                if (v == null)
                    continue;

                // Set this property on the entity to modified unless it's ID which won't change                
                context.Entry(entity).Property(p.Name).IsModified = true;
            }

            // return the record
            return o;
        }
    }
}

Executing Insert or Update

private void doInsertUpdate()
{
    // get instance of context
    using (var db = new Db())
    {
        // insert a customer
        var cInsert = new Customer { Name = "New2" };                
        db.Customers.MyInsertOrUpdate(cInsert, db);                                
        db.SaveChanges();

        // update a customer by id, assuming we don't have the original record
        var cUpdate = new Customer { id = 39, Type = 2 };
        db.Customers.MyInsertOrUpdate(cUpdate, db);
        db.SaveChanges();
    }

    Console.WriteLine("updated! " + DateTime.Now.ToString());
}
comments powered by Disqus