Batch Updating in Entity Framework

Batch Updating in Entity Framework
 
 
文/黃忠成
 
 
 
The Update Story of Entity Framework
 
 
 
      多數的O/R Mapping Framework都有個共同的行為模式，在刪除資料或是修改資料前，必須隱式的下達一個Query，由資料庫取得即將要更新的資料列，
然後轉成物件後再更新。
     這個行為模式，多半也會成為設計師考慮是否使用O/R Mapping Framework的考量之一，因為多一個Query，就代表著效能會因此降低，雖然對於
O/R Mapping Framework而言，這是一個必要的行為模式，因為它們得考量到當物件有著關聯時的情況。但對於實際的專案來說，跳過這個Query來更新資料，
卻也是必然會出現的情況，既然是必然會出現的情況，多數的O/R Mapping Framework也只好為此做出讓步，提供可跳過Query來更新資料的機制，
Entity Framework自然也擁有這個機制。
 
 
Update Row without Query
 
 
 Entity Framework支援跳過Query步驟來更新資料列，寫法如下:
static void UpdateWithoutQuery()
{
     NorthwindEntities context = new NorthwindEntities();                                   
     Customers c = new Customers();
     c.CustomerID = "VINET";
     context.AttachTo("Customers", c);
     c.CompanyName = "15556";           
     context.SaveChanges();
}
 
注意，AttachTo的位置很重要，在這之前所設定的值，都不會被寫入，例如下列的Region便不會被寫入。
 
static void UpdateWithoutQuery()
{
      NorthwindEntities context = new NorthwindEntities();                                    
      Customers c = new Customers();
      c.CustomerID = "VINET";
      c.Region = "TWN";
      context.AttachTo("Customers", c);
      c.CompanyName = "15556";           
      context.SaveChanges();
}
 
 
Delete Row without Query
 
 
  同樣的手法，也可以用在刪除資料列上。
 
static void DeleteWithoutQuery()
{
     NorthwindEntities context = new NorthwindEntities();
     Customers c = new Customers();
     c.CustomerID = "CT002";
     context.AttachTo("Customers", c);
     context.DeleteObject(c);
     context.SaveChanges();
}
 
 
缺點？
 
 
     那麼這樣就夠了嗎？事實上，O/R Mapping Framework一直都缺少著一種機制，那就是Batch Update，在很多情況下，我們希望能
下達下列的指令來更新一筆以上的資料列。
 
UPDATE Customers SET SomeFlag = 1 WHERE Region = “TW”
 
     
    在O/R Mapping Framework中，這得以迴圈方式，一一查詢出每一筆Region=”TW”的資料，然後更新SomeFlag，由於沒有指定主鍵，
所以也無法使用先前提及的方法來跳過Query動作，我們得遵守O/R Mapping Framework的規則，一筆筆Query後更新，這是很沒效率的動作。
     當然，所有O/R Mapping Framework都支援讓設計師直接下達SQL的方法，以Entity Framework而言，可以這麼下:
 
context.ExecuteStoreCommand(“UPDATE Customers SET SomeFlag = 1 WHERE Region = ‘TW’);
   
    不過，這種方法會失去Entity Framework可切換資料庫的特色，所以得特別小心把這部份獨立出來，為日後切換資料庫時留條後路。
 
 
Batch Update
 
 
   那麼，有沒有一個方法，可以達到Batch Update，又不失去Entity Framework可切換資料庫的特色呢？答案是有，下列的類別可以辦到。
