XYY.Core/1.Command/XYY.Core.Standard/Data/Dapper/SqlMapperExtensions.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Collections.Concurrent;
using System.Reflection.Emit;
using System.Threading;
using System.Runtime.CompilerServices;
using System.Linq.Expressions;
using System.Data.SqlClient;
using System.Configuration;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Common.Standard;
using XYY.Common.Standard.Attributes;
using KeyAttribute = System.ComponentModel.DataAnnotations.KeyAttribute;
using XYY.Core;
using System.Threading.Tasks;

namespace Dapper
{

    public static class SqlMapperExtensions
    {
        public enum Operate
        {
            //等于
            _Eq_,
            /// <summary>
            /// 日期等于
            /// </summary>
            _DataEq_,
            //不等于
            _NotEq_,
            //任意匹配
            _Co_,
            //小于
            _Le_,
            //大于
            _Gr_,
            //小于等于
            _LeE_,
            //大于等于
            _GrE_,
            //IN
            _In_,
            //NOT IN
            _NotIn_,
            _IsNlNumGr_,
            _IsNlNumLe_,
            _IsNotNl_,
            _IsNl_
        }

        public interface IProxy
        {
            bool IsDirty { get; set; }
        }

        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> GetQueries = new ConcurrentDictionary<RuntimeTypeHandle, string>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> TypeTableName = new ConcurrentDictionary<RuntimeTypeHandle, string>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> ComputedProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static IEnumerable<PropertyInfo> KeyPropertiesCache(Type type)
        {
            if (KeyProperties.ContainsKey(type.TypeHandle))
            {
                return KeyProperties[type.TypeHandle];
            }

            var allProperties = TypePropertiesCache(type);
            var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList();

            if (keyProperties.Count == 0)
            {
                var idProp = allProperties.Where(p => p.Name.ToLower() == "id").FirstOrDefault();
                if (idProp != null)
                {
                    keyProperties.Add(idProp);
                }
            }

            KeyProperties[type.TypeHandle] = keyProperties;
            return keyProperties;
        }

