使用Asp.Net Core MVC 開發項目實踐[第四篇:基於EF Core的擴展2]

上篇我們說到了基於EFCore的基礎擴展,這篇我們講解下基於實體結合拉姆達表達式的自定義更新以及刪除數據. 先說下原理:其實通過實體以及拉姆達表達式生成SQL語句去執行第一種更新擴展: 自定義更新欄位以及自定義擴展條件,請看下麵的代碼從上面的方法中我們看到幾個參數,第一個參數不必說,擴展方法第一 ...

上篇我們說到了基於EFCore的基礎擴展,這篇我們講解下基於實體結合拉姆達表達式的自定義更新以及刪除數據.

先說下原理:其實通過實體以及拉姆達表達式生成SQL語句去執行

第一種更新擴展:

自定義更新欄位以及自定義擴展條件,請看下麵的代碼

 1         /// <summary>
 2         /// 自定義更新擴展
 3         /// </summary>
 4         /// <typeparam name="TEntity"></typeparam>
 5         /// <param name="context"></param>
 6         /// <param name="fields">更新欄位</param>
 7         /// <param name="predicate">更新條件</param>
 8         /// <returns></returns>
 9         public static bool MangoUpdate<TEntity>(this DbContext context, Expression<Func<TEntity, bool>> fields, Expression<Func<TEntity, bool>> predicate) where TEntity : class, new()
10         {
11             TSqlAssembledResult result = TSqlAssembled.Update<TEntity>(fields, predicate);
12             context.Database.ExecuteSqlCommand(result.SqlStr);
13             return context.SaveChanges() > 0 ? true : false;
14         }

從上面的方法中我們看到幾個參數,第一個參數不必說,擴展方法第一個參數必須要的,我們重點講清楚一下第二個和第三個參數.

參數:

Expression<Func<TEntity, bool>> fields

表示實體中需要更新的欄位,這裡的參數要求的是一個拉姆達表達式,如下麵的代碼：

m => m.ClickCount == m.ClickCount + 1

這裡就是更新欄位ClickCount+1的功能.

參數:

Expression<Func<TEntity, bool>> predicate

表示更新條件,這個參數也是一個拉姆達表達式,如下麵代碼:

m => m.NavigationId == navigationId

這裡表示更新條件 NavigationId指定值的資料庫記錄.

接下來我們看方法中的調用

 TSqlAssembled.Update<TEntity>(fields, predicate);

這個方法表示將參數解析成SQL語句,我們看看這個方法的具體內容:

 1         /// <summary>
 2         /// 更新語句組裝
 3         /// </summary>
 4         /// <typeparam name="TEntity"></typeparam>
 5         /// <param name="fields"></param>
 6         /// <param name="predicate"></param>
 7         /// <returns></returns>
 8         public static TSqlAssembledResult Update<TEntity>(Expression<Func<TEntity, bool>> fields, Expression<Func<TEntity, bool>> predicate) where TEntity : class, new()
 9         {
10             try
11             {
12                 StringBuilder strBuilder = new StringBuilder();
13                 strBuilder.Append("update ");
14                 strBuilder.Append(typeof(TEntity).Name);
15                 strBuilder.Append(" set ");
16                 //解析需要更新的欄位值
17                 UpdateFieldBuilder updateFieldBuilder = new UpdateFieldBuilder();
18                 strBuilder.Append(updateFieldBuilder.Translate(fields));
19                 //解析條件
20                 ConditionBuilder conditionBuilder = new ConditionBuilder();
21                 strBuilder.Append(" where ");
22                 strBuilder.Append(conditionBuilder.Translate(predicate));
23                 //處理結果返回
24                 TSqlAssembledResult result = new TSqlAssembledResult();
25                 result.SqlParameters = null;
26                 result.SqlStr = strBuilder.ToString();
27                 return result;
28             }
29             catch(Exception ex)
30             {
31                 return null;
32                 throw ex;
33             }
34         }

PS：這個方法中用到的條件編譯類以及欄位編輯類我們將在文章底部貼出來.

第二種更新擴展：

        /// <summary>
        /// 自定義更新擴展
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="context"></param>
        /// <param name="entity">更新實體</param>
        /// <param name="predicate">更新條件</param>
        /// <returns></returns>
        public static bool MangoUpdate<TEntity>(this DbContext context, TEntity entity, Expression<Func<TEntity, bool>> predicate) where TEntity:class,new()
        {
            TSqlAssembledResult result = TSqlAssembled.Update<TEntity>(entity, predicate);
            context.Database.ExecuteSqlCommand(result.SqlStr, result.SqlParameters);
            return context.SaveChanges() > 0 ? true : false;
        }

參數 TEntity entity表示需要更新的實體

參數 Expression<Func<TEntity, bool>> predicate 表示更新條件,示例如下:

m => m.NavigationId == navigationId

