Random Dev Notes

June 7, 2011

Retrieving Auto Generated Primary Key Values on Insert

Filed under: Development — Tags: — Tom Brothers @ 12:17 am

The default IQToolkit Insert method always seemed a little odd to me in that it didn’t populate an auto generated primary key property after executing the Insert statement.  The following image demonstrates this issue.  Notice how the Categoryid is 0 after the insert.

image

The toolkit does provide a way to get the primary key value but the process isn’t very intuitive (especially after working with other ORMs like LINQ to SQL).  Here is how the toolkit provides a way to get the primary key value.

image

One problem with this approach is that it isn’t an option when using the Unit of Work (EntitySession) approach.

Personally, I ‘d rather have a simpler approach such as the following.

image

 

To achieve this simpler approach I needed to create a class that could handle the inserting of an Entity.  This class needed to read the mapping information to see if the Entity had an auto-generated primary key.  If the Entity did have an auto-generated primary key then the class needed to create an Insert function that could retrieve the new value and set it on the Entity instance.  However, if the Entity didn’t have an auto-generated primary key the class would return a function that called the standard IQToolkit Insert method.

Here is the class that I came up with:

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using IQToolkit;
using IQToolkit.Data;
using IQToolkit.Data.Common;
using IQToolkit.Data.Mapping;
 
namespace IQToolkitContrib {
    /// <summary>
    /// This class handles processing the Insert statement with special consideration given to AutoGenerated Primary Keys to ensure that 
    /// the Entity's Primary Key property is updated after the Insert.
    /// </summary>
    /// <typeparam name="T">Entity Type.</typeparam>
    internal class DbEntityInserter<T> {
        private readonly IEntityTable<T> entityTable;
        private readonly Type entityType = typeof(T);
        private readonly MappingEntity mappingEntity;
        private readonly PropertyInfo primaryKey;
        private readonly Func<T, int> insert;
 
        public DbEntityInserter(IEntityTable<T> entityTable, DbEntityProvider provider) {
            if (entityTable == null) {
                throw new ArgumentNullException("entityTable");
            }
 
            this.entityTable = entityTable;
 
            if (provider != null) {
                this.mappingEntity = provider.Mapping.GetEntity(this.entityType);
                this.primaryKey = this.GetPrimaryKey(provider);
            }
 
            this.insert = this.GetInsertFunction();
        }
 
        /// <summary>
        /// Executes the Insert for the Entity.
        /// </summary>
        /// <param name="instance">Entity instance.</param>
        /// <returns>Returns the Insert row count.</returns>
        public int Insert(T instance) {
            return this.insert(instance);
        }
 
        /// <summary>
        /// Inspects the Entity mapping information to determine if the standard Insert statement should be used or if the insert statement should include 
        /// the extra step of retrieving the AutoGenerated primary key.
        /// </summary>
        /// <returns>Returns the Insert function.</returns>
        private Func<T, int> GetInsertFunction() {
            if (this.primaryKey != null && this.IsGeneratedPrimaryKey()) {
                // Create a type array for the generic types for the Insert<T, S> method.
                Type[] genericTypes = new Type[] { this.entityType, this.primaryKey.PropertyType };
 
                LambdaExpression insertExpression = this.GetInsertExpression(genericTypes);
                MethodInfo insertMethod = this.GetInsertMethod(genericTypes);
 
                return instance => {
                    object id = insertMethod.Invoke(null, new object[] { this.entityTable, instance, insertExpression });
 
                    // Set the primary key property to the new Id value.
                    this.entityType.GetProperty(this.primaryKey.Name).SetValue(instance, id);
 
                    // Assuming if we got this far without an exception then the insert worked.
                    return 1;
                };
            }
 
            // Do the insert statement as normal if the Entity doesn't have an AutoGenerated primary key.
            return instance => this.entityTable.Insert(instance);
        }
 
        /// <summary>
        /// Gets a reference to the IQToolkit.Updatable.Insert method.
        /// </summary>
        /// <param name="genericTypes">Generic Types Array for the Expression.</param>
        /// <returns>Returns IQToolkit.Updatable.Insert MethodInfo.</returns>
        private MethodInfo GetInsertMethod(Type[] genericTypes) {
            // MethodInfo for: (IQToolkit.Updatable) public static S Insert<T, S>(this IUpdatable<T> collection, T instance, Expression<Func<T, S>> resultSelector)
            MethodInfo mi = typeof(IQToolkit.Updatable).GetMethods()
                                                       .Where(d => d.Name == "Insert" && d.IsGenericMethod && d.ReturnType.FullName == null)
                                                       .First();
 
            return mi.MakeGenericMethod(genericTypes);
        }
 
        /// <summary>
        /// Creates an Expression that will be passed to the IQToolkit.Updatable.Insert method
        /// </summary>
        /// <param name="genericTypes">Generic Types Array for the Expression.</param>
        /// <returns>Returns an Expression.</returns>
        private LambdaExpression GetInsertExpression(Type[] genericTypes) {
            // Create type for Expression<Func<T, S>>.
            Type functionExpressionType = Expression.GetFuncType(genericTypes);
 
            // Create an Expression Parameter.
            var parameter = Expression.Parameter(this.entityType, "instance");
 
            // Create a Lambda Expression for the Func<T, S> call. 
            // Expression example:  x => x.Id
            return Expression.Lambda(functionExpressionType, Expression.Property(parameter, this.primaryKey.Name), parameter);
        }
 
