EF批量添加，删除，修改的扩展

在EF各版本中，没有相应批量的添加，删除，修改，在用ORM 处理数据时直有个尴尬。在网上，也接到了很多网友的询问这方面的情况，特此今天把这方面的相关扩展分享一下，（这里只做批量删除的例子，添加和修改的思路雷同）

一、原由

     在先前的SQL操作中，我们要

update table set  cl=1 where  id>5 and id<100

delete from table where  id>5 and id<100

但是在EF中没有提供相应的接口，我们只能这样

 

//批量删除
foreach(var user in context.table.Where(u => u.id>5 and id<100).ToList())
{
   context.Remove(user);
}

 

本来一句sql可以解决的问题，现在变得复杂了。

二，扩展思路

     虽然EF没有提供的接口中，不过我们可以进行一个扩展（EF里面是指定自己SQL语句 context.Database.ExecuteSqlCommand（sql,args）），思路是这样的

    通过EF扩展 生成 SQL语句 让EF来执行SQL

三，具体实现代码(自己扩展的实现，具体看源码)

  1.应用代码

 

     DB<MdStudent> db = new DB<MdStudent>();
                     db.Remove(u => u.id>5 and id<100);

 

2.代码分析
     DB<MdStudent> db = new DB<MdStudent>();//这实例化一个context类，这类里封装了EF方法及扩展

        db.Remove(u => u.id>5 and id<100);//这里主要执行了三个操作，1.确认是哪个表，2，Lambda生成SQL，这里用到了ConditionBuilder类，PartialEvaluator类来解析成SQL

     以上是PartialEvaluator里的部分解析代码

   

        public void Build(Expression expression)
        {
            PartialEvaluator evaluator = new PartialEvaluator();
            Expression evaluatedExpression = evaluator.Eval(expression);

            this.m_arguments = new List<object>();
            this.m_conditionParts = new Stack<string>();

            this.Visit(evaluatedExpression);

            this.Arguments = this.m_arguments.ToArray();
            this.Condition = this.m_conditionParts.Count > 0 ? this.m_conditionParts.Pop() : null;
        }

        protected override Expression VisitBinary(BinaryExpression b)
        {
            if (b == null) return b;

            string opr;
            switch (b.NodeType)
            {
                case ExpressionType.Equal:
                    opr = "=";
                    break;
                case ExpressionType.NotEqual:
                    opr = "<>";
                    break;
                case ExpressionType.GreaterThan:
                    opr = ">";
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    opr = ">=";
                    break;
                case ExpressionType.LessThan:
                    opr = "<";
                    break;
                case ExpressionType.LessThanOrEqual:
                    opr = "<=";
                    break;
                case ExpressionType.AndAlso:
                    opr = "AND";
                    break;
                case ExpressionType.OrElse:
                    opr = "OR";
                    break;
                case ExpressionType.Add:
                    opr = "+";
                    break;
                case ExpressionType.Subtract:
                    opr = "-";
                    break;
                case ExpressionType.Multiply:
                    opr = "*";
                    break;
                case ExpressionType.Divide:
                    opr = "/";
                    break;
                default:
                    throw new NotSupportedException(b.NodeType + "is not supported.");
            }
            this.Visit(b.Left);
            this.Visit(b.Right);
            string right = this.m_conditionParts.Pop();
            string left = this.m_conditionParts.Pop();
            string condition = String.Format("({0} {1} {2})", left, opr, right);
            this.m_conditionParts.Push(condition);
            return b;
        }

        protected override Expression VisitConstant(ConstantExpression c)
        {
            if (c == null) return c;
            this.m_arguments.Add(c.Value);
            this.m_conditionParts.Push(String.Format("{{{0}}}", this.m_arguments.Count - 1));
            return c;
        }
        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            if (m == null) return m;
            PropertyInfo propertyInfo = m.Member as PropertyInfo;
            if (propertyInfo == null) return m;
            this.m_conditionParts.Push(String.Format("[{0}]", propertyInfo.Name));
            return m;
        }

 

3.组装SQL

 

 /// <summary>
        /// 删除扩展[优化删除]
        /// </summary>
        public static int DeleteEntity<Entity>(this EFDbContext<Entity> Context, Expression<Func<Entity, bool>> Predicate) where Entity : class,IEntity
        {
            Command cmd = GetCommands<Entity>(Predicate);
            int Result = Context.Database.ExecuteSqlCommand(cmd.Text, cmd.args);
            return Result;
        }
        public static CommSql GetDeleteSql<Entity>(this EFDbContext<Entity> Context, Expression<Func<Entity, bool>> Predicate, bool IsFag) where Entity : class,IEntity
        {
            Command com = GetCommands<Entity>(Predicate);
            string CommText = com.Text;
            object[] args = com.args;
            for (int j = 0; j < args.Count(); j++)
            {
                if (!(args[j].GetType() != "Type".GetType()))
                {
                    args[j] = "'" + args[j] + "'";
                }
            }
            if (args.Count() > 0)
            {
                CommText = string.Format(CommText, args);
            }
            CommSql cmd = new CommSql();
            cmd.Text = CommText;
            if (IsFag)
                cmd.ComNum = Context.Database.ExecuteSqlCommand(com.Text, com.args);
            return cmd;
        }
        private static Command GetCommands<Entity>(Expression<Func<Entity, bool>> Predicate) where Entity : class,IEntity
        {
            //根据条件表达式转换成SQL的条件语句
            ConditionBuilder Builder = new ConditionBuilder();
            Builder.Build(Predicate.Body);
            string sqlCondition = Builder.Condition;
            //获取SQL参数数组 
            string Table = Operate.getTableName<Entity>();
            string CommText = "Delete  From  [" + Table + "] Where " + sqlCondition;
            var args = Builder.Arguments;
            return new Command() { Text = CommText, args = args };
        }

 

以上只是部分代码，详细看扩展代码及实现例子 https://files.cnblogs.com/gzalrj/EF5.rar