        private static IEnumerable<PropertyInfo> ComputedPropertiesCache(Type type)
        {
            if (ComputedProperties.ContainsKey(type.TypeHandle))
            {
                return ComputedProperties[type.TypeHandle];
            }
            var allProperties = TypePropertiesCache(type);
            var computedProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is NoDbAttribute)).ToList();
            ComputedProperties[type.TypeHandle] = computedProperties;
            return computedProperties;
        }

        private static IEnumerable<PropertyInfo> TypePropertiesCache(Type type)
        {
            if (TypeProperties.ContainsKey(type.TypeHandle))
            {
                return TypeProperties[type.TypeHandle];
            }

            var properties = type.GetProperties();
            TypeProperties[type.TypeHandle] = properties;
            return properties;
        }

        /// <summary>
        /// Returns a single entity by a single id from table "Ts". T must be of interface type. 
        /// Id must be marked with [Key] attribute.
        /// Created entity is tracked/intercepted for changes and used by the Update() extension. 
        /// </summary>
        /// <typeparam name="T">Interface type to create and populate</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
        /// <returns>Entity of T</returns>
        public static async Task<T> GetAsync<T>(this IUnitOfWork unitOfWork, dynamic id, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            //if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            //{
            var keys = KeyPropertiesCache(type);
            if (keys.Count() > 1)
                throw new DataException("Get<T> only supports an entity with a single [Key] property");
            if (keys.Count() == 0)
                throw new DataException("Get<T> only supports en entity with a [Key] property");

            var onlyKey = keys.First();

            var name = GetTableName(type);

            // TODO: pluralizer 
            // TODO: query information schema and only select fields that are both in information schema and underlying class / interface 
            sql = "select * from " + name + "(nolock) where " + onlyKey.Name + " = @id";
            //    GetQueries[type.TypeHandle] = sql;
            //}
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var dynParms = new DynamicParameters();
            dynParms.Add("@id", id);

            T obj = null;

            if (type.IsInterface)
            {
                var r = await unitOfWork.Connection.QueryAsync(sql, dynParms);
                var res = r.FirstOrDefault() as IDictionary<string, object>;

                if (res == null)
                    return (T)((object)null);

                obj = ProxyGenerator.GetInterfaceProxy<T>();

                foreach (var property in TypePropertiesCache(type))
                {
                    var val = res[property.Name];
                    property.SetValue(obj, val, null);
                }

                ((IProxy)obj).IsDirty = false;   //reset change tracking and return
            }
            else
            {
                var r = await unitOfWork.Connection.QueryAsync<T>(sql, dynParms, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
                obj = r.FirstOrDefault();
            }
            return obj;
        }

        private static string TranslateWhere(List<QueryParamer> paramer)
        {
            string where = "";
            foreach (var item in paramer)
            {
                Operate operate = (Operate)Enum.Parse(typeof(Operate), string.Format("{0}{1}{0}", "_", item.Method));

                switch (operate)
                {
                    case Operate._Eq_:
                        where += string.Format(" AND {0} = N'{1}' ", item.Filed.Replace("$$", "."), item.Value);
                        break;
                    case Operate._DataEq_:
                        where += string.Format(" AND CONVERT(VARCHAR(8),{0},112) = '{1}' ", item.Filed, item.Value.Replace("-", ""));
                        break;
                    case Operate._NotEq_:
                        where += string.Format(" AND {0} != N'{1}' ", item.Filed, item.Value);
                        break;
                    case Operate._Co_:
                        where += string.Format(" AND {0} like '%{1}%' ", item.Filed, item.Value);
                        break;
                    case Operate._Le_:
                        if (item.Value.IsMatch("^(-?[0-9]*[.]*[0-9]{0,3})$"))
                        {
                            where += string.Format(" AND {0} < {1} ", item.Filed, item.Value);
                        }
                        else
                        {
                            where += string.Format(" AND {0} < '{1}' ", item.Filed, item.Value);
                        }
                        break;
                    case Operate._Gr_:
                        if (item.Value.IsMatch("^(-?[0-9]*[.]*[0-9]{0,3})$"))
                        {
                            where += string.Format(" AND {0} > {1} ", item.Filed, item.Value);
                        }
                        else
                        {
                            where += string.Format(" AND {0} > '{1}' ", item.Filed, item.Value);
                        }
                        break;
                    case Operate._LeE_:
                        if (item.Value.IsMatch("^(-?[0-9]*[.]*[0-9]{0,3})$"))
                        {
                            where += string.Format(" AND {0} <= {1} ", item.Filed, item.Value);
                        }
                        if (item.Value.IsMatch("\\d{4}-\\d{2}-\\d{2}"))
                        {
                            where += string.Format(" AND {0} <= '{1} 23:59:59.999'", item.Filed, item.Value);
                        }
                        else
                        {
                            where += string.Format(" AND {0} <= '{1}' ", item.Filed, item.Value);
                        }
                        break;
                    case Operate._GrE_:
                        if (item.Value.IsMatch("^(-?[0-9]*[.]*[0-9]{0,3})$"))
                        {
                            where += string.Format(" AND {0} >= {1} ", item.Filed, item.Value);
                        }
                        if (item.Value.IsMatch("\\d{4}-\\d{2}-\\d{2}"))
                        {
                            where += string.Format(" AND {0} >= '{1} 00:00:00.000'", item.Filed, item.Value);
                        }
                        else
                        {
                            where += string.Format(" AND {0} >= '{1}' ", item.Filed, item.Value);
                        }
                        break;
                    case Operate._In_:
                        if (item.Value.IsMatch("^\\d$"))
                        {
                            where += string.Format(" AND {0} in ( {1} )", item.Filed, item.Value);
                        }
                        else
                        {
                            if (item.Value.IndexOf(',') > -1)
                            {
                                string value = item.Value.Trim(',');
                                if (value != string.Empty)
                                {
                                    where += string.Format(" AND {0} in ( '{1}' )", item.Filed, value.Replace(",", "','"));
                                }
                            }
                            else if (item.Value.IndexOf('\n') > -1)
                            {
                                string value = item.Value.Trim('\n');
                                if (value != string.Empty)
                                {
                                    where += string.Format(" AND {0} in ( '{1}' )", item.Filed, value.Replace("\n", "','"));
                                }
                            }
                            else
                            {
                                if (item.Value != string.Empty)
                                {
                                    where += string.Format(" AND {0} in ( '{1}' )", item.Filed, item.Value);
                                }
                            }
                        }
                        break;
                    case Operate._NotIn_:
                        where += string.Format(" AND {0} not in ( {1} )", item.Filed, item.Value);
                        break;
                    case Operate._IsNlNumGr_:
                        where += string.Format(" AND isnull({0},0) >=  {1} ", item.Filed, item.Value);
                        break;
                    case Operate._IsNlNumLe_:
                        where += string.Format(" AND isnull({0},0) <=  {1} ", item.Filed, item.Value);
                        break;
                    case Operate._IsNotNl_:
                        where += string.Format(" AND isnull({0},'') !=  '{1}' ", item.Filed, item.Value);
                        break;
                    case Operate._IsNl_:
                        where += string.Format(" AND isnull({0},'') = '{1}' ", item.Filed, item.Value);
                        break;
                }
            }
            return where;
        }

        public static string GetWhere(this IUnitOfWork unitOfWork, List<QueryParamer> paramer)
        {
            return TranslateWhere(paramer);
        }

        /// <summary>
        /// 查询分页数据(优化)
        /// 该方法必须包含主键
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="unitOfWork"></param>
        /// <param name="queryModel"></param>
        /// <param name="commandTimeount"></param>
        /// <returns></returns>
        public static async Task<PageResult<T>> GetPagingList2Async<T>(this IUnitOfWork unitOfWork, QueryModel queryModel, int? commandTimeount = null) where T : class
        {
            var type = typeof(T);
            string sql;
            string tableName = GetTableName(type);

            sql = @"select * from ( select Row_Number()over(order by {0})rowId,{4} from  " + tableName + @"(nolock) {1})T   
	join " + tableName + @" b on t.{4} = b.{4} where rowId between  {2} and {3};
       select count(0) from " + tableName + @"(nolock)  {1}";
            if (string.IsNullOrEmpty(queryModel.Key))
                queryModel.Key = "Id";
            //sql = @"select * from ( select Row_Number()over(order by {0})rowId, * from " + tableName + @"(nolock) {1} )T where rowId between {2} and {3};
            //               select count(0) from " + tableName + @"(nolock)  {1}";

            string whereString = "WHERE 1=1 ";
            string sortString = "";
            //Where
            if (queryModel.QueryParamer != null && queryModel.QueryParamer.Count > 0)
            {
                var where = TranslateWhere(queryModel.QueryParamer);
                if (!string.IsNullOrWhiteSpace(where))
                {
                    whereString += where;
                }
            }
            if (queryModel.DataPermission != null && queryModel.DataPermission.Count > 0)
            {//存在数据权限
                var allProperties = TypePropertiesCache(type);
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    var identity = property.GetCustomAttribute<DataPermissionAttribute>();
                    if (identity == null)
                        continue;

                    var permission = queryModel.DataPermission.Find(x => x.PermissioinType == identity.PermissionType.GetValue());
                    if (permission == null)
                        continue;
                    if (string.IsNullOrWhiteSpace(permission.Value))
                        continue;

                    whereString += " AND {0} IN ({1})".Formater(property.Name, permission.Value.TrimEnd(','));

                }
            }
            //if (expression != null)
            //{
            //    var translator = new QueryTranslator();
            //    whereString = " where " + translator.Translate(expression);
            //}
            //Sort
            if (queryModel.QuerySort == null || queryModel.QuerySort.Count == 0)
            {
                var keys = KeyPropertiesCache(type);
                if (keys.Count() == 0)
                    throw new DataException("查询对象中必须包含主键");
                sortString = keys.First().Name;
            }
            else
            {
                foreach (var item in queryModel.QuerySort)
                {
                    sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
                }
                sortString = sortString.TrimEnd(',');
            }
            sql = string.Format(sql, sortString, whereString, (queryModel.PageIndex - 1) * queryModel.PageSize + 1, queryModel.PageSize * queryModel.PageIndex, queryModel.Key);
            var grid = await unitOfWork.Connection.QueryMultipleAsync(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeount);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return new PageResult<T>()
            {
                Result = grid.Read<T>(),
                TotalCount = grid.Read<int>().SingleOrDefault(),
                PageIndex = queryModel.PageIndex,
                PageSize = queryModel.PageIndex
            };
        }

        public static async Task<PageResult<T>> GetPagingListAsync<T>(this IUnitOfWork unitOfWork, QueryModel queryModel, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            string tableName = GetTableName(type);
            sql = @"select * from ( select Row_Number()over(order by {0})rowId, * from " + tableName + @"(nolock) {1} )T where rowId between {2} and {3};
                           select count(0) from " + tableName + @"(nolock)  {1}";

            string whereString = "WHERE 1=1 ";
            string sortString = "";
            //Where
            if (queryModel != null && queryModel.QueryParamer != null && queryModel.QueryParamer.Count > 0)
            {
                var where = TranslateWhere(queryModel.QueryParamer);
                if (!string.IsNullOrWhiteSpace(where))
                {
                    whereString += where;
                }
            }
            if (queryModel.DataPermission != null && queryModel.DataPermission.Count > 0)
            {//存在数据权限
                var allProperties = TypePropertiesCache(type);
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    var identity = property.GetCustomAttribute<DataPermissionAttribute>();
                    if (identity == null)
                        continue;

                    var permission = queryModel.DataPermission.Find(x => x.PermissioinType == identity.PermissionType.GetValue());
                    if (permission == null)
                        continue;
                    if (string.IsNullOrWhiteSpace(permission.Value))
                        continue;

                    whereString += " AND {0} IN ({1})".Formater(property.Name, permission.Value.TrimEnd(','));

                }
            }
            //if (expression != null)
            //{
            //    var translator = new QueryTranslator();
            //    whereString = " where " + translator.Translate(expression);
            //}
            //Sort
            if (queryModel.QuerySort == null || queryModel.QuerySort.Count == 0)
            {
                var keys = KeyPropertiesCache(type);
                if (keys.Count() == 0)
                    throw new DataException("查询对象中必须包含主键");
                sortString = keys.First().Name;
            }
            else
            {
                foreach (var item in queryModel.QuerySort)
                {
                    sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
                }
                sortString = sortString.TrimEnd(',');
            }
            sql = string.Format(sql, sortString, whereString, (queryModel.PageIndex - 1) * queryModel.PageSize + 1, queryModel.PageSize * queryModel.PageIndex);
            var grid = await unitOfWork.Connection.QueryMultipleAsync(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return new PageResult<T>()
            {
                Result = grid.Read<T>(),
                TotalCount = grid.Read<int>().SingleOrDefault(),
                PageIndex = queryModel.PageIndex,
                PageSize = queryModel.PageIndex
            };
        }

        public static async Task<IEnumerable<T>> QueryBySqlAsync<T>(this IUnitOfWork unitOfWork, string sql, int? commandTimeout = null, object param = null) where T : class
        {
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return await unitOfWork.Connection.QueryAsync<T>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout, param: param);
        }

        public static async Task<T> ExecuteScalarAsync<T>(this IUnitOfWork unitOfWork, string sql, int? commandTimeout = null) where T : class
        {
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return await unitOfWork.Connection.ExecuteScalarAsync<T>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }
        public static async Task ExecuteAsync(this IUnitOfWork unitOfWork, string sql)
        {
            await ExecuteAsync(unitOfWork, sql, null);
        }
        public static async Task ExecuteAsync(this IUnitOfWork unitOfWork, string sql, object obj)
        {
            await unitOfWork.Connection.ExecuteAsync(sql, obj, unitOfWork.Transaction);
        }

        public static async Task<PageResult<T>> GetPagingListAsync<T>(this IUnitOfWork unitOfWork, QueryModel queryModel, string customSql, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            string tableName = GetTableName(type);
            sql = @"select * from ( select Row_Number()over(order by {0})rowId, * from ( " + customSql + " ) " + tableName + @" {1} )T where rowId between {2} and {3};
                           select count(0) from ( " + customSql + " ) " + tableName + @"  {1}";
            string whereString = " WHERE 1=1 ";
            string sortString = "";
            //Where
            if (queryModel.QueryParamer != null && queryModel.QueryParamer.Count > 0)
            {
                var where = TranslateWhere(queryModel.QueryParamer);
                if (!string.IsNullOrWhiteSpace(where))
                {
                    whereString += where;
                }
            }
            //if (expression != null)
            //{
            //    var translator = new QueryTranslator();
            //    whereString = " where " + translator.Translate(expression);
            //}
            //Sort
            if (queryModel.QuerySort == null || queryModel.QuerySort.Count == 0)
            {
                var keys = KeyPropertiesCache(type);
                if (keys.Count() == 0)
                    throw new DataException("查询对象中必须包含主键");
                sortString = keys.First().Name;
            }
            else
            {
                foreach (var item in queryModel.QuerySort)
                {
                    sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
                }
                sortString = sortString.TrimEnd(',');
            }
            sql = string.Format(sql, sortString, whereString, (queryModel.PageIndex - 1) * queryModel.PageSize + 1, queryModel.PageSize * queryModel.PageIndex);
            var grid = await unitOfWork.Connection.QueryMultipleAsync(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return new PageResult<T>()
            {
                Result = grid.Read<T>(),
                TotalCount = grid.Read<int>().SingleOrDefault(),
                PageIndex = queryModel.PageIndex,
                PageSize = queryModel.PageIndex
            };
        }


        public static async Task<bool> IsExistsAsync<T>(this IUnitOfWork unitOfWork, Expression<Func<T, bool>> expression, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            //if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            //{

            //TableName
            string tableName = GetTableName(type);

            sql = @"if exists(select 1 from " + tableName + @"(nolock) {0} ) 
                                        select 1; 
                                      select 0 ";

            //    GetQueries[type.TypeHandle] = sql;
            //}
            string whereString = "";
            //Where
            if (expression != null)
            {
                var translator = new QueryTranslator();
                whereString = " where " + translator.Translate(expression);
            }
            sql = string.Format(sql, whereString);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var r = await unitOfWork.Connection.QueryAsync<bool>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            return r.Single();
        }

        public static async Task<IEnumerable<T>> QueryAsync<T>(this IUnitOfWork unitOfWork, Expression<Func<T, bool>> expression, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            var name = GetTableName(type);
            sql = "select * from " + name + "(nolock) where {0}";
            var translator = new QueryTranslator();
            string where = translator.Translate(expression);
            sql = string.Format(sql, where);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return await unitOfWork.Connection.QueryAsync<T>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }
        public static async Task<IEnumerable<T>> QueryAsync<T>(this IUnitOfWork unitOfWork, Expression<Func<T, bool>> expression, string customSql, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            //if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            //{
            var name = GetTableName(type);
            sql = "select * from ( " + customSql + " ) " + name + " where {0}";
            //GetQueries[type.TypeHandle] = sql;
            //}
            var translator = new QueryTranslator();
            string where = translator.Translate(expression);
            sql = string.Format(sql, where);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return await unitOfWork.Connection.QueryAsync<T>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }


        public static async Task<int> CountAsync<T>(this IUnitOfWork unitOfWork, Expression<Func<T, bool>> expression, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql = string.Empty;
            //if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            //{
            var name = GetTableName(type);


            sql = "select count(0) from " + name + "(nolock) where {0}";


            var translator = new QueryTranslator();
            string where = translator.Translate(expression);
            sql = string.Format(sql, where);

            if (unitOfWork.DataPermission != null && unitOfWork.DataPermission.Count > 0)
            {//存在数据权限
                var allProperties = TypePropertiesCache(typeof(T));
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    var identity = property.GetCustomAttribute<DataPermissionAttribute>();
                    if (identity == null)
                        continue;

                    var permission = unitOfWork.DataPermission.Find(x => x.PermissioinType == identity.PermissionType.GetValue());
                    if (permission == null)
                        continue;
                    if (string.IsNullOrWhiteSpace(permission.Value))
                        continue;

                    sql += " AND {0} IN ({1})".Formater(property.Name, permission.Value.TrimEnd(','));

                }
            }
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return await unitOfWork.Connection.ExecuteScalarAsync<int>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="unitOfWork"></param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="param">参数列表</param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static async Task<object> ExecuteStoredProcedureAsync(this IUnitOfWork unitOfWork, string procName, object param = null, int? commandTimeout = null)
        {
            return await unitOfWork.Connection.ExecuteScalarAsync(procName, param, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout, commandType: CommandType.StoredProcedure);
        }

        public static async Task<IEnumerable<T>> QueryAsync<T>(this IUnitOfWork unitOfWork, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            {
                var name = GetTableName(type);
                sql = string.Format("select * from {0}(nolock)", name);
                GetQueries[type.TypeHandle] = sql;
            }
#if DEBUG
            Trace.WriteLine(sql);
#endif

            return await unitOfWork.Connection.QueryAsync<T>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }


        private static string GetTableName(Type type)
        {
            string name;
            //if (!TypeTableName.TryGetValue(type.TypeHandle, out name))
            //{
            name = type.Name;
            if (type.IsInterface && name.StartsWith("I"))
                name = name.Substring(1);

            //NOTE: This as dynamic trick should be able to handle both our own Table-attribute as well as the one in EntityFramework 
            var tableattr = type.GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TableAttribute").SingleOrDefault() as
                dynamic;
            if (tableattr != null)
                name = tableattr.Name;
            //    TypeTableName[type.TypeHandle] = name;
            //}
            return name;
        }

        public static long InsertNotTransfer<T>(this IUnitOfWork unitOfWork, T entityToInsert, int? commandTimeout = null) where T : class
        {
            using (SqlConnection connection = new SqlConnection(unitOfWork.ConnectionAddress))
            {

                //using (var tx = connection.BeginTransaction())
                //{
                var type = typeof(T);

                var name = GetTableName(type);

                var sb = new StringBuilder(null);
                sb.AppendFormat("insert into {0} (", name);

                var allProperties = TypePropertiesCache(type);
                var keyProperties = KeyPropertiesCache(type);
                var computedProperties = ComputedPropertiesCache(type);
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    if (keyProperties.Contains(property))
                    {
                        continue;
                    }
                    if (computedProperties.Contains(property))
                        continue;

                    sb.Append("[{0}]".Formater(property.Name));
                    if (i < allProperties.Count() - 1)
                        sb.Append(", ");
                }
                sb.Append(") values (");
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    if (keyProperties.Contains(property))
                    {
                        continue;
                    }

                    if (computedProperties.Contains(property))
                        continue;

                    if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "updatetime")
                    {
                        property.SetValue(entityToInsert, DateTime.Now);
                    }
                    else if (property.Name.ToLower() == "createusername" || property.Name.ToLower() == "updateusername")
                    {
                        if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                        {
                            property.SetValue(entityToInsert, unitOfWork.CurrentName);
                        }
                    }
                    else if (property.Name.ToLower() == "userid")
                    {
                        if (unitOfWork.CurrentId.HasValue)
                        {
                            property.SetValue(entityToInsert, unitOfWork.CurrentId.Value);
                        }
                    }

                    sb.AppendFormat("@{0}", property.Name);
                    if (i < allProperties.Count() - 1)
                        sb.Append(", ");
                }
                sb.Append(") ");
