EF6 备忘 (二) 框架搭建

最新推荐文章于 2024-04-18 14:13:27 发布

直男250

最新推荐文章于 2024-04-18 14:13:27 发布

阅读量2.8k

点赞数

分类专栏： C# EntityFramework6 备忘录文章标签： ef-6 entity-framework mysql 框架

本文链接：https://blog.csdn.net/black0707/article/details/45688795

版权

C# EntityFramework6 备忘录专栏收录该内容

2 篇文章 0 订阅

订阅专栏

基本架构

这里写图片描述

项目结构

这里写图片描述

会话定义

这里写图片描述

实现方式
1，通过连接方法，获取产生的Session对象（如果远程方式，则远程服务端产生，如果单机方式，则通过AutoFac产生）；
2，内部利用工厂方法，构建业务对象实例，函数不空开。
3，直接使用Session对象中的业务对象接口行为，进行相关操作。
调用方式

//会话注册
 _session = FSession.Connect(new Uri("net.tcp://127.0.0.1"), user, password);
//举例：调用用户模块，认证用户登录
if (null != _session)
    _session.UserActor.Identificate(null);

优点（内聚）
1，相对解决了安全性问题；
2，屏蔽了对象实例的过程，使消费者（UI开发人员）更简洁的理解
3，支持了多服务、分布式、单机部署等多种通讯方式，并提供扩
缺点（耦合）

业务接口与Session本身的属性未解耦，每增加业务接口，需在ISession中添加属性，当然是可以解决，。
续重构。

WCF实现

网络版实现

这里写图片描述

单机版实现

/// <summary>
/// 单机版代理工厂
/// 为扩展WCF代理，修改为本机调用方式
/// </summary>
/// <typeparam name="TChannel">频道对象</typeparam>
internal class SimpleChannelFactory<TChannel> : ChannelFactory<TChannel>
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public SimpleChannelFactory():base()
        {
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="endpoint">终端节点信息</param>
        public SimpleChannelFactory(ServiceEndpoint endpoint) :base(endpoint)
        {
        }

/// <summary>
/// 创建一个通信通道
/// </summary>
/// <param name="address">地市</param>
/// <param name="url">地址</param>
/// <returns>通道对象</returns>
public override TChannel CreateChannel(EndpointAddress address, Uri url)
     {
      return AutoFacRegister.Resolve<TChannel>();
     }

    }

数据层实现

这里写图片描述

using EntityFramework.Extensions;
using EFSolution.Model.Entity;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Validation;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using LinqKit;