TSqlAssembled.Update<TEntity>(entity, predicate) 這個方法表示將參數解析成SQL語句,我們看看這個方法的具體內容:

        /// <summary>
        /// 更新語句組裝
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public static TSqlAssembledResult Update<TEntity>(TEntity entity, Expression<Func<TEntity, bool>> predicate) where TEntity : class, new()
        {
            try
            {
                StringBuilder strBuilder = new StringBuilder();
                strBuilder.Append("update ");
                //
                Type type = entity.GetType();
                strBuilder.Append(type.Name);
                strBuilder.Append(" set ");
                //處理實體類屬性
                PropertyInfo[] properties = type.GetProperties();
                int index = 0;
                List<SqlParameter> sqlParameter = new List<SqlParameter>();
                foreach (var property in properties)
                {
                    object value = property.GetValue(entity, null);
                    if (value != null)
                    {
                        if (index != 0)
                        {
                            strBuilder.Append(",");
                        }
                        strBuilder.Append(property.Name);
                        strBuilder.Append("=@");
                        strBuilder.Append(property.Name);

                        sqlParameter.Add(new SqlParameter(property.Name, value));
                        index++;
                    }
                }
                //編譯條件
                ConditionBuilder conditionBuilder = new ConditionBuilder();
                strBuilder.Append(" where ");
                strBuilder.Append(conditionBuilder.Translate(predicate));
                //處理結果返回
                TSqlAssembledResult result = new TSqlAssembledResult();
                result.SqlParameters = sqlParameter.ToArray();
                result.SqlStr = strBuilder.ToString();
                return result;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
        }

PS：這裡我們多了將實體反射獲取需要更新的欄位以及欄位值.

第三種刪除擴展:

自定刪除條件,代碼如下

 1         /// <summary>
 2         /// 自定義刪除擴展
 3         /// </summary>
 4         /// <typeparam name="TEntity"></typeparam>
 5         /// <param name="context"></param>
 6         /// <param name="predicate">刪除條件</param>
 7         /// <returns></returns>
 8         public static bool MangoRemove<TEntity>(this DbContext context,Expression<Func<TEntity, bool>> predicate) where TEntity : class,new()
 9         {
10             TSqlAssembledResult result = TSqlAssembled.Delete<TEntity>(predicate);
11             context.Database.ExecuteSqlCommand(result.SqlStr);
12             return context.SaveChanges() > 0 ? true : false;
13         }

參數Expression<Func<TEntity, bool>> predicate表示為自定義條件,示例如下:

_dbContext.MangoRemove<Entity.m_PostsAnswerRecords>(m => m.AnswerId == model.AnswerId && m.UserId == model.UserId);

PS:此段代碼表示根據指定條件刪除m_PostsAnswerRecords表中的記錄

TSqlAssembled.Delete<TEntity>(predicate)方法負責將指定條件編譯成SQL語句,代碼如下:

        /// <summary>
        /// 刪除語句組裝
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public static TSqlAssembledResult Delete<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity:class,new()
        {
            try
            {
                string tableName = typeof(TEntity).Name;
                //條件編譯
                ConditionBuilder conditionBuilder = new ConditionBuilder();
                string conditionStr = conditionBuilder.Translate(predicate);
                StringBuilder strBuilder = new StringBuilder();
                strBuilder.Append("delete from ");
                strBuilder.Append(tableName);
                strBuilder.Append(" where ");
                strBuilder.Append(conditionStr);
                //處理結果返回
                TSqlAssembledResult result = new TSqlAssembledResult();
                result.SqlParameters = null;
                result.SqlStr = strBuilder.ToString();
                return result;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

下麵我們貼出欄位以及條件的拉姆達表達式解析類:

條件解析類(ConditionBuilder)：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Linq;
  5 using System.Linq.Expressions;
  6 using System.Reflection;
  7 namespace Mango.Framework.EFCore
  8 {
  9     public class ConditionBuilder : ExpressionVisitor
 10     {
 11 
 12         StringBuilder strBuilder;
 13 
 14         public ConditionBuilder()
 15         {
 16         }
 17 
 18         public string Translate(Expression expression)
 19         {
 20             this.strBuilder = new StringBuilder();
 21             this.Visit(expression);
 22             return this.strBuilder.ToString();
 23         }
 24 
 25         private static Expression StripQuotes(Expression e)
 26         {
 27             while (e.NodeType == ExpressionType.Quote)
 28             {
 29                 e = ((UnaryExpression)e).Operand;
 30             }
 31             return e;
 32         }
 33 
 34         protected override Expression VisitBinary(BinaryExpression b)
 35         {
 36             strBuilder.Append("(");
 37             this.Visit(b.Left);
 38             switch (b.NodeType)
 39             {
 40                 case ExpressionType.AndAlso:
 41                     strBuilder.Append(" and ");
 42                     break;
 43                 case ExpressionType.OrElse:
 44                     strBuilder.Append(" or ");
 45                     break;
 46                 case ExpressionType.Equal:
 47                     strBuilder.Append(" = ");
 48                     break;
 49                 case ExpressionType.NotEqual:
 50                     strBuilder.Append(" <> ");
 51                     break;
 52                 case ExpressionType.LessThan:
 53                     strBuilder.Append(" < ");
 54                     break;
 55                 case ExpressionType.LessThanOrEqual:
 56                     strBuilder.Append(" <= ");
 57                     break;
 58                 case ExpressionType.GreaterThan:
 59                     strBuilder.Append(" > ");
 60                     break;
 61                 case ExpressionType.GreaterThanOrEqual:
 62                     strBuilder.Append(" >= ");
 63                     break;
 64                 default:
 65                     throw new NotSupportedException(string.Format("運算符{0}不支持", b.NodeType));
 66             }
 67             if (b.Right.NodeType != ExpressionType.Parameter&& b.Right.NodeType == ExpressionType.MemberAccess)
 68             {
 69                 LambdaExpression lambda = Expression.Lambda(b.Right);
 70                 var fn = lambda.Compile();
 71                 this.Visit(Expression.Constant(fn.DynamicInvoke(null), b.Right.Type));
 72             }
 73             else
 74             { 
 75                 this.Visit(b.Right);
 76             }
 77             strBuilder.Append(")");
 78             return b;
 79         }
 80 
 81         protected override Expression VisitConstant(ConstantExpression c)
 82         {
 83             switch (Type.GetTypeCode(c.Value.GetType()))
 84             {
 85                 case TypeCode.Boolean:
 86                     strBuilder.Append(((bool)c.Value) ? 1 : 0);
 87                     break;
 88                 case TypeCode.String:
 89                     strBuilder.Append("'");
 90                     strBuilder.Append(c.Value);
 91                     strBuilder.Append("'");
 92                     break;
 93                 case TypeCode.Object:
 94                     throw new NotSupportedException(string.Format("常量{0}不支持", c.Value));
 95                 default:
 96                     strBuilder.Append(c.Value);
 97                     break;
 98             }
 99             return c;
100         }
101 
102         protected override Expression VisitMember(MemberExpression m)
103         {
104             if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
105             {
106                 strBuilder.Append(m.Member.Name);
107                 return m;
108             }
109             else if (m.Expression != null && m.Expression.NodeType == ExpressionType.Constant)
110             {
111                 LambdaExpression lambda = Expression.Lambda(m);
112                 var fn = lambda.Compile();
113                 this.Visit(Expression.Constant(fn.DynamicInvoke(null), m.Type));
114                 return m;
115             }
116             throw new NotSupportedException(string.Format("成員{0}不支持", m.Member.Name));
117         }
118     }
119 }

更新欄位解析類(UpdateFieldBuilder):

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 using System.Linq;
 5 using System.Linq.Expressions;
 6 using System.Reflection;
 7 namespace Mango.Framework.EFCore
 8 {
 9     public class UpdateFieldBuilder : ExpressionVisitor
10     {
11         StringBuilder strBuilder;
12         public string Translate(Expression expression)
13         {
14             this.strBuilder = new StringBuilder();
15             this.Visit(expression);
16             return this.strBuilder.ToString();
17         }
18 
19         private static Expression StripQuotes(Expression e)
20         {
21             while (e.NodeType == ExpressionType.Quote)
22             {
23                 e = ((UnaryExpression)e).Operand;
24             }
25             return e;
26         }
27         protected override Expression VisitBinary(BinaryExpression b)
28         {
29             //strBuilder.Append("(");
30             this.Visit(b.Left);
31             switch (b.NodeType)
32             {
33                 case ExpressionType.Equal:
34                     strBuilder.Append("=");
35                     break;
36                 case ExpressionType.AndAlso:
37                     strBuilder.Append(",");
38                     break;
39                 case ExpressionType.Add:
40                     strBuilder.Append("+");
41                     break;
42                 case ExpressionType.Subtract:
43                     strBuilder.Append("-");
44                     break;
45                 default:
46                     throw new NotSupportedException(string.Format("運算符{0}不支持", b.NodeType));
47             }
48             this.Visit(b.Right);
49             //strBuilder.Append(")");
50             return b;
51         }
52 
53         protected override Expression VisitConstant(ConstantExpression c)
54         {
55             switch (Type.GetTypeCode(c.Value.GetType()))
56             {
57                 case TypeCode.Boolean:
58                     strBuilder.Append(((bool)c.Value) ? 1 : 0);
59                     break;
60                 case TypeCode.String:
61                     strBuilder.Append("'");
62                     strBuilder.Append(c.Value);
63                     strBuilder.Append("'");
64                     break;
65                 case TypeCode.Object:
66                     throw new NotSupportedException(string.Format("常量{0}不支持", c.Value));
67                 default:
68                     strBuilder.Append(c.Value);
69                     break;
70             }
71             return c;
72         }
73 
74         protected override Expression VisitMember(MemberExpression m)
75         {
76             if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
77             {
78                 strBuilder.Append(m.Member.Name);
79                 return m;
80             }
81             throw new NotSupportedException(string.Format("成員{0}不支持", m.Member.Name));
82         }
83     }
84 }

到此本篇章完成,更詳細的代碼請下載源代碼查看.