view source
print?
001 using System;
002 using System.Collections.Generic;
003 using System.Linq;
004 using System.Text;
005 using System.Data.Objects;
006 using System.ComponentModel;
007 using System.Data.Common;
008 using System.Data;
009 using System.Data.EntityClient;
010 using System.Data.Objects.DataClasses;
011 using System.Reflection;
012 using System.Collections;
013   
014   
015 namespace EntityHelper
016 {
017     public class EntityBatchUpdater<T>:IDisposable where T :ObjectContext
018     {
019         private static Assembly _systemDataEntity = null;
020         private static Type _propagatorResultType = null;
021         private static Type _entityAdapterType = null;
022         private static Type _updateTranslatorType = null;
023         private static Type _entityStateType = null;
024   
025         static EntityBatchUpdater()
026         {
027             _systemDataEntity = AppDomain.CurrentDomain.GetAssemblies().Where(a => a.GetName().Name == "System.Data.Entity").FirstOrDefault();
028             Type t = _systemDataEntity.GetType("System.Data.Mapping.Update.Internal.PropagatorResult");
029             Type t1 = typeof(KeyValuePair<,>).MakeGenericType(t, typeof(object));
030             Type t2 = typeof(List<>).MakeGenericType(t1);
031             _entityAdapterType = _systemDataEntity.GetType("System.Data.IEntityAdapter");
032             _updateTranslatorType = _systemDataEntity.GetType("System.Data.Mapping.Update.Internal.UpdateTranslator");
033             _entityStateType = _systemDataEntity.GetType("System.Data.IEntityStateManager");
034             _propagatorResultType = t2;
035         }
036   
037         private T _context = null;
038   
039         public T ObjectContext
040         {
041             get
042             {
043                 return _context;
044             }
045         }
046   
047         public EntityBatchUpdater()        
048         {
049             _context = (T)typeof(T).GetConstructor(new Type[]{}).Invoke(new object[]{});
050         }
051   
052         static object CreatePropagatorResultDictionary()
053         {
054             return Activator.CreateInstance(_propagatorResultType);
055         }
056   
057         static object GetEntityAdapter(ObjectContext context)
058         {
059             object providerFactory = typeof(EntityConnection).GetProperty("ProviderFactory",
060                 BindingFlags.NonPublic | BindingFlags.Instance).GetValue(context.Connection, null);
061             object result = ((IServiceProvider)providerFactory).GetService(_entityAdapterType);
062             return result;
063         }
064   
065         static object CreateUpdateTranslator(object entityStateManager, System.Data.Metadata.Edm.MetadataWorkspace workspace, EntityConnection connection, int? commandTimeout)
066         {
067             ConstructorInfo ci = _updateTranslatorType.GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null,
068                 new Type[] { _entityStateType, typeof(System.Data.Metadata.Edm.MetadataWorkspace), typeof(EntityConnection), typeof(int?) }, null);
069             return ci.Invoke(new object[] { entityStateManager, workspace, connection, commandTimeout });
070         }
071   
072         static string GetQueryStatement(ObjectQuery query)
073         {
074             object queryState = typeof(ObjectQuery).GetProperty("QueryState", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(query, null);
075             object queryPlan = queryState.GetType().BaseType.InvokeMember("GetExecutionPlan", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod,
076                                                                            null, queryState, new object[] { null });
077             DbCommandDefinition cmddef = (DbCommandDefinition)queryPlan.GetType().GetField("CommandDefinition", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(queryPlan);
078   
079   
080             IEnumerable<string> cmds = (IEnumerable<string>)cmddef.GetType().GetProperty("MappedCommands", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(cmddef, null);
081             return cmds.FirstOrDefault();
082         }
083   
084         public static void Update(ObjectContext context)
085         {
086             object entityAdapter = GetEntityAdapter(context);
087             object updateTranslator = CreateUpdateTranslator(context.ObjectStateManager, ((EntityConnection)context.Connection).GetMetadataWorkspace(), (EntityConnection)context.Connection, context.CommandTimeout);
088             IEnumerable o = (IEnumerable)updateTranslator.GetType().InvokeMember("ProduceCommands",
089                 BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod, null, updateTranslator, null);
090             Dictionary<int, object> identifierValues = new Dictionary<int, object>();
091             object generateValues = CreatePropagatorResultDictionary();
092             context.Connection.Open();
093             try
094             {
095                 foreach (var item in o)
096                 {
097                     item.GetType().InvokeMember("Execute", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod, null, item,
098                         new object[] { updateTranslator, (EntityConnection)context.Connection, identifierValues, generateValues });
099                 }
100             }
101             finally
102             {
103                 context.Connection.Close();
104             }
105         }
106   
107         private static void MarkModifiedProperty(ObjectContext context, object entity, params string[] propertys)
108         {
109             context.ObjectStateManager.ChangeObjectState(entity, EntityState.Unchanged);
110             ObjectStateEntry objectStateEntry = context.ObjectStateManager.GetObjectStateEntry(entity);
111             PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entity.GetType());
112             foreach (FieldMetadata metadata in objectStateEntry.CurrentValues.DataRecordInfo.FieldMetadata)
113             {
114                 string name = objectStateEntry.CurrentValues.GetName(metadata.Ordinal);
115                 PropertyDescriptor descriptor = properties[name];
116                 if (propertys.Contains(descriptor.Name))
117                     objectStateEntry.SetModifiedProperty(descriptor.Name);
118             }
119         }
120   
121         public static void UpdateDirect(ObjectContext context, string orKeyFields)
122         {
123             object entityAdapter = GetEntityAdapter(context);
124             object updateTranslator = CreateUpdateTranslator(context.ObjectStateManager, ((EntityConnection)context.Connection).GetMetadataWorkspace(),
125                 (EntityConnection)context.Connection, context.CommandTimeout);
126             IEnumerable o = (IEnumerable)updateTranslator.GetType().InvokeMember("ProduceCommands",
127                 BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod, null, updateTranslator, null);
128             Dictionary<int, object> identifierValues = new Dictionary<int, object>();
129             object generateValues = CreatePropagatorResultDictionary();
130             context.Connection.Open();
131             try
132             {
133                 foreach (var item in o)
134                 {
135                     DbCommand cmd = (DbCommand)item.GetType().InvokeMember("CreateCommand", BindingFlags.NonPublic | BindingFlags.Instance |
136                         BindingFlags.InvokeMethod, null, item,
137                         new object[] { updateTranslator, identifierValues });
138                     cmd.Connection = ((EntityConnection)context.Connection).StoreConnection;
139                     cmd.CommandText = cmd.CommandText + " OR " + orKeyFields;
140                     cmd.ExecuteReader(CommandBehavior.CloseConnection);
141                 }
142             }
143             finally
144             {
145                 context.Connection.Close();
146             }
147         }
148   
149         public void UpdateBatch(EntityObject entity, IQueryable query)
150         {
151             if (!(query is ObjectQuery))
152                 throw new Exception("only support ObjectQuery.");
153             object entityAdapter = GetEntityAdapter(_context);
154             object updateTranslator = CreateUpdateTranslator(_context.ObjectStateManager, ((EntityConnection)_context.Connection).GetMetadataWorkspace(),
155                 (EntityConnection)_context.Connection, _context.CommandTimeout);
156             IEnumerable o = (IEnumerable)updateTranslator.GetType().InvokeMember("ProduceCommands",
157                 BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod, null, updateTranslator, null);
158             Dictionary<int, object> identifierValues = new Dictionary<int, object>();
159             object generateValues = CreatePropagatorResultDictionary();
160             _context.Connection.Open();
161             try
162             {
163                 foreach (var item in o)
164                 {
165                     DbCommand cmd = (DbCommand)item.GetType().InvokeMember("CreateCommand", BindingFlags.NonPublic | BindingFlags.Instance |
166                         BindingFlags.InvokeMethod, null, item,
167                         new object[] { updateTranslator, identifierValues });
168                     cmd.Connection = ((EntityConnection)_context.Connection).StoreConnection;
169                     string queryStatement = GetQueryStatement(query as ObjectQuery);
170                     if (queryStatement.ToLower().Contains("where"))
171                         queryStatement = queryStatement.Substring(queryStatement.ToLower().IndexOf("where ") + 5);
172                     cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.ToLower().IndexOf("where ") - 1) + " Where " +
173                               queryStatement.Replace("[Extent1].", "").Replace("/"Extent1/".", "").Replace("Extent1.", "");
174                     RemovePrimaryKeyParameter(cmd, entity);
175                     cmd.ExecuteReader(CommandBehavior.CloseConnection);
176                 }
177             }
178             finally
179             {
180                 _context.Connection.Close();
181             }
182         }
183   
184         private static void RemovePrimaryKeyParameter(DbCommand cmd, EntityObject entity)
185         {
186             foreach (var prop in entity.GetType().GetProperties())
187             {
188                 EdmScalarPropertyAttribute[] attrs = (EdmScalarPropertyAttribute[])prop.GetCustomAttributes(typeof(EdmScalarPropertyAttribute), true);
189                 if (attrs != null && attrs.Length > 0)
190                 {
191                     if (attrs[0].EntityKeyProperty)
192                         cmd.Parameters.RemoveAt(cmd.Parameters.Count - 1);
193                 }
194             }
195         }
196   
197         public void TrackEntity(EntityObject entity)
198         {
199             if (entity.EntityKey == null)
200             {
201                 EntityKey keys = new EntityKey();
202                 List<EntityKeyMember> members = new List<EntityKeyMember>();
203                 foreach (var prop in entity.GetType().GetProperties())
204                 {
205                     EdmScalarPropertyAttribute[] attrs = (EdmScalarPropertyAttribute[])prop.GetCustomAttributes(typeof(EdmScalarPropertyAttribute), true);
206                     if (attrs != null && attrs.Length > 0)
207                     {
208                         if (attrs[0].EntityKeyProperty)
209                         {
210                             object defaultValue = null;
211   
212                             if (prop.PropertyType == typeof(string))
213                                 defaultValue = "";
214                             else if (prop.PropertyType == typeof(int) ||
215                                     prop.PropertyType == typeof(double) ||
216                                     prop.PropertyType == typeof(float) ||
217                                     prop.PropertyType == typeof(Int32) ||
218                                     prop.PropertyType == typeof(Int16) ||
219                                     prop.PropertyType == typeof(Int64) ||
220                                     prop.PropertyType == typeof(long) ||
221                                     prop.PropertyType == typeof(short))
222                                 defaultValue = -1;
223                             else if (prop.PropertyType == typeof(DateTime))
224                                 defaultValue = DateTime.MinValue;
225                             else if (prop.PropertyType == typeof(TimeSpan))
226                                 defaultValue = TimeSpan.MinValue;
227                             else if (prop.PropertyType == typeof(Char))
228                                 defaultValue = 'C';
229                             prop.SetValue(entity, defaultValue, null);
230                             members.Add(new EntityKeyMember(prop.Name, defaultValue));
231                         }
232                     }
233                 }
234                 keys.EntityKeyValues = members.ToArray();
235                 EdmEntityTypeAttribute[] attrs1 = (EdmEntityTypeAttribute[])entity.GetType().GetCustomAttributes(typeof(EdmEntityTypeAttribute), true);
236                 if (attrs1 != null && attrs1.Length > 0)
237                 {
238                     keys.EntityContainerName = _context.DefaultContainerName;
239                     keys.EntitySetName = attrs1[0].Name;
240                 }
241                 entity.EntityKey = keys;
242             }
243   
244             _context.Attach(entity);
245   
246             entity.PropertyChanged += (s, args) =>
247             {
248                 MarkModifiedProperty(_context, entity, args.PropertyName);
249             };
250         }
251   
252         public void Dispose()
253         {
254             _context.Dispose();
255         }
256     }
257 }
 這個類別的程式碼，說穿了就是透過Entity Framework原本提供，但不公開的函式及物件來達到目的，運用此類別，我們可以寫下以下這段程式碼，然後進行批次更新:
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.ComponentModel;
using System.Data.Common;
using System.Data;
using System.Data.EntityClient;
using System.Reflection;
using System.Collections;
using EntityHelper;
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Customers c = new Customers();
            EntityBatchUpdater<NorthwindEntities> batchContext =
                new EntityBatchUpdater<NorthwindEntities>();
            //設定c為要Tracking的對象物件
            batchContext.TrackEntity(c);
            //要更新的欄位
            c.CompanyName = "CR4";
            //更新c物件,第二個參數為查詢條件.
            batchContext.UpdateBatch(c,
                batchContext.ObjectContext.Customers.Where(a => a.Region == "ru"));           
        }
    }
}
當對要更新的物件呼叫TrackEntity函式時，EntityBatchUpdater會自動開始追蹤該物件，此後更新的欄位都將被視為是要寫入資料庫的值，呼叫UpdateBatch則是將c的變動
寫入資料庫中，注意，第二個參數是更新c時的查詢條件，此例會將所有Region = “ru”的資料列的CompanyName更新為CR4。
同樣的結果，也可以這樣寫:
 
batchContext.UpdateBatch(c, from s1 in batchContext.ObjectContext.Customers where s1.Region == "ru" select s1);
 
 
 
Batch Delete
 
 
 
    EntityBatchUpdater也可以用在刪除，如下:
 
static void Main(string[] args)
{
     Customers c = new Customers();
     EntityBatchUpdater<NorthwindEntities> batchContext =
             new EntityBatchUpdater<NorthwindEntities>();
 
     batchContext.TrackEntity(c);
     batchContext.ObjectContext.DeleteObject(c);
     batchContext.UpdateBatch(c,
from s1 in batchContext.ObjectContext.Customers where s1.Region == "ru" select s1);          
}
 
此例會將所有Region = “ru”的資料列刪除。
 
 
 
你該知道的事
 
     EntityBatchUpdater可以幫我們完成Batch Update及Batch Delete，現在問題出在跨資料庫上，EntityBatchUpdater所使用的手法可以適用於SQL Server及Oracle(Devart)，
而其它的資料庫就沒測試過了，如果你遭遇到問題，那麼可查看UpdateBatch最後的SQL字串組合部份，通常問題會出現在Alias。
 
posted @ 2011-02-17 15:01  greystar  阅读(465)  评论(0)  编辑  收藏  举报