namespace EFSolution.Data.Entity
{
    /// <summary>
    /// 利用EntityFramework 的数据持久层基类。
    /// 如果需要ADO.NET 或其他方式连接数据库，则需要新增类，
    /// 实现“ IDataSource[TSource],IDataCommand[TSource]”接口
    /// </summary>
    /// <auther>2014-12-17</auther>
    /// <modify>
    /// 2014-12-19 稳定性重构
    /// 2014-12-20 批量删除、更新添加
    /// 2014-12-25 全文搜索
    /// 2014-12-26 全文搜索优化
    /// </modify>
    /// <typeparam name="TEntity">EF实体模型名称，参见配置文件“connectionStrings”项</typeparam>
    /// <typeparam name="TSource">表实体模型</typeparam>
    public class EntityContainer<TEntity, TSource> : IDataSource<TSource>, IDataCommand<TSource>,IFullTextSearch<TSource>
        where TSource : class,new()
        where TEntity : DbContext, new()
    {
        #region IDataSource

        #region Search

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="exp">条件lamda表达式</param>
        /// <returns>查询结果</returns>
        public virtual IEnumerable<TSource> Select(Expression<Func<TSource, bool>> exp)
        {
            //using (TEntity db = new TEntity())
            TEntity db = new TEntity();
            {
                try
                {
                    db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                    return db.Set<TSource>().AsExpandable().Where(exp).ToList();
                }
                catch (System.Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 计算总个数(分页)
        /// </summary>
        /// <param name="exp">Lambda条件的where</param>
        /// <returns>查询结果</returns>
        public virtual int Count(Expression<Func<TSource, bool>> exp)
        {
            using (TEntity db = new TEntity())
            {
                db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                return db.Set<TSource>().AsExpandable().Where(exp).Count();
            }
        }

        /// <summary>
        /// 分页查询(Linq分页方式)
        /// </summary>
        /// <param name="pageNumber">当前页</param>
        /// <param name="pageSize">页码</param>
        /// <param name="orderName">lambda排序名称</param>
        /// <param name="sortOrder">排序(升序or降序)</param>
        /// <param name="exp">lambda查询条件where</param>
        /// <returns>查询结果</returns>
        public virtual IEnumerable<TSource> SelectPaging(int pageNumber, int pageSize, Func<TSource, string> orderName, string sortOrder, Expression<Func<TSource, bool>> exp)
        {
            //using (TEntity db = new TEntity())
            TEntity db = new TEntity();
              {
                  db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                  if (sortOrder == "asc") //升序排列
                  {
                      return db.Set<TSource>().AsExpandable().Where(exp).OrderBy(orderName).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
                  }
                  else
                  {
                      return db.Set<TSource>().AsExpandable().Where(exp).OrderByDescending(orderName).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
                  }
              }

        }

        /// <summary>
        /// 根据条件查找
        /// </summary>
        /// <param name="exp">lambda查询条件where</param>
        /// <returns>结果实体</returns>
        public virtual TSource FirstOrDefault(Expression<Func<TSource, bool>> exp)
        {
            //using (TEntity db = new TEntity())
            TEntity db = new TEntity();
            {
                db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                return db.Set<TSource>().AsExpandable().FirstOrDefault(exp);
            }

        }

        #endregion

        /// <summary>
        /// 插入source
        /// </summary>
        /// <param name="source">数据实体</param>
        /// <returns>是否成功</returns>
        public virtual bool Add(TSource source)
        {
            using (TEntity db = new TEntity())
            {
                try
                {
                    var obj = db.Set<TSource>();
                    obj.Add(source);
                    return db.SaveChanges() > 0;
                }
                catch (System.Data.Entity.Validation.DbEntityValidationException ex)
                {
                    #region qzj
#if DEBUG
                    foreach (var item in ex.EntityValidationErrors)
                    {
                        foreach (var item2 in item.ValidationErrors)
                        {
                           string  error = string.Format("{0}:{1}\r\n", item2.PropertyName, item2.ErrorMessage);
                        }
                    }

#endif
                    #endregion
                    throw ex;
                }
            }
        }

        #region Update

        /// <summary>
        /// 指定字段更新
        /// </summary>
        /// <param name="action"></param>
        /// <returns></returns>
        public bool Update(TSource param)
        {
            return Update(param, null);
        }

        public bool Update(TSource param, Func<TSource, TSource> exp)
        {
            //构造新对象
            TSource source = new TSource();
            //非空字段赋值
            source = source.ToPreview<TSource>();
            string[] keyNames = null;
            using (TEntity db = new TEntity())
            {

                var obj = db.Set<TSource>();
                try
                {
                    //获取主键
                    var objectSet = ((IObjectContextAdapter)db).ObjectContext.CreateObjectSet<TSource>();
                    keyNames = objectSet.EntitySet.ElementType.KeyMembers
                                                       .Select(k => k.Name)
                                                       .ToArray();
                    source = param.CopyKey<TSource>(source, keyNames);
                    //自适应更新
                    obj.Attach(source);
                    //定向更新
                    source = param.CopyTo<TSource>(source, keyNames);
                    //针对默认值的处理
                    if (null != exp)
                        exp(source);
                    return db.SaveChanges() > 0;
                }
                catch (System.Data.Entity.Validation.DbEntityValidationException ex)
                {
                    // Retrieve the error messages as a list of strings.
                    var errorMessages = ex.EntityValidationErrors
                            .SelectMany(x => x.ValidationErrors)
                            .Select(x => x.ErrorMessage);
                    // Join the list to a single string.
                    var fullErrorMessage = string.Join("; ", errorMessages);
                    // Combine the original exception message with the new one.
                    var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                    // Throw a new DbEntityValidationException with the improved exception message.
                    throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

        }

        /// <summary>
        /// 按需更新数据
        /// </summary>
        /// <param name="model">数据实体，不需要更新的数据不要赋值</param>
        /// <returns>是否成功</returns>
        public virtual bool Update(TSource source, bool fullupdate)
        {
            if (!fullupdate)
                return Update(source);
            using (TEntity db = new TEntity())
            {
                var obj = db.Set<TSource>();
                try
                {
                    obj.Attach(source);
                    db.Entry(source).State = EntityState.Modified;
                    return db.SaveChanges() > 0;
                }
                catch (System.Data.Entity.Validation.DbEntityValidationException ex)
                {
                    // Retrieve the error messages as a list of strings.
                    var errorMessages = ex.EntityValidationErrors
                            .SelectMany(x => x.ValidationErrors)
                            .Select(x => x.ErrorMessage);
                    // Join the list to a single string.
                    var fullErrorMessage = string.Join("; ", errorMessages);
                    // Combine the original exception message with the new one.
                    var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                    // Throw a new DbEntityValidationException with the improved exception message.
                    throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
                }
            }
        }

        /// <summary>
        /// 更新满足条件的实体，返回更新实体的条数
        /// </summary>
        /// <typeparam name="source">更新的类型</typeparam>
        /// <param name="exp">更新的条件</param>
        /// <returns>bool</returns>
        public virtual bool Update(Expression<Func<TSource, TSource>> source, Expression<Func<TSource, bool>> exp)
        {
            using (TEntity db = new TEntity())
            {
                try
                {

#if Extension
                    IQueryable<TSource> obj = db.Set<TSource>();
                    //obj = obj.Where(exp);
                    int xx = obj.Where(exp).Update(source);
                    return db.SaveChanges() > 0;
#else
                    //查询条件表达式转换成SQL的条件语句
                    MySqlBuilder builder = new MySqlBuilder();
                    builder.Build(exp.Body);
                    string sqlCondition = builder.Condition;
                    //获取Update的赋值语句
                    var updateMemberExpr = (MemberInitExpression)source.Body;
                    var updateMemberCollection = updateMemberExpr.Bindings.Cast<MemberAssignment>().Select(c => new
                    {
                        Name = c.Member.Name,
                        Value = ((ConstantExpression)c.Expression).Value
                    });
                    int i = builder.Arguments.Length;
                    string sqlUpdateBlock = string.Join(", ", updateMemberCollection.Select(c => string.Format("{0}=@p{1}", c.Name, i)).ToArray());
                    string testcondition = string.Join(",", updateMemberCollection.Select(c => c.Name).ToArray());
                    //SQL命令
                    string commandText = string.Format("UPDATE {0} SET {1} WHERE {2}", typeof(TSource).Name, sqlUpdateBlock, sqlCondition);
                    //获取SQL参数数组 (包括查询参数和赋值参数)
                    var args = builder.Arguments.Union(updateMemberCollection.Select(c => c.Value)).ToArray();
                    var Result = db.Database.ExecuteSqlCommand(commandText,args) > 0;
                    return Result;
#endif
                }
                catch (System.Data.Entity.Validation.DbEntityValidationException ex)
                {
                    throw ex;
                }
            }
        }

        #endregion

        /// <summary>
        /// 删除满足条件的实体，返回删除实体的条数
        /// </summary>
        /// <param name="exp">删除的条件</param>
        /// <returns>int</returns>
        public virtual bool Remove(Expression<Func<TSource, bool>> exp)
        {
            using (TEntity db = new TEntity())
            {
#if Extension
                IQueryable<TSource> obj = db.Set<TSource>();
                obj.Where(exp).Delete();
                return false;
#elif Regex
                var set = db.Set<TSource>().AsQueryable();
                set = (exp == null) ? set : set.Where(exp);

                string sql = set.ToString().Replace("\r", "").Replace("\n", "").Trim();
                if (exp == null && !string.IsNullOrEmpty(sql) && !string.IsNullOrWhiteSpace(sql))
                    sql += " WHERE 1=1";

                Regex reg = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase);
                Match match = reg.Match(sql);

                if (!match.Success)
                    throw new ArgumentException("Cannot delete this type of collection");

                string table = match.Groups["Table"].Value.Trim();
                string tableAlias = match.Groups["TableAlias"].Value.Trim();
                string condition = match.Groups["Condition"].Value.Trim().Replace(tableAlias, table);

                string sql1 = string.Format("DELETE FROM {0} WHERE {1}", table, condition);

                return db.Database.ExecuteSqlCommand(sql1);
#else

                //查询条件表达式转换成SQL的条件语句
                MySqlBuilder builder = new MySqlBuilder();
                builder.Build(exp.Body);
                string sqlCondition = builder.Condition;
                //获取SQL参数数组 
                var args = builder.Arguments;

                string sql = string.Format("Delete  From  " + typeof(TSource).Name + " Where {0} ", sqlCondition);
                //return sqlCondition;//string.Format(sqlCondition,args);
                var Result = db.Database.ExecuteSqlCommand(sql,args) >0;
                return Result;
#endif
            }
        }

        #endregion

        #region IData

        /// <summary>
        /// 删除source
        /// </summary>
        /// <param name="source">数据实体</param>
        /// <returns>是否成功</returns>
        public virtual bool Remove(TSource source)
        {
            using (TEntity db = new TEntity())
            {
                var obj = db.Set<TSource>();
                if (source != null)
                {
                    obj.Attach(source);
                    db.Entry(source).State = EntityState.Deleted;
                    obj.Remove(source);
                    return db.SaveChanges() > 0;
                }
                return false;
            }

        }

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <typeparam name="TParame">查询参数泛型</typeparam>
        /// <param name="exp">查询条件lamda表达式</param>
        /// <returns>查询的结果</returns>
        public virtual IEnumerable<TSource> Read(Expression< Func<TSource, bool>> exp)
        {
            return Select(exp);
        }

        /// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="source">数据项实体对象</param>
        /// <returns>是否成功</returns>
        public virtual bool Write(TSource source)
        {
            return Add(source);
        }

        #endregion

        #region  IDataCommand

        /// <summary>
        /// 执行更新、删除等操作
        /// </summary>
        /// <param name="sql">sql指令</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNoQuerry(string sql, params object[] param)
        {
            using (TEntity db = new TEntity())
            {
                var obj = db.Set<TSource>();
                try
                {
                    return db.Database.ExecuteSqlCommand(sql, param);
                }
                catch (System.Data.Entity.Validation.DbEntityValidationException ex)
                {
                    // Retrieve the error messages as a list of strings.
                    var errorMessages = ex.EntityValidationErrors
                            .SelectMany(x => x.ValidationErrors)
                            .Select(x => x.ErrorMessage);
                    // Join the list to a single string.
                    var fullErrorMessage = string.Join("; ", errorMessages);
                    // Combine the original exception message with the new one.
                    var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                    // Throw a new DbEntityValidationException with the improved exception message.
                    throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sql">sql查询指令</param>
        /// <param name="param">查询参数列表</param>
        /// <returns>查询结果</returns>
        public IEnumerable<TSource> ExecuteScalar(string sql, params object[] param)
        {
            TEntity db = new TEntity();

            var obj = db.Set<TSource>();
            try
            {
                return db.Database.SqlQuery<TSource>(sql, param);
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        private string sqlpagerNew = @"SELECT * FROM ({sqlmain}) B {order0} LIMIT {skip},{pagesize}";//要拼装的SQL语句
        /// <summary>
        /// 分页查询(分页方式)
        /// </summary>
        /// <param name="pageNumber">当前页</param>
        /// <param name="pageSize">页码</param>
        /// <param name="orderName">排序名称</param>
        /// <param name="sortOrder">排序(升序or降序)</param>
        /// <param name="sql">查询语句</param>
        /// <param name="parame">查询条件where</param>
        /// <returns>查询结果</returns>
        public IEnumerable<TSource> ExecuteScalarPaging(int pageNumber, int pageSize, string orderName, string sortOrder, string srcSql, params object[] param)
        {
            srcSql = srcSql.Trim();
            if (srcSql.StartsWith("select ", StringComparison.CurrentCultureIgnoreCase) || srcSql.StartsWith("select\n", StringComparison.CurrentCultureIgnoreCase))
            {
                srcSql = sqlpagerNew.Replace("{sqlmain}", srcSql);
                srcSql = srcSql.Replace("{pagesize}", Convert.ToString(pageSize));
                srcSql = srcSql.Replace("{skip}", Convert.ToString((pageNumber - 1) * pageSize));
                if (orderName.Length > 0)
                {
                    srcSql = srcSql.Replace("{order0}", " order by " + orderName + " " + sortOrder);
                }
                else
                {
                    //srcSql = srcSql.Replace("{order0}", " order by id");
                    srcSql = srcSql.Replace("{order0}", " ");
                }
            }
            return ExecuteScalar(srcSql, param);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sql">sql查询指令</param>
        /// <param name="param">查询参数列表</param>
        /// <returns>查询结果</returns>
        public DataTable ExecuteSqlQuery(string sql, DbParameterCollection param)
        {
            try
            {
                using (ASMEntities db = new ASMEntities())
                {
                    var obj = db.Set<auth_users>();
                    DataTable dt = null;
                    //从数据库对象中创建命令对象
                    using (var cmd = db.Database.Connection.CreateCommand())
                    {
                        //打开连接，using后释放
                        db.Database.Connection.Open();
                        cmd.CommandText = sql;
                        //参数赋值
                        if (null != param)
                            foreach (var p in param)
                                cmd.Parameters.Add(p);
                        //逐条执行
                        using (var reader = cmd.ExecuteReader())
                        {
                            //构造datatable对象
                            if (null == dt)
                            {
                                dt = new DataTable();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    //构造列
                                    DataColumn dc = new DataColumn(reader.GetName(i));
                                    dt.Columns.Add(dc);
                                }
                                dt.AcceptChanges();
                            }
                            //循环赋值各行信息
                            while (reader.Read())
                            {
                                DataRow dr = dt.NewRow();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    dr[i] = reader.GetValue(i);
                                }
                                dt.Rows.Add(dr);
                            }
                            return dt;
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sql">sql查询指令</param>
        /// <param name="param">查询参数列表</param>
        /// <returns>查询结果</returns>
        public DataTable ExecuteSqlQuery(string sql, params object[] param)
        {
            try
            {
                using (ASMEntities db = new ASMEntities())
                {
                    var obj = db.Set<auth_users>();
                    DataTable dt = null;
                    //从数据库对象中创建命令对象
                    using (var cmd = db.Database.Connection.CreateCommand())
                    {
                        //打开连接，using后释放
                        db.Database.Connection.Open();
                        cmd.CommandText = sql;
                        //参数赋值
                        if (null != param)
                            foreach (var p in param)
                                cmd.Parameters.Add(p);
                        //逐条执行
                        using (var reader = cmd.ExecuteReader())
                        {
                            //构造datatable对象
                            if (null == dt)
                            {
                                dt = new DataTable();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    //构造列
                                    DataColumn dc = new DataColumn(reader.GetName(i));
                                    dt.Columns.Add(dc);
                                }
                                dt.AcceptChanges();
                            }
                            //循环赋值各行信息
                            while (reader.Read())
                            {
                                DataRow dr = dt.NewRow();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    dr[i] = reader.GetValue(i);
                                }
                                dt.Rows.Add(dr);
                            }
                            return dt;
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region IFullTextSearch #Mysql

        #region sqlText

        /// <summary>
        /// 获取给定条件的全文搜索数量
        /// 服务于分页
        /// </summary>
        private string _sqlFullTestCount = "SELECT count(0) FROM {0} WHERE (MATCH ({1}) AGAINST (@p{3} IN BOOLEAN MODE )) and {2}";

        /// <summary>
        /// 获取全文搜索的字段，在全文搜索执行前，
        /// 需要给定参与全文搜索的字段
        /// </summary>
        private string _sqlGetFullTextColumns = @"select group_concat(distinct column_name) from information_schema.STATISTICS  where table_schema = '{0}'  and table_name = '{1}'  and index_type = 'FULLTEXT'";

        /// <summary>
        /// 执行全文搜索的SQL
        /// </summary>
        private string _sqlFullTextSelect = @"SELECT * FROM {0} WHERE (MATCH ({1}) AGAINST (@p{3} IN BOOLEAN MODE )) and {2} ";

        /// <summary>
        /// 添加一个字段到全文搜索中
        /// </summary>
        private string _sqlCreateFullTextColumn = @"ALTER TABLE {0} ADD FULLTEXT {1} ({2})";

        /// <summary>
        /// 从基表中获取当前表所有全文搜索的索引
        /// </summary>
        private string _sqlGetCurruntColumnFullTextIndex = @"select * from information_schema.STATISTICS  
where table_schema = @p0  and 
table_name = @p1  and index_type = 'FULLTEXT' ";

        /// <summary>
        /// 移除一张表的全文搜索索引
        /// </summary>
        private string _sqlDropFullTextColumn = @"ALTER TABLE {0}  DROP INDEX {1} ";

        /// <summary>
        /// 在移除的同时，可能误删了其他字段，必须将其他字段还原，重新创建
        /// </summary>
        private string _sqlReAddFullTextExceptColumns = @",ADD FULLTEXT {2} ({3}) ";

        #endregion

        /// <summary>
        /// 全文搜索
        /// </summary>
        /// <param name="keyword">关键字</param>
        /// <param name="exp">补充条件</param>
        /// <returns>结果</returns>
        public IEnumerable<TSource> FullTextSelect(string keyword, Expression<Func<TSource, bool>> exp)
        {
            string tablename = string.Empty;
            string indexcolumns = string.Empty;
            string condition = string.Empty;
            string databasename = string.Empty;
            List<object> args = new List<object>();

            TEntity db = new TEntity();

            var obj = db.Set<TSource>();
            try
            {
                databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog;  
                tablename = typeof(TSource).Name;
                //获取数据库中指定表的全文搜索字段
                string sql = string.Format(_sqlGetFullTextColumns, databasename, tablename);
                var columns = db.Database.SqlQuery<string>(sql, string.Empty).FirstOrDefault();
                //jq修改,如果没有加索引，那么就返回一个空的LIST
                if (string.IsNullOrEmpty(columns))
                {
                    List<TSource> nullList = new List<TSource>();
                    return nullList;
                }

                //获取其他条件
                if (null == exp)
                {
                    condition = " 1 = 1 ";
                }
                else
                {
                    MySqlBuilder builder = new MySqlBuilder();
                    builder.Build(exp.Body);
                    //获取SQL参数数组 
                    args.AddRange(builder.Arguments);
                    condition = builder.Condition;
                }
                //添加全文搜索关键字参数,客户端可能传递带空格的多个参数，默认参数查询“与”条件
                string[] keywords = keyword.Split(' ').Where(p =>!string.IsNullOrEmpty(p)).Select(q=>"+"+q+"*").ToArray();
                string realkeyword = string.Join(" ", keywords);
                args.Add(realkeyword);
                //拼装查询语句
                string sqlQuerry = string.Format(_sqlFullTextSelect, tablename, columns, condition, (args.Count-1).ToString());
                //执行SQL
                return db.Database.SqlQuery<TSource>(sqlQuerry, args.ToArray());
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取全文搜索结果数量
        /// 一般结合分页使用
        /// </summary>
        /// <param name="keyword">关键字</param>
        /// <param name="exp">lambda表达式</param>
        /// <returns>总数</returns>
        public int FullTextCount(string keyword, Expression<Func<TSource, bool>> exp)
        {
            string tablename = string.Empty;
            string indexcolumns = string.Empty;
            string condition = string.Empty;
            string databasename = string.Empty;
            List<object> args = new List<object>();
            TEntity db = new TEntity();

            var obj = db.Set<TSource>();
            try
            {
                databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog;
                tablename = typeof(TSource).Name;
                //获取数据库中指定表的全文搜索字段
                string sql = string.Format(_sqlGetFullTextColumns, databasename, tablename);
                var columns = db.Database.SqlQuery<string>(sql, string.Empty).FirstOrDefault();
                if (string.IsNullOrEmpty(columns))
                    return 0;
                //获取其他条件
                if (null == exp)
                {
                    condition = " 1 = 1 ";
                }
                else
                {
                    MySqlBuilder builder = new MySqlBuilder();
                    builder.Build(exp.Body);
                    //获取SQL参数数组 
                    args.AddRange(builder.Arguments);
                    condition = builder.Condition;
                }
                //添加全文搜索关键字参数,客户端可能传递带空格的多个参数，默认参数查询“与”条件
                string[] keywords = keyword.Split(' ').Where(p => !string.IsNullOrEmpty(p)).Select(q => "+" + q + "*").ToArray();
                string realkeyword = string.Join(" ", keywords);
                args.Add(realkeyword);
                //拼装查询语句
                string sqlQuerry = string.Format(_sqlFullTestCount, tablename, columns, condition, (args.Count - 1).ToString());
                //执行SQL
                var result = db.Database.SqlQuery<string>(sqlQuerry, args.ToArray()).ToList();
                if (null == result || result.Count == 0)
                    return 0;
                return int.Parse(result[0]);
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 全文搜索（分页）
        /// </summary>
        /// <param name="pageNumber">页号</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="orderName">排序字段</param>
        /// <param name="sortOrder">排序类型</param>
        /// <param name="keyword">关键字</param>
        /// <param name="exp">表达式</param>
        /// <returns></returns>
        public IEnumerable<TSource> FullTextSelectPaging(int pageNumber, int pageSize, string orderName, string sortOrder, string keyword, Expression<Func<TSource, bool>> exp)
        {
            string sqlQuerry = string.Empty;
            List<object> args = new List<object>();

            using (TEntity db = new TEntity())
            {
                string tablename = string.Empty;
                string indexcolumns = string.Empty;
                string condition = string.Empty;
                string databasename = string.Empty;

                var obj = db.Set<TSource>();
                databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog;
                tablename = typeof(TSource).Name;
                //获取数据库中指定表的全文搜索字段
                string sql = string.Format(_sqlGetFullTextColumns, databasename, tablename);
                var columns = db.Database.SqlQuery<string>(sql, string.Empty).FirstOrDefault();
                if (string.IsNullOrEmpty(columns))
                    return null;
                //获取其他条件
                if (null == exp)
                {
                    condition = " 1 = 1 ";
                }
                else
                {
                    MySqlBuilder builder = new MySqlBuilder();
                    builder.Build(exp.Body);
                    //获取SQL参数数组 
                    args.AddRange(builder.Arguments);
                    condition = builder.Condition;
                }
                //添加全文搜索关键字参数
                args.Add(keyword + "*");
                //拼装查询语句
                sqlQuerry = string.Format(_sqlFullTextSelect, tablename, columns, condition, (args.Count - 1).ToString());
            }
            if(string.IsNullOrEmpty(sqlQuerry))
                return null;
            return ExecuteScalarPaging(pageNumber, pageSize, orderName, sortOrder, sqlQuerry, args.ToArray());
        }

        /// <summary>
        /// 基表中的索引和字段信息数据结构
        /// 服务于删除功能
        /// </summary>
        private class CurruntColumnFullTextIndex
        {
            public string INDEX_NAME { get; set; }

            public string COLUMN_NAME { get; set; }
        }

        /// <summary>
        /// 添加一个字段到全文搜索中
        /// </summary>
        /// <param name="exp">具体字段 p=>p.Name</param>
        /// <returns>是否成功</returns>
        public bool FullTextAdd(Expression<Func<TSource, string>> exp)
        {
            try
            {
                var updateMemberExpr = (MemberExpression)exp.Body;
                using (TEntity db = new TEntity())
                {
                    var obj = db.Set<TSource>();
                    TSource source = new TSource();
                    string column = updateMemberExpr.Member.Name;
                    string indexFullTextName = "FT_" + column;
                    _sqlCreateFullTextColumn = string.Format(_sqlCreateFullTextColumn, typeof(TSource).Name, indexFullTextName, column);
                    return db.Database.ExecuteSqlCommand(_sqlCreateFullTextColumn) >0;
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 添加指定表格，指定字段到全文索引
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strCollmnName"></param>
        /// <returns></returns>
        public bool FullTextAdd(string strCollmnName)
        {
            try
            {
                using (TEntity db = new TEntity())
                {
                   var  databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog;
                   var allIndexInCurruntTable = db.Database.SqlQuery<CurruntColumnFullTextIndex>(_sqlGetCurruntColumnFullTextIndex,
                                                                   new string[] { databasename, typeof(TSource).Name }).ToList();
                    foreach (var tableIndex in allIndexInCurruntTable)
                    {
                        if (tableIndex.COLUMN_NAME.Equals(strCollmnName))
                            return true;
                    }
                    string indexFullTextName = "FT_" + strCollmnName;
                    _sqlCreateFullTextColumn = string.Format(_sqlCreateFullTextColumn, typeof(TSource).Name, indexFullTextName, strCollmnName);
                    return db.Database.ExecuteSqlCommand(_sqlCreateFullTextColumn) > 0;
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 从全文检索中移除一个字段
        /// </summary>
        /// <param name="exp">具体字段 p=>p.Name</param>
        /// <returns>是否成功</returns>
        public bool FullTextRemove(Expression<Func<TSource, string>> exp)
        {
            try
            {
                string databasename = string.Empty;
                string tablename = string.Empty;

                var updateMemberExpr = (MemberExpression)exp.Body;
;
                using (TEntity db = new TEntity())
                {
                    var obj = db.Set<TSource>();
                    TSource source = new TSource();
                    string column = updateMemberExpr.Member.Name;
                    //第一步，获取系统中所有该字段已建立的全文搜索索引
                    databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog;
                    tablename = typeof(TSource).Name;
                    var allIndexInCurruntTable = db.Database.SqlQuery<CurruntColumnFullTextIndex>(_sqlGetCurruntColumnFullTextIndex,
                                                                    new string[] { databasename, tablename}).ToList();
                    if(allIndexInCurruntTable.Count ==0)
                        return true;
                    var curruntColumnIndexs = allIndexInCurruntTable.Where(p => p.COLUMN_NAME.Equals(column)).Select(q=>q.INDEX_NAME).Distinct();
                    if (curruntColumnIndexs.Count() == 0)
                        return true;
                    //记录除当前字段外的所有索引名称和索引包含的列
                    var exceptColumnIndexs = allIndexInCurruntTable.Where(p=>curruntColumnIndexs.Contains(p.INDEX_NAME) &&!p.COLUMN_NAME.Equals(column));
                    //第二步启用事务
                    using (var trans = db.Database.BeginTransaction())
                    {
                        try
                        {
                            //第三步删除当前字段所在的索引,重新创建索引
                            foreach (var indexname in curruntColumnIndexs)
                            {
                                //删除当前字段所在索引
                                string sql = _sqlDropFullTextColumn;
                                string curruntIndexExceptColumns = string.Join(", ", 
                                         exceptColumnIndexs.Where(p => p.INDEX_NAME.Equals(indexname)).Select(p=>p.COLUMN_NAME));
                                if (!string.IsNullOrEmpty(curruntIndexExceptColumns))
                                    sql += _sqlReAddFullTextExceptColumns;
                                List<object> args = new List<object>();
                                args.Add(tablename);
                                args.Add(indexname);
                                args.Add(indexname);
                                args.Add(curruntIndexExceptColumns);
                                sql = string.Format(sql, args.ToArray());
                                //无法防注入
                                db.Database.ExecuteSqlCommand(sql);
                            }
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            throw ex;
                        }
                        return true;
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 把指定表格的全文索引列删除
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strCollmnName"></param>
        /// <returns></returns>
        public bool FullTextRemove(string strCollmnName)
        {
            try
            {
                string databasename = string.Empty;
                string tablename = string.Empty;

                //var updateMemberExpr = (MemberExpression)exp.Body;
                using (TEntity db = new TEntity())
                {
                    //var obj = db.Set<TSource>();
                    //TSource source = new TSource();
                    //string column = updateMemberExpr.Member.Name;
                    //第一步，获取系统中所有该字段已建立的全文搜索索引
                    databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog;
                    //tablename = typeof(TSource).Name;

                    string column = strCollmnName;
                    tablename = typeof(TSource).Name;


                    var allIndexInCurruntTable = db.Database.SqlQuery<CurruntColumnFullTextIndex>(_sqlGetCurruntColumnFullTextIndex,
                                                                    new string[] { databasename, tablename }).ToList();
                    if (allIndexInCurruntTable.Count == 0)
                        return true;
                    var curruntColumnIndexs = allIndexInCurruntTable.Where(p => p.COLUMN_NAME.Equals(column)).Select(q => q.INDEX_NAME).Distinct();
                    if (curruntColumnIndexs.Count() == 0)
                        return true;
                    //记录除当前字段外的所有索引名称和索引包含的列
                    var exceptColumnIndexs = allIndexInCurruntTable.Where(p => curruntColumnIndexs.Contains(p.INDEX_NAME) && !p.COLUMN_NAME.Equals(column));
                    //第二步启用事务
                    using (var trans = db.Database.BeginTransaction())
                    {
                        try
                        {
                            //第三步删除当前字段所在的索引,重新创建索引
                            foreach (var indexname in curruntColumnIndexs)
                            {
                                //删除当前字段所在索引
                                string sql = _sqlDropFullTextColumn;
                                string curruntIndexExceptColumns = string.Join(", ",
                                         exceptColumnIndexs.Where(p => p.INDEX_NAME.Equals(indexname)).Select(p => p.COLUMN_NAME));
                                if (!string.IsNullOrEmpty(curruntIndexExceptColumns))
                                    sql += _sqlReAddFullTextExceptColumns;
                                List<object> args = new List<object>();
                                args.Add(tablename);
                                args.Add(indexname);
                                args.Add(indexname);
                                args.Add(curruntIndexExceptColumns);
                                sql = string.Format(sql, args.ToArray());
                                //无法防注入
                                db.Database.ExecuteSqlCommand(sql);
                            }
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            throw ex;
                        }
                        return true;
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);
                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);
                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);
                // Throw a new DbEntityValidationException with the improved exception message.
                throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}