        /// <summary>
        /// Use reflection to determine if the primary key column was mapped as a generated column.
        /// </summary>
        /// <returns>Returns true if the primary key is generated by the database.</returns>
        private bool IsGeneratedPrimaryKey() {
            // Get a reference to the internal method GetMappingMember.
            MethodInfo getMappingMember = this.mappingEntity.GetType()
                                                             .GetMethod("GetMappingMember", BindingFlags.NonPublic | BindingFlags.Instance);
 
            // Invoke GetMappingMember.
            object mappingMember = getMappingMember.Invoke(this.mappingEntity, new object[] { this.primaryKey.Name });
 
            // Get the Column property from the mappingMember.
            ColumnAttribute columnAttribute = (ColumnAttribute)mappingMember.GetType()
                                                                            .GetProperty("Column", BindingFlags.NonPublic | BindingFlags.Instance)
                                                                            .GetValue(mappingMember);
 
            return columnAttribute.IsGenerated;
        }
 
        /// <summary>
        /// Gets the primary key if the Entity has only one column for the primary key.  The assumption is that composite
        /// primary keys don't need to be retrieved after executing the Insert statement.
        /// </summary>
        /// <param name="provider">DbEntityProvider instance.</param>
        /// <returns>Returns the PrimaryKey PropertyInfo.</returns>
        private PropertyInfo GetPrimaryKey(DbEntityProvider provider) {
            MemberInfo primaryKeyMemberInfo = provider.Mapping
                                                      .GetPrimaryKeyMembers(this.mappingEntity)
                                                      .SingleOrDefault();
 
            return primaryKeyMemberInfo as PropertyInfo;
        }
    }
}

The next step was to hook the DbEntityInserter into the IQToolkit insert process.  This was handled by creating a decorator class that implemented IEntityTable.

Here is the class that I came up with:

using System;
using System.Collections.Generic;
using System.Linq;
using IQToolkit;
using IQToolkit.Data;
 
namespace IQToolkitContrib {
    /// <summary>
    /// This class wraps the actions to the EntityTable to provide a hook into the Insert method.
    /// </summary>
    /// <typeparam name="T">Entity Type.</typeparam>
    internal class DbEntityTable<T> : IEntityTable<T> where T : class {
        private readonly IEntityTable<T> entityTable;
        private DbEntityInserter<T> inserter;
 
        public DbEntityTable(IEntityTable<T> entityTable) {
            if (entityTable == null) {
                throw new ArgumentNullException("entityTable");
            }
 
            this.entityTable = entityTable;
        }
 
        #region IEntityTable<T> Members
 
        public int Delete(T instance) {
            return this.entityTable.Delete(instance);
        }
 
        public T GetById(object id) {
            return this.entityTable.GetById(id);
        }
 
        public int Insert(T instance) {
            if (this.inserter == null) {
                this.inserter = new DbEntityInserter<T>(this.entityTable, this.Provider as DbEntityProvider);
            }
 
            // Using the DbEntityInserter to handle the insert.
            return this.inserter.Insert(instance);
        }
 
        public int InsertOrUpdate(T instance) {
            return this.InsertOrUpdate(instance);
        }
 
        public int Update(T instance) {
            return this.entityTable.Update(instance);
        }
 
        #endregion
 
        #region IEntityTable Members
 
        public int Delete(object instance) {
            return this.entityTable.Delete(instance);
        }
 
        object IEntityTable.GetById(object id) {
            return this.entityTable.GetById(id);
        }
 
        public int Insert(object instance) {
            return this.Insert(instance as T);
        }
 
        public int InsertOrUpdate(object instance) {
            return this.entityTable.InsertOrUpdate(instance);
        }
 
        public IEntityProvider Provider {
            get { return this.entityTable.Provider; }
        }
 
        public string TableId {
            get { return this.entityTable.TableId; }
        }
 
        public int Update(object instance) {
            return this.entityTable.Update(instance);
        }
 
        #endregion
 
        #region IQueryable Members
 
        public Type ElementType {
            get { return this.entityTable.ElementType; }
        }
 
        public System.Linq.Expressions.Expression Expression {
            get { return this.entityTable.Expression; }
        }
 
        IQueryProvider IQueryable.Provider {
            get { return ((IQueryable)this.entityTable).Provider; }
        }
 
        #endregion
 
        #region IEnumerable Members
 
        public System.Collections.IEnumerator GetEnumerator() {
            return this.entityTable.GetEnumerator();
        }
 
        #endregion
 
        #region IEnumerable<T> Members
 
        IEnumerator<T> IEnumerable<T>.GetEnumerator() {
            return ((IEnumerable<T>)this.entityTable).GetEnumerator();
        }
 
        #endregion
    }
}

 

The final step was to simply hook into the Provider’s GetTable method and return an instance of the DbEntityTable.

Here is an example of a base class that I wrote to just that:

using System;
using IQToolkit;
using IQToolkit.Data;
 
namespace IQToolkitContrib {
    public abstract class DbEntityContextBase {
        private readonly DbEntityProvider provider;
 
        public DbEntityProvider Provider {
            get {
                return this.provider;
            }
        }
 
        public DbEntityContextBase(DbEntityProvider provider) {
            if (provider == null) {
                throw new ArgumentNullException("provider");
            }
 
            this.provider = provider;
        }
 
        protected IEntityTable<T> GetTable<T>() where T : class {
            string tableId = this.provider.Mapping.GetTableId(typeof(T));
            IEntityTable<T> table = this.provider.GetTable<T>(tableId);
 
            return new DbEntityTable<T>(table);
        }
    }
}

 

* This base class and a unit of work version can be found in the IQToolkitContrib download.

Blog at WordPress.com.