#if DEBUG
                Trace.WriteLine(sb.ToString());
#endif
                connection.Execute(sb.ToString(), entityToInsert, transaction: null, commandTimeout: commandTimeout);
                //NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
                var r = connection.Query<int>("select  isnull(@@IDENTITY,0)", transaction: null, commandTimeout: commandTimeout);
                // tx.Commit();
                return (int)r.First();
            }

        }
        public static async Task<bool> UpdateByConditionNoTransferAsync<T, TProperty>(this IUnitOfWork unitOfWork, Expression<Func<T, TProperty>> expression, object t) where T : class
        {
            using (SqlConnection connection = new SqlConnection(unitOfWork.ConnectionAddress))
            {
                var type = typeof(T);
                var psc = type.GetProperties();

                var ps = t.GetType().GetProperties();

                var set = string.Empty;
                var tableName = GetTableName(type);
                foreach (var item in ps)
                {
                    if (psc.Where(x => x.Name.Equals(item.Name, StringComparison.OrdinalIgnoreCase)).Count() <= 0)
                        throw new Exception(string.Format("表{0}中不存在字段{1}", tableName, item.Name));
                    set += item.Name + "=@" + item.Name + ",";
                }

                var translator = new QueryTranslator();
                var where = translator.Translate(expression);

                var sql = "update {0} set {1} UpdateTime='{3}',UpdateUserName='{4}' {2}".Formater(
                    tableName,
                    set,
                    string.IsNullOrEmpty(where.Trim()) ? string.Empty : " where " + where,
                    DateTime.Now,
                    unitOfWork.CurrentName);
                Trace.WriteLine(sql);
                var r = await connection.ExecuteAsync(sql, t, null);
                return r > 0;
            }
        }
        /// <summary>
        /// Inserts an entity into table "Ts" and returns identity id.
        /// </summary>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToInsert">Entity to insert</param>
        /// <returns>Identity of inserted entity</returns>
        public static async Task<long> InsertAsync<T>(this IUnitOfWork unitOfWork, T entityToInsert, int? commandTimeout = null) where T : class
        {
            //using (var tx = connection.BeginTransaction())
            //{
            var type = typeof(T);

            var name = GetTableName(type);

            var sb = new StringBuilder(null);
            sb.AppendFormat("insert into {0} (", name);

            var allProperties = TypePropertiesCache(type);
            var keyProperties = KeyPropertiesCache(type);
            var computedProperties = ComputedPropertiesCache(type);
            var insertProperties = allProperties
                .Where(x => !keyProperties.Any(y => y == x) && !computedProperties.Any(y => y == x));
            for (var i = 0; i < insertProperties.Count(); i++)
            {
                var property = insertProperties.ElementAt(i);

                sb.Append("[{0}]".Formater(property.Name));
                if (i < insertProperties.Count() - 1)
                    sb.Append(", ");
            }

            sb.Append(") values (");
            for (var i = 0; i < insertProperties.Count(); i++)
            {
                var property = insertProperties.ElementAt(i);

                if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "updatetime")
                {
                    property.SetValue(entityToInsert, DateTime.Now);
                }
                else if (property.Name.ToLower() == "createusername" || property.Name.ToLower() == "updateusername")
                {
                    if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                    {
                        property.SetValue(entityToInsert, unitOfWork.CurrentName);
                    }
                }
                else if (property.Name.ToLower() == "userid")
                {
                    if (unitOfWork.CurrentId.HasValue)
                    {
                        property.SetValue(entityToInsert, unitOfWork.CurrentId.Value);
                    }
                }
                sb.AppendFormat("@{0}", property.Name);
                if (i < insertProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") ");

            Trace.WriteLine(sb.ToString());
            //await unitOfWork.Connection.ExecuteAsync(sb.ToString(), entityToInsert, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            //NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
            var r = await unitOfWork.Connection.QueryAsync<int>(sb.ToString() + " select  isnull(@@IDENTITY,0)", entityToInsert, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            // tx.Commit();
            return (int)r.First();
            //}
        }

        public static void InsertBatch<T>(this IUnitOfWork unitOfWork, List<T> entityToInsert, int? commandTimeout = null) where T : class
        {
            //using (var tx = connection.BeginTransaction())
            //{
            var type = typeof(T);

            var name = GetTableName(type);

            var sb = new StringBuilder(null);
            sb.AppendFormat("insert into {0} (", name);

            var allProperties = TypePropertiesCache(type);
            var keyProperties = KeyPropertiesCache(type);
            var computedProperties = ComputedPropertiesCache(type);

            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                if (keyProperties.Contains(property) || computedProperties.Contains(property))
                {
                    continue;
                }

                sb.Append("[{0}]".Formater(property.Name));
                if (i < allProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") values (");
            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                if (keyProperties.Contains(property) || computedProperties.Contains(property))
                {
                    continue;
                }

                //if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "updatetime")
                //{
                //    property.SetValue(entityToInsert, DateTime.Now);
                //}
                //else if (property.Name.ToLower() == "createusername" || property.Name.ToLower() == "updateusername")
                //{
                //    if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                //    {
                //        property.SetValue(entityToInsert, unitOfWork.CurrentName);
                //    }
                //}
                //else if (property.Name.ToLower() == "userid")
                //{
                //    if (unitOfWork.CurrentId.HasValue)
                //    {
                //        property.SetValue(entityToInsert, unitOfWork.CurrentId.Value);
                //    }
                //}
                sb.AppendFormat("@{0}", property.Name);
                if (i < allProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") ");
            unitOfWork.Connection.Execute(sb.ToString(), entityToInsert, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }

        /// <summary>
        /// 非自增表的插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="unitOfWork"></param>
        /// <param name="entityToInsert"></param>
        /// <param name="commandTimeout"></param>
        public static async Task<bool> InsertNotIdentityAsync<T>(this IUnitOfWork unitOfWork, T entityToInsert, int? commandTimeout = null) where T : class
        {
            //using (var tx = connection.BeginTransaction())
            //{
            var type = typeof(T);

            var name = GetTableName(type);

            var sb = new StringBuilder(null);
            sb.AppendFormat("insert into {0} (", name);

            var allProperties = TypePropertiesCache(type);
            var keyProperties = KeyPropertiesCache(type);

            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                var notIdentity = property.GetCustomAttribute<NotIdentityAttribute>();

                if (notIdentity == null && keyProperties.Contains(property))
                {
                    continue;
                }

                sb.Append("[{0}]".Formater(property.Name));
                if (i < allProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") values (");
            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                var Identity = property.GetCustomAttribute<NotIdentityAttribute>();
                //不包含自增属性并且是主键
                if (Identity == null && keyProperties.Contains(property))
                {
                    continue;
                }

                if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "updatetime")
                {
                    property.SetValue(entityToInsert, DateTime.Now);
                }
                else if (property.Name.ToLower() == "createusername" || property.Name.ToLower() == "updateusername")
                {
                    if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                    {
                        property.SetValue(entityToInsert, unitOfWork.CurrentName);
                    }
                }
                sb.AppendFormat("@{0}", property.Name);
                if (i < allProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") ");
#if DEBUG
            Trace.WriteLine(sb.ToString());
#endif
            await unitOfWork.Connection.ExecuteAsync(sb.ToString(), entityToInsert, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            return true;
        }

        /// <summary>
        /// 向没有主键、非自增表插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="unitOfWork"></param>
        /// <param name="entityToInsert"></param>
        /// <param name="commandTimeout"></param>
        public static async Task<bool> InsertOperateLogAsync<T>(this IUnitOfWork unitOfWork, T entityToInsert, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);

            var name = GetTableName(type);

            var sb = new StringBuilder(null);
            sb.AppendFormat("insert into {0} (", name);

            var allProperties = TypePropertiesCache(type);
            var keyProperties = KeyPropertiesCache(type);

            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                var notIdentity = property.GetCustomAttribute<NotIdentityAttribute>();

                //if (notIdentity == null && keyProperties.Contains(property))
                //{
                //    continue;
                //}

                sb.Append("[{0}]".Formater(property.Name));
                if (i < allProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") values (");
            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                var Identity = property.GetCustomAttribute<NotIdentityAttribute>();
                //不包含自增属性并且是主键
                //if (Identity == null && keyProperties.Contains(property))
                //{
                //    continue;
                //}

                if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "updatetime")
                {
                    property.SetValue(entityToInsert, DateTime.Now);
                }
                else if (property.Name.ToLower() == "createusername" || property.Name.ToLower() == "updateusername")
                {
                    if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                    {
                        property.SetValue(entityToInsert, unitOfWork.CurrentName);
                    }
                }
                sb.AppendFormat("@{0}", property.Name);
                if (i < allProperties.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(") ");
#if DEBUG
            Trace.WriteLine(sb.ToString());
#endif
            await unitOfWork.Connection.ExecuteAsync(sb.ToString(), entityToInsert, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            return true;
        }

        /// <summary>
        /// 根据某个字段修改表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="unitOfWork"></param>
        /// <param name="field"></param>
        /// <param name="value"></param>
        /// <param name="pk"></param>
        /// <returns></returns>
        public static async Task<bool> UpdateByPrimaryKeyAsync<T>(this IUnitOfWork unitOfWork, string field, object value, int pk) where T : class
        {
            var type = typeof(T);

            await OperateLog<T>(unitOfWork, field, value, pk, type);

            var tableName = GetTableName(type);
            string where = @" where id=@id";
            string sql = "update {0} set {1} = @value,UpdateTime=@updateTime,UpdateUserName=@updateUserName".Formater(tableName, field);
            if (pk != 0)
            {
                sql += where;
            }
            else
            {
                throw new Exception("未传入正确的主键");
            }
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var r = await unitOfWork.Connection.ExecuteAsync(sql, new { value = value, id = pk, updateTime = DateTime.Now, updateUserName = unitOfWork.CurrentName }, transaction: unitOfWork.Transaction, commandType: null);
            return r > 0 ? true : false;
        }

        private static async Task<bool> OperateLog<T>(IUnitOfWork unitOfWork, string field, object value, int pk, Type type, DateTime? createTime = null) where T : class
        {

            if (createTime == null)
                createTime = DateTime.Now;

            var allProperties = TypePropertiesCache(type);

            string logTableName = string.Empty;
            string description = string.Empty;
            bool isTrack = false;

            var newValue = value;
            var propertyValue = value;
            foreach (var item in allProperties)
            {
                if (!item.Name.Equals(field, StringComparison.OrdinalIgnoreCase))
                    continue;
                var track = item.GetCustomAttribute<TrackAttribute>();
                if (track == null)
                    continue;
                isTrack = true;
                description = track.Description;
                if (item.PropertyType.IsEnum && value.GetType().Equals(typeof(int)))
                {
                    newValue = item.PropertyType.GetEnumName(value.ToString().TryConvertInt32());
                    propertyValue = item.PropertyType.GetField(newValue.ToString()).GetValue(null);
                }
                else if (item.PropertyType.IsEnum)
                {
                    newValue = value.ToString();
                    propertyValue = item.PropertyType.GetField(value.ToString()).GetValue(null);
                }
                break;
            }
            if (isTrack)
            {
                var trackTable = type.GetCustomAttribute<TrackTableAttribute>();
                var orign = await unitOfWork.GetAsync<T>(pk);
                var orignType = orign.GetType();

                if (
                    orignType.GetProperty(field) != null && (
                    (orignType.GetProperty(field).GetValue(orign) != null && orignType.GetProperty(field).GetValue(orign).Equals(propertyValue))
                    ||
                    (orignType.GetProperty(field).GetValue(orign) == null && propertyValue == null))
                    )
                {
                    return false;
                }
                if (trackTable == null)
                    throw new Exception("TrackTable未设定，请检查实体类");

                logTableName = GetTableName(trackTable.Table);

                var allLogProperties = TypePropertiesCache(trackTable.Table);
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("insert into {0}", logTableName);
                sb.Append("(");
                for (var i = 0; i < allLogProperties.Count(); i++)
                {
                    var property = allLogProperties.ElementAt(i);

                    sb.Append(property.Name);
                    if (i < allLogProperties.Count() - 1)
                        sb.Append(", ");
                }
                sb.Append(")");
                sb.Append("values");
                sb.Append("(");
                for (var i = 0; i < allLogProperties.Count(); i++)
                {
                    var property = allLogProperties.ElementAt(i);
                    sb.AppendFormat("@{0}", property.Name);
                    if (i < allLogProperties.Count() - 1)
                        sb.Append(", ");
                }
                sb.Append(")");

                var instance = Activator.CreateInstance(trackTable.Table);
                var properties = instance.GetType().GetProperties().Where(p => p.PropertyType.IsPublic && p.CanWrite).ToList();
                foreach (var item in properties)
                {
                    switch (item.Name.ToLower())
                    {
                        case "id":
                            item.SetValue(instance, pk);
                            break;
                        case "field":
                            item.SetValue(instance, field);
                            break;
                        case "fielddesc":
                            item.SetValue(instance, description);
                            break;
                        case "oldvalue":
                            var result = orignType.GetProperty(field).GetValue(orign) ?? orignType.GetProperty(field);
                            item.SetValue(instance, result == null ? "" : result.ToString());
                            break;
                        case "newvalue":
                            item.SetValue(instance, newValue == null ? "" : newValue.ToString());
                            break;
                        case "createtime":
                            item.SetValue(instance, createTime.Value);
                            break;
                        case "createusername":
                            item.SetValue(instance, string.IsNullOrWhiteSpace(unitOfWork.CurrentName) ? "System" : unitOfWork.CurrentName);
                            break;
                        case "updatetime":
                            item.SetValue(instance, DateTime.Now);
                            break;
                        case "updateusername":
                            item.SetValue(instance, string.IsNullOrWhiteSpace(unitOfWork.CurrentName) ? "System" : unitOfWork.CurrentName);
                            break;
                    }
                }
#if DEBUG
                Trace.WriteLine(sb.ToString());
#endif
                await unitOfWork.Connection.ExecuteAsync(sb.ToString(), instance, transaction: unitOfWork.Transaction, commandType: null);

            }
            return true;
        }

        public static async Task<bool> UpdateByPrimaryKeyAsync<T, TProperty>(this IUnitOfWork unitOfWork, Expression<Func<T, TProperty>> expression, int id, object value) where T : class
        {
            var type = typeof(T);
            string field = (expression.Body as System.Linq.Expressions.MemberExpression).Member.Name;
            await OperateLog<T>(unitOfWork, field, value, id, type);

            var tableName = GetTableName(type);
            string sql = "update {0} set {1} = @value,UpdateTime=@updateTime,UpdateUserName=@updateUserName where id=@id".Formater(tableName, field);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var o = await unitOfWork.Connection.ExecuteAsync(sql, new { value = value, id = id, updateTime = DateTime.Now, updateUserName = unitOfWork.CurrentName }, transaction: unitOfWork.Transaction, commandType: null);
            return o > 0 ? true : false;

        }
        public static async Task<bool> UpdateByConditionAsync<T, TProperty>(this IUnitOfWork unitOfWork, Expression<Func<T, TProperty>> expression, string[] propertys, object[] values) where T : class
        {
            var type = typeof(T);
            var set = string.Empty;
            var dic = new Dictionary<string, object>();
            for (int i = 0; i < propertys.Length; i++)
            {
                set += propertys[i] + "=@" + propertys[i] + ",";
                dic.Add("@" + propertys[i], values[i]);
            }
            dic.Add("@updateTime", DateTime.Now);
            dic.Add("@updateUserName", unitOfWork.CurrentName);

            var tableName = GetTableName(type);
            var translator = new QueryTranslator();
            var where = translator.Translate(expression);

            var sql = "update {0} set {1} UpdateTime=@updateTime,UpdateUserName=@updateUserName {2}".Formater(tableName, set, string.IsNullOrEmpty(where.Trim()) ? string.Empty : " where " + where);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var r = await unitOfWork.Connection.ExecuteAsync(sql, dic, transaction: unitOfWork.Transaction, commandType: null);
            return r > 0 ? true : false;
        }
        public static async Task<bool> UpdateByConditionAsync<T, TProperty>(this IUnitOfWork unitOfWork, Expression<Func<T, TProperty>> expression, object t) where T : class
        {
            var type = typeof(T);
            var psc = type.GetProperties();

            var ps = t.GetType().GetProperties();

            var set = string.Empty;
            var tableName = GetTableName(type);
            foreach (var item in ps)
            {
                if (psc.Where(x => x.Name.Equals(item.Name, StringComparison.OrdinalIgnoreCase)).Count() <= 0)
                    throw new Exception(string.Format("表{0}中不存在字段{1}", tableName, item.Name));
                set += item.Name + "=@" + item.Name + ",";
            }

            var translator = new QueryTranslator();
            var where = translator.Translate(expression);

            var sql = "update {0} set {1} UpdateTime='{3}',UpdateUserName='{4}' {2}".Formater(
                tableName,
                set,
                string.IsNullOrEmpty(where.Trim()) ? string.Empty : " where " + where,
                DateTime.Now,
                unitOfWork.CurrentName);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var r = await unitOfWork.Connection.ExecuteAsync(sql, t, transaction: unitOfWork.Transaction, commandType: null);
            return r > 0 ? true : false;
        }

        public static async Task<bool> UpdateByConditionAsync<T, TProperty>(this IUnitOfWork unitOfWork, Expression<Func<T, TProperty>> expression, string sql) where T : class
        {
            var type = typeof(T);
            var psc = type.GetProperties();

            var tableName = GetTableName(type);

            var where = string.Empty;
            if (expression != null)
            {
                var translator = new QueryTranslator();
                where = translator.Translate(expression);
            }
            if (!string.IsNullOrEmpty(where.Trim()))
            {
                if (sql.ToLower().Contains("where"))
                {
                    sql += where;
                }
                else
                {
                    sql += " where " + where;
                }
            }
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var obj = await unitOfWork.Connection.ExecuteAsync(sql, new { }, transaction: unitOfWork.Transaction, commandType: null);
            return obj > 0 ? true : false;
        }
        public static async Task<int> CountAsync<T>(this IUnitOfWork unitOfWork, Expression<Func<T, bool>> expression, QueryModel model, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            var name = GetTableName(type);
            sql = "select count(0) from " + name + " (NOLOCK)  where 1=1 {0}";
            var translator = new QueryTranslator();
            string where = translator.Translate(expression);
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? "" : (" and " + where));
            if (model != null && model.DataPermission != null && model.DataPermission.Count > 0)
            {
                //存在数据权限
                var allProperties = TypePropertiesCache(typeof(T));
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    var identity = property.GetCustomAttribute<DataPermissionAttribute>();
                    if (identity == null)
                        continue;

                    var permission = model.DataPermission.Find(x => x.PermissioinType == identity.PermissionType.GetValue());
                    if (permission == null)
                        continue;
                    if (string.IsNullOrWhiteSpace(permission.Value))
                        continue;
                    sql += " AND {0} IN ({1})".Formater(property.Name, permission.Value.TrimEnd(','));
                }
            }
#if DEBUG
            Trace.WriteLine(sql);
#endif
            return await unitOfWork.Connection.ExecuteScalarAsync<int>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
        }
        public static async Task<IDictionary<int, int>> GroupCountAsync<T, TProperty>(this IUnitOfWork unitOfWork, Expression<Func<T, TProperty>> propName, Expression<Func<T, bool>> expression = null, QueryModel queryModel = null, int? commandTimeout = null) where T : class
        {
            string field = (propName.Body as System.Linq.Expressions.MemberExpression).Member.Name;
            var name = GetTableName(typeof(T));
            string sql = string.Format(" SELECT  {1} AS [Status] ,  COUNT(0) AS [Count] FROM    {0} (NOLOCK) ", name, field);
            sql += " where 1=1  ";
            if (expression != null)
            {
                var translator = new QueryTranslator();
                var where = translator.Translate(expression);
                sql += string.IsNullOrEmpty(where.Trim()) ? string.Empty : " and " + where;
            }
            if (unitOfWork.DataPermission != null && unitOfWork.DataPermission.Count > 0)
            {//存在数据权限
                var allProperties = TypePropertiesCache(typeof(T));
                for (var i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    var identity = property.GetCustomAttribute<DataPermissionAttribute>();
                    if (identity == null)
                        continue;

                    var permission = unitOfWork.DataPermission.Find(x => x.PermissioinType == identity.PermissionType.GetValue());
                    if (permission == null)
                        continue;
                    if (string.IsNullOrWhiteSpace(permission.Value))
                        continue;

                    sql += " AND {0} IN ({1})".Formater(property.Name, permission.Value.TrimEnd(','));

                }
            }

            sql += string.Format(" GROUP BY {0} ", field);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var result = await unitOfWork.Connection.QueryAsync<dynamic>(sql, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            IDictionary<int, int> dic = new Dictionary<int, int>();
            foreach (var item in result)
            {
                dic.Add(item.Status, item.Count);
            }
            return dic;
        }

        public static async Task<bool> UpdateListAsync<T>(this IUnitOfWork unitOfWork, IList<T> list, int? commandTimeout = null) where T : class
        {
            if (list.Count == 0)
                return false;
            var proxy = list.First() as IProxy;
            if (proxy != null)
            {
                if (!proxy.IsDirty) return false;
            }

            var type = typeof(T);

            var keyProperties = KeyPropertiesCache(type);
            if (keyProperties.Count() == 0)
                throw new ArgumentException("实体必须包含一个主键 [Key] 属性");
            var key = keyProperties.FirstOrDefault();

            var name = GetTableName(type);
            var allProperties = TypePropertiesCache(type);
            var nonIdProps = allProperties.Where(a => !keyProperties.Contains(a));

            var sb = new StringBuilder();
            foreach (var entityToUpdate in list)
            {
                sb.Append("update {0} set ".Formater(name));

                for (var i = 0; i < nonIdProps.Count(); i++)
                {
                    var property = nonIdProps.ElementAt(i);
                    var value = property.GetValue(entityToUpdate);
                    if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "createusername")
                        continue;
                    if (property.Name.ToLower() == "updatetime")
                    {
                        property.SetValue(entityToUpdate, DateTime.Now);
                    }
                    else if (property.Name.ToLower() == "updateusername" || property.Name.ToLower() == "shelvesusername")// || property.Name.ToLower() == "allottedusername")
                    {
                        if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                        {
                            property.SetValue(entityToUpdate, unitOfWork.CurrentName);
                        }
                    }
                    //add gyp 20160608 非ERP操作的（例：PDA）动态设置值
                    else if (property.Name.ToLower() == "userid" || property.Name.ToLower() == "shelvesuserid")// || property.Name.ToLower() == "allotteduserid")
                    {
                        if (unitOfWork.CurrentId.HasValue)
                        {
                            property.SetValue(entityToUpdate, unitOfWork.CurrentId.Value);
                        }
                    }

                    if (property.GetValue(entityToUpdate) == null)
                    {
                        sb.AppendFormat("[{0}] = null", property.Name, property.GetValue(entityToUpdate));
                    }
                    else if (property.PropertyType == typeof(string) || property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
                    {
                        sb.AppendFormat("[{0}] = '{1}'", property.Name, property.GetValue(entityToUpdate).ToString().Replace("'", "''"));
                    }
                    else
                    {
                        sb.AppendFormat("[{0}] = {1}", property.Name, property.GetValue(entityToUpdate));
                    }

                    if (i < nonIdProps.Count() - 1)
                        sb.AppendFormat(", ");
                }
                sb.Append(" where ");
                for (var i = 0; i < keyProperties.Count(); i++)
                {
                    var property = keyProperties.ElementAt(i);
                    sb.AppendFormat("[{0}] = {1}", property.Name, property.GetValue(entityToUpdate));
                    if (i < keyProperties.Count() - 1)
                        sb.AppendFormat(" and ");
                }
                sb.AppendLine();
            }
            var updated = await unitOfWork.Connection.ExecuteAsync(sb.ToString(), null, commandTimeout: commandTimeout, transaction: unitOfWork.Transaction);
            return updated > 0;
        }

        /// <summary>
        /// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension.
        /// </summary>
        /// <typeparam name="T">Type to be updated</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToUpdate">Entity to be updated</param>
        /// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
        public static async Task<bool> UpdateAsync<T>(this IUnitOfWork unitOfWork, T entityToUpdate, int? commandTimeout = null) where T : class
        {

            var proxy = entityToUpdate as IProxy;
            if (proxy != null)
            {
                if (!proxy.IsDirty) return false;
            }

            var type = typeof(T);

            var keyProperties = KeyPropertiesCache(type);
            if (keyProperties.Count() == 0)
                throw new ArgumentException("实体必须包含一个主键 [Key] 属性");
            var key = keyProperties.FirstOrDefault();

            var name = GetTableName(type);

            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set ", name);

            var allProperties = TypePropertiesCache(type);

            var computedProperties = ComputedPropertiesCache(type);
            var nonIdProps = allProperties.Where(a => !keyProperties.Contains(a) && !computedProperties.Contains(a));
            DateTime operTime = DateTime.Now;
            for (var i = 0; i < nonIdProps.Count(); i++)
            {
                var property = nonIdProps.ElementAt(i);
                var value = property.GetValue(entityToUpdate);
                if (property.Name.ToLower() == "createtime" || property.Name.ToLower() == "createusername")
                    continue;
                if (property.Name.ToLower() == "updatetime")
                {
                    property.SetValue(entityToUpdate, DateTime.Now);
                }
                else if (property.Name.ToLower() == "updateusername" || property.Name.ToLower() == "shelvesusername")// || property.Name.ToLower() == "allottedusername")
                {
                    if (!string.IsNullOrWhiteSpace(unitOfWork.CurrentName))
                    {
                        property.SetValue(entityToUpdate, unitOfWork.CurrentName);
                    }
                }
                //add gyp 20160608 非ERP操作的（例：PDA）动态设置值
                else if (property.Name.ToLower() == "userid" || property.Name.ToLower() == "shelvesuserid")// || property.Name.ToLower() == "allotteduserid")
                {
                    if (unitOfWork.CurrentId.HasValue)
                    {
                        property.SetValue(entityToUpdate, unitOfWork.CurrentId.Value);
                    }
                }
                sb.AppendFormat("[{0}] = @{1}", property.Name, property.Name);
                if (i < nonIdProps.Count() - 1)
                    sb.AppendFormat(", ");

                await OperateLog<T>(unitOfWork, property.Name, value, key.GetValue(entityToUpdate).ToString().TryConvertInt32(), type, operTime);
            }
            sb.Append(" where ");
            for (var i = 0; i < keyProperties.Count(); i++)
            {
                var property = keyProperties.ElementAt(i);
                sb.AppendFormat("[{0}] = @{1}", property.Name, property.Name);
                if (i < keyProperties.Count() - 1)
                    sb.AppendFormat(" and ");
            }
#if DEBUG
            Trace.WriteLine(sb.ToString());
#endif
            var updated = await unitOfWork.Connection.ExecuteAsync(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: unitOfWork.Transaction);
            return updated > 0;
        }

        /// <summary>
        /// Delete entity in table "Ts".
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToDelete">Entity to delete</param>
        /// <returns>true if deleted, false if not found</returns>
        public static async Task<bool> DeleteAsync<T>(this IUnitOfWork unitOfWork, T entityToDelete, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);

            var keyProperties = KeyPropertiesCache(type);
            if (keyProperties.Count() == 0)
                throw new ArgumentException("实体必须包含一个主键 [Key] 属性");

            var name = GetTableName(type);

            var sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where ", name);

            for (var i = 0; i < keyProperties.Count(); i++)
            {
                var property = keyProperties.ElementAt(i);
                sb.AppendFormat("[{0}] = @{1}", property.Name, property.Name);
                if (i < keyProperties.Count() - 1)
                    sb.AppendFormat(" and ");
            }
#if DEBUG
            Trace.WriteLine(sb.ToString());
#endif
            var deleted = await unitOfWork.Connection.ExecuteAsync(sb.ToString(), entityToDelete, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            return deleted > 0;
        }



        public static async Task<bool> DeleteAsync<T>(this IUnitOfWork unitOfWork, List<T> list, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);

            var keyProperties = KeyPropertiesCache(type);
            if (keyProperties.Count() == 0)
                throw new ArgumentException("实体必须包含一个主键 [Key] 属性");

            var name = GetTableName(type);

            var sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where ", name);

            for (var i = 0; i < keyProperties.Count(); i++)
            {
                var property = keyProperties.ElementAt(i);
                sb.AppendFormat("[{0}] = @{1}", property.Name, property.Name);
                if (i < keyProperties.Count() - 1)
                    sb.AppendFormat(" and ");
            }
            var deleted = 0;
            foreach (var item in list)
            {
#if DEBUG
                Trace.WriteLine(sb.ToString());
#endif
                deleted += await unitOfWork.Connection.ExecuteAsync(sb.ToString(), item, transaction: unitOfWork.Transaction, commandTimeout: commandTimeout);
            }

            return deleted > 0;
        }


        public static async Task<bool> DeleteByIdAsync<T>(this IUnitOfWork unitOfWork, int id) where T : class
        {
            var type = typeof(T);
            var tableName = GetTableName(type);
            string sql = "delete from {0} where id=@id".Formater(tableName);
#if DEBUG
            Trace.WriteLine(sql);
#endif
            var qty = await unitOfWork.Connection.ExecuteAsync(sql, new { id = id, updateTime = DateTime.Now, updateUserName = unitOfWork.CurrentName }, transaction: unitOfWork.Transaction, commandType: null);
            return qty > 0 ? true : false;
        }

        public static void BulkToDB<T>(this SqlConnection conn, List<T> t, SqlTransaction tran)
        {
            var type = typeof(T);
            var name = GetTableName(type);

            var allProperties = TypePropertiesCache(type);

            DataTable dt = new DataTable();
            for (int i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                dt.Columns.Add(property.Name);
            }
            foreach (var item in t)
            {
                var itemType = item.GetType();
                var properties = TypePropertiesCache(itemType);

                DataRow dr = dt.NewRow();

                for (int i = 0; i < properties.Count(); i++)
                {
                    var property = properties.ElementAt(i);
                    if (property.Name.Equals("id", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (property.PropertyType.IsEnum)
                    {
                        //alter zkp 20160524
                        dr[property.Name] = Convert.ToInt32(property.GetValue(item));
                    }
                    else
                    {
                        dr[property.Name] = property.GetValue(item);
                    }
                }
                dt.Rows.Add(dr);
            }


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
            {

                bulkCopy.DestinationTableName = name;
                for (int i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    if (property.Name.Equals("id", StringComparison.OrdinalIgnoreCase))
                        continue;
                    bulkCopy.ColumnMappings.Add(property.Name, property.Name);
                }
                bulkCopy.BatchSize = t.Count;
                bulkCopy.WriteToServer(dt);
            }
        }

        public static void BulkToDB<T>(this SqlConnection conn, List<T> t)
        {
            var type = typeof(T);
            var name = GetTableName(type);

            var allProperties = TypePropertiesCache(type);

            DataTable dt = new DataTable();
            for (int i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                dt.Columns.Add(property.Name);
            }
            foreach (var item in t)
            {
                var itemType = item.GetType();
                var properties = TypePropertiesCache(itemType);

                DataRow dr = dt.NewRow();

                for (int i = 0; i < properties.Count(); i++)
                {
                    var property = properties.ElementAt(i);
                    if (property.Name.Equals("id", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (property.PropertyType.IsEnum)
                    {
                        //alter zkp 20160524
                        dr[property.Name] = Convert.ToInt32(property.GetValue(item));
                    }
                    else
                    {
                        dr[property.Name] = property.GetValue(item);
                    }
                }
                dt.Rows.Add(dr);
            }


            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, null))
            {

                bulkCopy.DestinationTableName = name;
                for (int i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    if (property.Name.Equals("id", StringComparison.OrdinalIgnoreCase))
                        continue;
                    bulkCopy.ColumnMappings.Add(property.Name, property.Name);
                }
                bulkCopy.BatchSize = t.Count;
                bulkCopy.WriteToServer(dt);
            }
        }

        public static async Task<bool> BulkToDBAsync<T>(this IUnitOfWork unitOfWork, List<T> t)
        {
            var type = typeof(T);
            var name = GetTableName(type);

            var allProperties = TypePropertiesCache(type);
            allProperties = allProperties.Where(p => !p.GetCustomAttributes(true).Any(a => a is NoDbAttribute)).ToList();
            DataTable dt = new DataTable();
            for (int i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                dt.Columns.Add(property.Name);
            }
            foreach (var item in t)
            {
                var itemType = item.GetType();
                var properties = TypePropertiesCache(itemType);
                properties = properties.Where(p => !p.GetCustomAttributes(true).Any(a => a is NoDbAttribute)).ToList();

                DataRow dr = dt.NewRow();

                for (int i = 0; i < properties.Count(); i++)
                {
                    var property = properties.ElementAt(i);
                    if (property.Name.Equals("id", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (property.PropertyType.IsEnum)
                    {
                        //alter zkp 20160524
                        dr[property.Name] = Convert.ToInt32(property.GetValue(item));
                    }
                    else
                    {
                        dr[property.Name] = property.GetValue(item);
                    }
                }
                dt.Rows.Add(dr);
            }

            var connection = unitOfWork.Connection;

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)unitOfWork.Connection, SqlBulkCopyOptions.Default, (SqlTransaction)unitOfWork.Transaction))
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                bulkCopy.DestinationTableName = name;
                for (int i = 0; i < allProperties.Count(); i++)
                {
                    var property = allProperties.ElementAt(i);
                    if (property.Name.Equals("id", StringComparison.OrdinalIgnoreCase))
                        continue;
                    bulkCopy.ColumnMappings.Add(property.Name, property.Name);
                }
                bulkCopy.BatchSize = t.Count;
                bulkCopy.BulkCopyTimeout = 120;
                await bulkCopy.WriteToServerAsync(dt);
                //unitOfWork.Connection.Close();
            }
            return true;
        }


        class ProxyGenerator
        {
            private static readonly Dictionary<Type, object> TypeCache = new Dictionary<Type, object>();

            private static AssemblyBuilder GetAsmBuilder(string name)
            {
                var assemblyBuilder = AssemblyBuilder.DefineDynamicAssembly(new AssemblyName { Name = name },
                    AssemblyBuilderAccess.Run);       //NOTE: to save, use RunAndSave

                return assemblyBuilder;
            }

            public static T GetClassProxy<T>()
            {
                // A class proxy could be implemented if all properties are virtual
                //  otherwise there is a pretty dangerous case where internal actions will not update dirty tracking
                throw new NotImplementedException();
            }


            public static T GetInterfaceProxy<T>()
            {
                Type typeOfT = typeof(T);

                if (TypeCache.ContainsKey(typeOfT))
                {
                    return (T)TypeCache[typeOfT];
                }
                var assemblyBuilder = GetAsmBuilder(typeOfT.Name);

                var moduleBuilder = assemblyBuilder.DefineDynamicModule("SqlMapperExtensions." + typeOfT.Name); //NOTE: to save, add "asdasd.dll" parameter

                var interfaceType = typeof(SqlMapperExtensions.IProxy);
                var typeBuilder = moduleBuilder.DefineType(typeOfT.Name + "_" + Guid.NewGuid(),
                    TypeAttributes.Public | TypeAttributes.Class);
                typeBuilder.AddInterfaceImplementation(typeOfT);
                typeBuilder.AddInterfaceImplementation(interfaceType);

                //create our _isDirty field, which implements IProxy
                var setIsDirtyMethod = CreateIsDirtyProperty(typeBuilder);

                // Generate a field for each property, which implements the T
                foreach (var property in typeof(T).GetProperties())
                {
                    var isId = property.GetCustomAttributes(true).Any(a => a is KeyAttribute);
                    CreateProperty<T>(typeBuilder, property.Name, property.PropertyType, setIsDirtyMethod, isId);
                }

                var generatedType = typeBuilder.CreateTypeInfo().AsType();

                //assemblyBuilder.Save(name + ".dll");  //NOTE: to save, uncomment

                var generatedObject = Activator.CreateInstance(generatedType);

                TypeCache.Add(typeOfT, generatedObject);
                return (T)generatedObject;
            }


            private static MethodInfo CreateIsDirtyProperty(TypeBuilder typeBuilder)
            {
                var propType = typeof(bool);
                var field = typeBuilder.DefineField("_" + "IsDirty", propType, FieldAttributes.Private);
                var property = typeBuilder.DefineProperty("IsDirty",
                                               System.Reflection.PropertyAttributes.None,
                                               propType,
                                               new Type[] { propType });

                const MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.NewSlot | MethodAttributes.SpecialName |
                                                    MethodAttributes.Final | MethodAttributes.Virtual | MethodAttributes.HideBySig;

                // Define the "get" and "set" accessor methods
                var currGetPropMthdBldr = typeBuilder.DefineMethod("get_" + "IsDirty",
                                             getSetAttr,
                                             propType,
                                             Type.EmptyTypes);
                var currGetIL = currGetPropMthdBldr.GetILGenerator();
                currGetIL.Emit(OpCodes.Ldarg_0);
                currGetIL.Emit(OpCodes.Ldfld, field);
                currGetIL.Emit(OpCodes.Ret);
                var currSetPropMthdBldr = typeBuilder.DefineMethod("set_" + "IsDirty",
                                             getSetAttr,
                                             null,
                                             new Type[] { propType });
                var currSetIL = currSetPropMthdBldr.GetILGenerator();
                currSetIL.Emit(OpCodes.Ldarg_0);
                currSetIL.Emit(OpCodes.Ldarg_1);
                currSetIL.Emit(OpCodes.Stfld, field);
                currSetIL.Emit(OpCodes.Ret);

                property.SetGetMethod(currGetPropMthdBldr);
                property.SetSetMethod(currSetPropMthdBldr);
                var getMethod = typeof(SqlMapperExtensions.IProxy).GetMethod("get_" + "IsDirty");
                var setMethod = typeof(SqlMapperExtensions.IProxy).GetMethod("set_" + "IsDirty");
                typeBuilder.DefineMethodOverride(currGetPropMthdBldr, getMethod);
                typeBuilder.DefineMethodOverride(currSetPropMthdBldr, setMethod);

                return currSetPropMthdBldr;
            }

            private static void CreateProperty<T>(TypeBuilder typeBuilder, string propertyName, Type propType, MethodInfo setIsDirtyMethod, bool isIdentity)
            {
                //Define the field and the property 
                var field = typeBuilder.DefineField("_" + propertyName, propType, FieldAttributes.Private);
                var property = typeBuilder.DefineProperty(propertyName,
                                               System.Reflection.PropertyAttributes.None,
                                               propType,
                                               new Type[] { propType });

                const MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.Virtual |
                                                    MethodAttributes.HideBySig;

                // Define the "get" and "set" accessor methods
                var currGetPropMthdBldr = typeBuilder.DefineMethod("get_" + propertyName,
                                             getSetAttr,
                                             propType,
                                             Type.EmptyTypes);

                var currGetIL = currGetPropMthdBldr.GetILGenerator();
                currGetIL.Emit(OpCodes.Ldarg_0);
                currGetIL.Emit(OpCodes.Ldfld, field);
                currGetIL.Emit(OpCodes.Ret);

                var currSetPropMthdBldr = typeBuilder.DefineMethod("set_" + propertyName,
                                             getSetAttr,
                                             null,
                                             new Type[] { propType });

                //store value in private field and set the isdirty flag
                var currSetIL = currSetPropMthdBldr.GetILGenerator();
                currSetIL.Emit(OpCodes.Ldarg_0);
                currSetIL.Emit(OpCodes.Ldarg_1);
                currSetIL.Emit(OpCodes.Stfld, field);
                currSetIL.Emit(OpCodes.Ldarg_0);
                currSetIL.Emit(OpCodes.Ldc_I4_1);
                currSetIL.Emit(OpCodes.Call, setIsDirtyMethod);
                currSetIL.Emit(OpCodes.Ret);

                //TODO: Should copy all attributes defined by the interface?
                if (isIdentity)
                {
                    var keyAttribute = typeof(KeyAttribute);
                    var myConstructorInfo = keyAttribute.GetConstructor(new Type[] { });
                    var attributeBuilder = new CustomAttributeBuilder(myConstructorInfo, new object[] { });
                    property.SetCustomAttribute(attributeBuilder);
                }

                property.SetGetMethod(currGetPropMthdBldr);
                property.SetSetMethod(currSetPropMthdBldr);
                var getMethod = typeof(T).GetMethod("get_" + propertyName);
                var setMethod = typeof(T).GetMethod("set_" + propertyName);
                typeBuilder.DefineMethodOverride(currGetPropMthdBldr, getMethod);
                typeBuilder.DefineMethodOverride(currSetPropMthdBldr, setMethod);
            }

        }
    }

    [AttributeUsage(AttributeTargets.Class)]
    public class TableAttribute : Attribute
    {
        public TableAttribute(string tableName)
        {
            Name = tableName;
        }
        public string Name { get; private set; }
    }
}