JW/EMiS.DataLogic/UnitOfWork.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bowin.Common.Data;
using Bowin.Common.Linq;
using Bowin.Common.Linq.Entity;
using EMIS.Entities;
using System.Data.SqlClient;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Diagnostics;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Linq.Expressions;
using System.Reflection;
using EMIS.Utility.Log;
using System.Collections;
using Bowin.Common.Linq.DB;
using System.Configuration;

namespace EMIS.DataLogic
{
    public class UnitOfWork : EMISNewContext, IDisposable
    {
        public UnitOfWork() 
            : base("EMISNewContext")
        {

        }

        public TEntity Add<TEntity>(TEntity entity) where TEntity : class
        {
            return Set<TEntity>().Add(entity);
        }

        public IEnumerable<TEntity> AddRange<TEntity>(IEnumerable<TEntity> entityList) where TEntity : class
        {
            return Set<TEntity>().AddRange(entityList);
        }

        public TEntity Attach<TEntity>(TEntity entity) where TEntity : class
        {
            return Set<TEntity>().Attach(entity);
        }

        /// <summary>
        /// 事务提交
        /// </summary>
        public void Commit()
        {
            var logs = DbLog.GetLog(this.ChangeTracker).Concat(
                        DbLog.GetRelationshipLog(this)).ToList();
            try
            {
                SaveChanges();
                logs.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logs);
            }
            catch (DbEntityValidationException dbEx)
            {
                logs.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logs);
                foreach (var validationErrors in dbEx.EntityValidationErrors)
                {
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                        Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
                    }
                }
                throw dbEx;
            }
            catch (Exception ex)
            {
                logs.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logs);

                throw ex;
            }
        }

        /// <summary>
        /// 创建只有ID字段的临时表 
        /// </summary>
        /// <param name="TempTableName"></param>
        public void CreateIDTempTable(string TempTableName, List<Guid?> Id)
        {
            SqlConnection conn = GetConnection();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            string sql = "";
            StringBuilder sb = new StringBuilder();
            if (Id.Count == 0)
            {
                sb.Append(@"CREATE TABLE [" + TempTableName + "] (ID UNIQUEIDENTIFIER NOT NULL)");
            }
            else
            {
                sb.Append(@"CREATE TABLE [" + TempTableName + "] (ID UNIQUEIDENTIFIER NOT NULL) INSERT INTO [" + TempTableName + "] VALUES");
                foreach (var i in Id)
                {
                    sb.Append("(" + "'" + i + "'" + "),");
                }
                sb.Remove(sb.Length - 1, 1);
            }
            sql = sb.ToString();
            try
            {
                var command = new SqlCommand(sql, conn);
                command.CommandTimeout = 12000;
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 删除只有ID字段的临时表 
        /// </summary>
        /// <param name="TempTableName"></param>
        public void DeleteTempTable(string TempTableName)
        {
            SqlConnection conn = GetConnection();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            string sql = "";
            StringBuilder sb = new StringBuilder();
            sb.Append(@"DROP TABLE [" + TempTableName + "] ");
            sql = sb.ToString();
            try
            {
                var command = new SqlCommand(sql, conn);
                command.CommandTimeout = 12000;
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="data"></param>
        public void BatchUpdate<TEntity>(IList<TEntity> data) where TEntity : class
        {
            var logList = DbLog.GetBatchUpdateLog(data, this);

            string setField = "";
            string keyExpression = "";
            string tableName = typeof(TEntity).Name;
            var properties = typeof(TEntity).GetProperties();
            string tableKey = "";
            PropertyInfo keyProperty;

            try
            {
                tableKey = TableKeyDictionary.GetKeyName<TEntity>();
                keyProperty = typeof(TEntity).GetProperty(tableKey);
            }
            catch
            {
                throw new Exception("批量修改出错，输入的实体没有正确对应的主键，请检查输入。");
            }

            foreach (var property in properties)
            {
                if (property.PropertyType.IsPrimitive || property.PropertyType.IsValueType || property.PropertyType.Name.StartsWith("String"))
                {
                    var field = property.Name;

                    if (setField != "")
                    {
                        setField += ",";
                    }

                    setField += "[" + field + "]" + "=dest.[" + field + "]";
                }
            }

            if (keyProperty.PropertyType.IsPrimitive || keyProperty.PropertyType.IsValueType || keyProperty.PropertyType.Name.StartsWith("String"))
            {
                keyExpression = "source.[" + keyProperty.Name + "]" + "=dest.[" + keyProperty.Name + "]";
            }

            string tmpTableName = "##UpdateTemp" + Guid.NewGuid().ToString().Replace("-", "");

            SqlConnection conn = GetConnection();
            conn.BulkCopyToTempTable(tmpTableName, data);

            string sql = @"update " + tableName + @" set " + setField + @" from " + tableName + @" source inner join " + tmpTableName + @" dest on " + keyExpression;

            try
            {
                var command = new SqlCommand(sql, conn);
                command.CommandTimeout = 12000;
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();

                try
                {
                    command.CommandText = "drop table " + tmpTableName;
                    command.CommandType = System.Data.CommandType.Text;
                    command.ExecuteNonQuery();
                }
                catch { }
                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="data"></param>
        public void BulkInsert<TEntity>(IList<TEntity> data) where TEntity : class
        {
            var logList = DbLog.GetBulkInsertLog(data);
            try
            {
                var db = GetConnection();

                data.ExecuteBulkCopy(db, typeof(TEntity).Name);
                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity1"></typeparam>
        /// <typeparam name="TEntity2"></typeparam>
        /// <param name="data"></param>
        public void BulkInsert<TEntity1, TEntity2>(IList<Relation2KeyTable> data)
        {
            var tableName1 = typeof(TEntity1).Name;
            var tableName2 = typeof(TEntity2).Name;
            var metadata = ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace;
            var associationSet = metadata.GetItemCollection(DataSpace.SSpace)
                .GetItems<EntityContainer>().Single()
                .AssociationSets.Where(x => x.AssociationSetEnds.Any(w => w.Name == tableName1)
                    && (x.AssociationSetEnds.Any(w => w.Name == tableName1 + tableName2) || x.AssociationSetEnds.Any(w => w.Name == tableName2 + tableName1))).FirstOrDefault();
            if (associationSet == null) { return; }
            bool isSourceSet;
            var targetSetProperty = typeof(AssociationSet).GetProperty("TargetSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var sourceSetProperty = typeof(AssociationSet).GetProperty("SourceSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var targetSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(targetSetProperty.GetValue(associationSet, null)));
            var sourceSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(sourceSetProperty.GetValue(associationSet, null)));
            var tableName = targetSet.Table;
            if (tableName == null)
            {
                tableName = sourceSet.Table;

                isSourceSet = false;
                if (tableName == null || tableName == tableName1)
                {
                    return;
                }
            }
            else
            {
                isSourceSet = true;
            }
            var key1 = TableKeyDictionary.GetKeyName<TEntity1>();
            var key2 = TableKeyDictionary.GetKeyName<TEntity2>();
            var keyProperty = typeof(TEntity1).GetProperty(key1);
            var targetKeyPropery = typeof(TEntity2).GetProperty(key2);
            var resultTable = new DataTable(tableName);

            var db = GetConnection();
            var columnList = db.GetTableColumns(tableName);

            if (columnList.First().ToLower() == key1.ToLower())
            {
                resultTable.Columns.Add(key1, typeof(Guid));
                resultTable.Columns.Add(key2, typeof(Guid));
            }
            else
            {
                resultTable.Columns.Add(key2, typeof(Guid));
                resultTable.Columns.Add(key1, typeof(Guid));
            }

            foreach (var dataItem in data)
            {
                var row = resultTable.NewRow();
                row[0] = dataItem.Key1;
                row[1] = dataItem.Key2;
                resultTable.Rows.Add(row);
            }

            var logList = DbLog.GetBulkInsertLog(resultTable, tableName);
            try
            {
                BulkCopyExtensions.ExecuteBulkCopy(db, resultTable);

                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <typeparam name="TProperty"></typeparam>
        /// <param name="data"></param>
        /// <param name="relationShip"></param>
        public void BulkInsert<TEntity, TProperty>(IList<TEntity> data, Expression<Func<TEntity, TProperty>> relationShip)
        {
            if (!typeof(TProperty).FullName.Contains("HashSet"))
            {
                return;
            }
            var tableName1 = typeof(TEntity).Name;
            var targetProperty = (PropertyInfo)((MemberExpression)relationShip.Body).Member;
            var tableName2 = targetProperty.Name;
            var metadata = ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace;
            var associationSet = metadata.GetItemCollection(DataSpace.SSpace)
                .GetItems<EntityContainer>().Single()
                .AssociationSets.Where(x => x.AssociationSetEnds.Any(w => w.Name == tableName1)
                    && (x.AssociationSetEnds.Any(w => w.Name == tableName1 + tableName2) || x.AssociationSetEnds.Any(w => w.Name == tableName2 + tableName1))).FirstOrDefault();
            if (associationSet == null) { return; }
            bool isSourceSet;
            var targetSetProperty = typeof(AssociationSet).GetProperty("TargetSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var sourceSetProperty = typeof(AssociationSet).GetProperty("SourceSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var targetSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(targetSetProperty.GetValue(associationSet, null)));
            var sourceSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(sourceSetProperty.GetValue(associationSet, null)));
            var tableName = targetSet.Table;
            if (tableName == null)
            {
                tableName = sourceSet.Table;

                isSourceSet = false;
                if (tableName == null || tableName == tableName1)
                {
                    return;
                }
            }
            else
            {
                isSourceSet = true;
            }
            var targetElementType = (typeof(TProperty)).GetGenericArguments()[0];
            var key1 = TableKeyDictionary.GetKeyName<TEntity>();
            var key2 = TableKeyDictionary.GetKeyName(targetElementType.Assembly.CreateInstance(targetElementType.FullName));
            var keyProperty = typeof(TEntity).GetProperty(key1);
            var targetKeyPropery = targetElementType.GetProperty(key2);
            var resultTable = new DataTable(tableName);

            var db = GetConnection();
            var columnList = db.GetTableColumns(tableName);

            if (columnList.First().ToLower() == key1.ToLower())
            {
                resultTable.Columns.Add(key1, typeof(Guid));
                resultTable.Columns.Add(key2, typeof(Guid));
            }
            else
            {
                resultTable.Columns.Add(key2, typeof(Guid));
                resultTable.Columns.Add(key1, typeof(Guid));
            }

            foreach (var dataItem in data)
            {
                IEnumerable targetHashset = (IEnumerable)targetProperty.GetValue(dataItem, null);

                foreach (var target in targetHashset)
                {
                    var row = resultTable.NewRow();
                    if (columnList.First().ToLower() == key1.ToLower())
                    {
                        row[0] = (Guid)keyProperty.GetValue(dataItem, null);
                        row[1] = (Guid)targetKeyPropery.GetValue(target, null);
                    }
                    else
                    {
                        row[0] = (Guid)targetKeyPropery.GetValue(target, null);
                        row[1] = (Guid)keyProperty.GetValue(dataItem, null);
                    }
                    resultTable.Rows.Add(row);
                }
            }

            var logList = DbLog.GetBulkInsertLog(resultTable, tableName);
            try
            {
                BulkCopyExtensions.ExecuteBulkCopy(db, resultTable);

                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConnection()
        {
            //var connectionKey = typeof(EMISNewContext).Name;
            ////if (!SqlConnectionManager.IsGlobalConnectionStarted)
            ////{
            ////    SqlConnectionManager.InitConnections();
            ////}
            //return SqlConnectionManager.GetConnection(connectionKey);
            return (SqlConnection)((DbContext)this).Database.Connection;
        }

        public TEntity Remove<TEntity>(TEntity entity) where TEntity : class
        {
            Set<TEntity>().Attach(entity);
            return Set<TEntity>().Remove(entity);
        }

        public void Remove<TEntity>(Expression<Func<TEntity, bool>> where) where TEntity : class
        {
            var entities = this.Set<TEntity>().Where(where).ToList();

            Set<TEntity>().RemoveRange(entities);
        }

        public void RemoveRange<TEntity>(HashSet<TEntity> entities) where TEntity : class
        {
            Set<TEntity>().RemoveRange(entities);
        }

        void IDisposable.Dispose()
        {
            Dispose();
        }

        public void Update<TEntity>(TEntity entity) where TEntity : class
        {
            Entry<TEntity>(entity).State = System.Data.Entity.EntityState.Modified;
        }

        public event Action<object> PostUpdate;

        public void Update<TEntity>(Expression<Func<TEntity, TEntity>> setExpression, Expression<Func<TEntity, bool>> whereExpression) where TEntity : class
        {
            var entities = this.Set<TEntity>().Where(whereExpression).ToList();
            entities.ForEach(x =>
            {
                foreach (var binding in ((MemberInitExpression)setExpression.Body).Bindings)
                {
                    PropertyInfo property = (PropertyInfo)binding.Member;
                    var expression = ((MemberAssignment)binding).Expression;

                    if (expression is ConstantExpression)
                    {
                        property.SetValue(x, ((ConstantExpression)expression).Value, null);
                    }
                    else
                    {
                        property.SetValue(x, Expression.Lambda(expression, setExpression.Parameters.ToArray()).Compile()
                            .DynamicInvoke(x), null);
                    }
                }
            });
            var logList = DbLog.GetBatchUpdateLog(entities, this);

            try
            {
                EntityFramework.Extensions.BatchExtensions.Update(this.Set<TEntity>().Where(whereExpression), setExpression);
                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);
                throw ex;
            }
        }

        public void Delete<TEntity>(Expression<Func<TEntity, bool>> whereExpression) where TEntity : class
        {
            var entities = this.Set<TEntity>().Where(whereExpression).ToList();
            if (entities.Count == 0) return;
            var logList = DbLog.GetDeleteLog(entities);

            try
            {
                ((DbContext)this).Delete(whereExpression);

                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
            //entities.ForEach(x => this.Remove(x));

            //this.Commit();
        }

        public void Delete<TEntity>(IList<TEntity> entityList) where TEntity : class
        {
            if (entityList.Count == 0) return;
            var tableName = typeof(TEntity).Name;
            var tableKey = TableKeyDictionary.GetKeyName<TEntity>();
            var logList = DbLog.GetDeleteLog(Bowin.Common.Data.ConvertToDataTable.ToTable(entityList), tableName);
            try
            {
                var db = GetConnection();
                string tmpTableName = "##DeleteTemp" + Guid.NewGuid().ToString().Replace("-", "");
                db.BulkCopyToTempTable(tmpTableName, entityList);
                string sql = @"
                delete t 
                from " + tableName + @" t 
                inner join " + tmpTableName + @" tmp on 
                    t.[" + tableKey + "]=tmp.[" + tableKey + "]";

                var command = new SqlCommand(sql, db);
                command.CommandTimeout = 12000;
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();

                command.CommandText = "drop table " + tmpTableName;
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();

                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
        }

        public void Delete<TEntity, TProperty>(IList<TEntity> entityList, Expression<Func<TEntity, TProperty>> relationShip)
        {
            if (entityList.Count == 0) return;
            if (!typeof(TProperty).FullName.Contains("HashSet"))
            {
                return;
            }
            var tableName1 = typeof(TEntity).Name;
            var targetProperty = (PropertyInfo)((MemberExpression)relationShip.Body).Member;
            var tableName2 = targetProperty.Name;
            var metadata = ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace;
            var associationSet = metadata.GetItemCollection(DataSpace.SSpace)
                .GetItems<EntityContainer>().Single()
                .AssociationSets.Where(x => x.AssociationSetEnds.Any(w => w.Name == tableName1)
                    && (x.AssociationSetEnds.Any(w => w.Name == tableName1 + tableName2) || x.AssociationSetEnds.Any(w => w.Name == tableName2 + tableName1))).FirstOrDefault();
            if (associationSet == null) { return; }
            bool isSourceSet;
            var targetSetProperty = typeof(AssociationSet).GetProperty("TargetSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var sourceSetProperty = typeof(AssociationSet).GetProperty("SourceSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var targetSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(targetSetProperty.GetValue(associationSet, null)));
            var sourceSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(sourceSetProperty.GetValue(associationSet, null)));
            var tableName = targetSet.Table;
            if (tableName == null)
            {
                tableName = sourceSet.Table;

                isSourceSet = false;
                if (tableName == null || tableName == tableName1)
                {
                    return;
                }
            }
            else
            {
                isSourceSet = true;
            }
            var targetElementType = (typeof(TProperty)).GetGenericArguments()[0];
            var key1 = TableKeyDictionary.GetKeyName<TEntity>();
            var key2 = TableKeyDictionary.GetKeyName(targetElementType.Assembly.CreateInstance(targetElementType.FullName));
            var keyProperty = typeof(TEntity).GetProperty(key1);
            var targetKeyPropery = targetElementType.GetProperty(key2);
            var resultTable = new DataTable(tableName);

            if (isSourceSet)
            {
                resultTable.Columns.Add(key1, typeof(Guid));
                resultTable.Columns.Add(key2, typeof(Guid));
            }
            else
            {
                resultTable.Columns.Add(key2, typeof(Guid));
                resultTable.Columns.Add(key1, typeof(Guid));
            }

            List<Guid> keyList = new List<Guid>();
            foreach (var entity in entityList)
            {
                IEnumerable targetHashset = (IEnumerable)targetProperty.GetValue(entity, null);

                foreach (var target in targetHashset)
                {
                    var row = resultTable.NewRow();
                    if (isSourceSet)
                    {
                        row[0] = (Guid)keyProperty.GetValue(entity, null);
                        row[1] = (Guid)targetKeyPropery.GetValue(target, null);
                    }
                    else
                    {
                        row[0] = (Guid)targetKeyPropery.GetValue(target, null);
                        row[1] = (Guid)keyProperty.GetValue(entity, null);
                    }
                    keyList.Add((Guid)keyProperty.GetValue(entity, null));
                    resultTable.Rows.Add(row);
                }
            }

            var logList = DbLog.GetDeleteLog(resultTable, tableName);
            try
            {
                var db = GetConnection();
                string tmpTableName = "##DeleteTemp" + Guid.NewGuid().ToString().Replace("-", "");
                db.BulkCopyToTempTable(tmpTableName, keyList, keyProperty.Name);
                string sql = @"
                delete t 
                from " + tableName + @" t 
                inner join " + tmpTableName + @" tmp on 
                    t.[" + keyProperty.Name + "]=tmp.[" + keyProperty.Name + "]";

                var command = new SqlCommand(sql, db);
                command.CommandTimeout = 12000;
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();

                command.CommandText = "drop table " + tmpTableName;
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();

                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
        }

        public void Delete<TEntity, TProperty>(TEntity entity, Expression<Func<TEntity, TProperty>> relationShip)
        {
            if (entity == null) return;
            if (!typeof(TProperty).FullName.Contains("HashSet"))
            {
                return;
            }
            var tableName1 = typeof(TEntity).Name;
            var targetProperty = (PropertyInfo)((MemberExpression)relationShip.Body).Member;
            var tableName2 = targetProperty.Name;
            var metadata = ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace;
            var associationSet = metadata.GetItemCollection(DataSpace.SSpace)
                .GetItems<EntityContainer>().Single()
                .AssociationSets.Where(x => x.AssociationSetEnds.Any(w => w.Name == tableName1)
                    && (x.AssociationSetEnds.Any(w => w.Name == tableName1 + tableName2) || x.AssociationSetEnds.Any(w => w.Name == tableName2 + tableName1))).FirstOrDefault();
            if (associationSet == null) { return; }
            bool isSourceSet;
            var targetSetProperty = typeof(AssociationSet).GetProperty("TargetSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var sourceSetProperty = typeof(AssociationSet).GetProperty("SourceSet", BindingFlags.Instance | BindingFlags.NonPublic);
            var targetSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(targetSetProperty.GetValue(associationSet, null)));
            var sourceSet = ((System.Data.Entity.Core.Metadata.Edm.EntitySet)(sourceSetProperty.GetValue(associationSet, null)));
            var tableName = targetSet.Table;
            if (tableName == null)
            {
                tableName = sourceSet.Table;

                isSourceSet = false;
                if (tableName == null || tableName == tableName1)
                {
                    return;
                }
            }
            else
            {
                isSourceSet = true;
            }
            var targetElementType = (typeof(TProperty)).GetGenericArguments()[0];
            var key1 = TableKeyDictionary.GetKeyName<TEntity>();
            var key2 = TableKeyDictionary.GetKeyName(targetElementType.Assembly.CreateInstance(targetElementType.FullName));
            var keyProperty = typeof(TEntity).GetProperty(key1);
            var targetKeyPropery = targetElementType.GetProperty(key2);
            var resultTable = new DataTable(tableName);

            if (isSourceSet)
            {
                resultTable.Columns.Add(key1, typeof(Guid));
                resultTable.Columns.Add(key2, typeof(Guid));
            }
            else
            {
                resultTable.Columns.Add(key2, typeof(Guid));
                resultTable.Columns.Add(key1, typeof(Guid));
            }

            IEnumerable targetHashset = (IEnumerable)targetProperty.GetValue(entity, null);

            foreach (var target in targetHashset)
            {
                var row = resultTable.NewRow();
                if (isSourceSet)
                {
                    row[0] = (Guid)keyProperty.GetValue(entity, null);
                    row[1] = (Guid)targetKeyPropery.GetValue(target, null);
                }
                else
                {
                    row[0] = (Guid)targetKeyPropery.GetValue(target, null);
                    row[1] = (Guid)keyProperty.GetValue(entity, null);
                }
                resultTable.Rows.Add(row);
            }

            var logList = DbLog.GetDeleteLog(resultTable, tableName);
            try
            {
                var db = GetConnection();
                db.Delete(tableName, key1, (Guid)keyProperty.GetValue(entity, null));

                logList.ForEach(x => x.IsSuccess = true);
                LogUnitOfWork.WriteLogs(logList);
            }
            catch (Exception ex)
            {
                logList.ForEach(x => x.IsSuccess = false);
                LogUnitOfWork.WriteLogs(logList);

                throw ex;
            }
        }

        int ExecuteSQL(string sql, params object[] parameters)
        {
            return this.Database.ExecuteSqlCommand(sql, parameters);
        }

        public void BatchUpdate<TKey>(string tableName, string columnName, object value, IList<TKey> idList)
        {
            var assembly = typeof(EMISNewContext).Assembly;
            var entityType = assembly.GetTypes().Where(x => x.Name.ToLower() == tableName.ToLower()).FirstOrDefault();
            var methodInfo = typeof(UnitOfWork).GetMethods()
                .Where(x => x.ContainsGenericParameters && x.GetGenericArguments().Length == 2 && x.Name == "BatchUpdate"
                && x.GetParameters().Length == 3).FirstOrDefault();


            methodInfo = methodInfo
                .MakeGenericMethod(entityType, typeof(TKey));

            methodInfo.Invoke(this, new object[] { columnName, value, idList });
        }

        public void BatchUpdate<TEntity, TKey>(string columnName, object value, IList<TKey> idList) where TEntity : class
        {
            var curUser = EMIS.Utility.FormValidate.CustomPrincipal.Current;
            ExpressionFactory<TEntity> factory = new ExpressionFactory<TEntity>(Set<TEntity>());
            var para = Expression.Parameter(typeof(TEntity));

            var property = typeof(TEntity).GetProperty(columnName);
            var modifytime = typeof(TEntity).GetProperty("ModifyTime");
            var modifyuser = typeof(TEntity).GetProperty("ModifyUserID");
            object timevalue = DateTime.Now;
            object uservalue = curUser.UserID;
            MemberInitExpression memberExpression;
            if (modifytime != null)
            {
                var newExpression = Expression.New(typeof(TEntity).GetConstructor(new Type[] { }));
                var bindExpression = Expression.Bind(property, GetValueExpression(value, property));
                var timebindExpression = Expression.Bind(modifytime, GetValueExpression(timevalue, modifytime));
                var userbindExpression = Expression.Bind(modifyuser, GetValueExpression(uservalue, modifyuser));
                memberExpression = Expression.MemberInit(newExpression, new MemberBinding[] { bindExpression, timebindExpression, userbindExpression });
            }
            else
            {
                var newExpression = Expression.New(typeof(TEntity).GetConstructor(new Type[] { }));
                var bindExpression = Expression.Bind(property, GetValueExpression(value, property));
                memberExpression = Expression.MemberInit(newExpression, new MemberBinding[] { bindExpression });
            }
            Expression<Func<TEntity, TEntity>> setExpression = Expression.Lambda<Func<TEntity, TEntity>>(memberExpression, para);
            Expression<Func<TEntity, bool>> whereExpression = factory.DynamicInExpression(TableKeyDictionary.GetKeyName<TEntity>(), idList);
            this.Update(setExpression, whereExpression);
        }

        public void BatchUpdate<TKey>(string tableName, IDictionary<string, object> valueList, IList<TKey> idList)
        {
            var assembly = typeof(EMISNewContext).Assembly;
            var entityType = assembly.GetTypes().Where(x => x.Name.ToLower() == tableName.ToLower()).FirstOrDefault();
            var methodInfo = typeof(UnitOfWork).GetMethods()
                .Where(x => x.ContainsGenericParameters && x.GetGenericArguments().Length == 2 && x.Name == "BatchUpdate"
                && x.GetParameters().Length == 2).FirstOrDefault();


            methodInfo = methodInfo
                .MakeGenericMethod(entityType, typeof(TKey));

            methodInfo.Invoke(this, new object[] { valueList, idList });
        }

        public void BatchUpdate<TEntity, TKey>(IDictionary<string, object> valueList, IList<TKey> idList) where TEntity : class
        {
            var curUser = EMIS.Utility.FormValidate.CustomPrincipal.Current;
            ExpressionFactory<TEntity> factory = new ExpressionFactory<TEntity>(Set<TEntity>());
            var para = Expression.Parameter(typeof(TEntity));
            var newExpression = Expression.New(typeof(TEntity).GetConstructor(new Type[] { }));
            var memberBindingList = new List<MemberBinding>();
            foreach (var keyValue in valueList)
            {
                var property = typeof(TEntity).GetProperty(keyValue.Key);

                var bindExpression = Expression.Bind(property, GetValueExpression(keyValue.Value, property));
                memberBindingList.Add(bindExpression);
            }
            var modifytime = typeof(TEntity).GetProperty("ModifyTime");
            var modifyuser = typeof(TEntity).GetProperty("ModifyUserID");
            if (modifytime != null)
            {
                object timevalue = DateTime.Now;
                var timebindExpression = Expression.Bind(modifytime, GetValueExpression(timevalue, modifytime));
                memberBindingList.Add(timebindExpression);
            }
            if (modifyuser != null)
            {
                object uservalue = curUser.UserID;
                var userbindExpression = Expression.Bind(modifyuser, GetValueExpression(uservalue, modifyuser));
                memberBindingList.Add(userbindExpression);
            }
            var memberExpression = Expression.MemberInit(newExpression, memberBindingList.ToArray());

            Expression<Func<TEntity, TEntity>> setExpression = Expression.Lambda<Func<TEntity, TEntity>>(memberExpression, para);
            Expression<Func<TEntity, bool>> whereExpression = factory.DynamicInExpression(TableKeyDictionary.GetKeyName<TEntity>(), idList);
            this.Update(setExpression, whereExpression);
        }

        private Expression GetValueExpression(object value, PropertyInfo property)
        {
            if (property.PropertyType == typeof(Guid) || property.PropertyType == typeof(Guid?))
            {
                return Expression.Constant(Guid.Parse(value.ToString()));
            }
            else if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))
            {
                return Expression.Constant(int.Parse(value.ToString()));
            }
            else if (property.PropertyType == typeof(short) || property.PropertyType == typeof(short?))
            {
                return Expression.Constant(short.Parse(value.ToString()));
            }
            else if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(bool?))
            {
                if (value.Equals("1") || value.Equals("0"))
                    return Expression.Constant(value.Equals("1"));
                return Expression.Constant(bool.Parse(value.ToString()));
            }
            else if (property.PropertyType == typeof(decimal) || property.PropertyType == typeof(decimal?))
            {
                return Expression.Constant(decimal.Parse(value.ToString()));
            }
            else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
            {
                return Expression.Constant(DateTime.Parse(value.ToString()));
            }
            else
            {
                return Expression.Convert(Expression.Constant(value), property.PropertyType);
            }
        }

        public DataSet QuerySQL(string sql,List<SqlParameter> sParams) {

            var db = GetConnection();
            if (db.State == ConnectionState.Closed)
            {
                db.Open();
            }

            DataSet ds = new DataSet();

            try
            {
                SqlCommand comm = new SqlCommand(sql, db);

                if (sParams != null && sParams.Count > 0)
                {
                    sParams.ForEach(p =>
                    {
                        comm.Parameters.Add(p);
                    });
                }

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = comm;

                
                adapter.Fill(ds);
                //adapter.
                
            }
            catch(Exception ex)
            {
                var a = ex;
            }

            return ds;
        }
    }

    public class Relation2KeyTable
    {
        public Guid Key1 { get; set; }
        public Guid Key2 { get; set; }
    }

}