DOC

Batch Updating in Entity Framework

By Eugene Watkins,2014-10-05 12:50
12 views 0
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

    可切換資料庫的特色呢?答案是有;下列的類別可以辦到。 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.Data.Objects.DataClasses;

    using System.Reflection;

    using System.Collections;

namespace EntityHelper

    {

     public class EntityBatchUpdater : IDisposable where T : ObjectContext

     {

     private static Assembly _systemDataEntity = null;

     private static Type _propagatorResultType = null;

     private static Type _entityAdapterType = null;

     private static Type _updateTranslatorType = null;

     private static Type _entityStateType = null;

     static EntityBatchUpdater()

     {

     _systemDataEntity = AppDomain.CurrentDomain.GetAssemblies().Where(a =>

    a.GetName().Name == "System.Data.Entity").FirstOrDefault();

     Type t =

    _systemDataEntity.GetType("System.Data.Mapping.Update.Internal.PropagatorResult");

     Type t1 = typeof(KeyValuePair<,>).MakeGenericType(t, typeof(object));

     Type t2 = typeof(List<>).MakeGenericType(t1);

     _entityAdapterType = _systemDataEntity.GetType("System.Data.IEntityAdapter");

     _updateTranslatorType =

    _systemDataEntity.GetType("System.Data.Mapping.Update.Internal.UpdateTranslator");

     _entityStateType = _systemDataEntity.GetType("System.Data.IEntityStateManager");

     _propagatorResultType = t2;

     }

     private T _context = null;

     public T ObjectContext

     {

     get

     {

     return _context;

     }

     }

     public EntityBatchUpdater()

     {

     _context = (T)typeof(T).GetConstructor(new Type[] { }).Invoke(new object[] { });

     }

     static object CreatePropagatorResultDictionary()

     {

     return Activator.CreateInstance(_propagatorResultType);

     }

     static object GetEntityAdapter(ObjectContext context)

     {

     object providerFactory = typeof(EntityConnection).GetProperty("ProviderFactory",

     BindingFlags.NonPublic | BindingFlags.Instance).GetValue(context.Connection,

    null);

     object result =

    ((IServiceProvider)providerFactory).GetService(_entityAdapterType);

     return result;

     }

     static object CreateUpdateTranslator(object entityStateManager, System.Data.Metadata.Edm.MetadataWorkspace workspace, EntityConnection connection, int? commandTimeout)

     {

     ConstructorInfo ci = _updateTranslatorType.GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null,

     new Type[] { _entityStateType,

    typeof(System.Data.Metadata.Edm.MetadataWorkspace), typeof(EntityConnection), typeof(int?) }, null);

     return ci.Invoke(new object[] { entityStateManager, workspace, connection, commandTimeout });

     }

     static string GetQueryStatement(ObjectQuery query)

     {

     object queryState = typeof(ObjectQuery).GetProperty("QueryState", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(query, null);

     object queryPlan = queryState.GetType().BaseType.InvokeMember("GetExecutionPlan", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod,

     null, queryState, new object[] { null });

     DbCommandDefinition cmddef =

    (DbCommandDefinition)queryPlan.GetType().GetField("CommandDefinition",

    BindingFlags.NonPublic | BindingFlags.Instance).GetValue(queryPlan);

     IEnumerable<string> cmds =

    (IEnumerable<string>)cmddef.GetType().GetProperty("MappedCommands", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(cmddef, null);

     return cmds.FirstOrDefault();

     }

     public static void Update(ObjectContext context)

     {

     object entityAdapter = GetEntityAdapter(context);

     object updateTranslator = CreateUpdateTranslator(context.ObjectStateManager,

    ((EntityConnection)context.Connection).GetMetadataWorkspace(), (EntityConnection)context.Connection, context.CommandTimeout);

     IEnumerable o =

    (IEnumerable)updateTranslator.GetType().InvokeMember("ProduceCommands",

     BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod, null, updateTranslator, null);

     Dictionary<int, object> identifierValues = new Dictionary<int, object>();

     object generateValues = CreatePropagatorResultDictionary();

     context.Connection.Open();

     try

     {

     foreach (var item in o)

     {

     item.GetType().InvokeMember("Execute", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod, null, item,

     new object[] { updateTranslator, (EntityConnection)context.Connection,

    identifierValues, generateValues });

     }

     }

     finally

     {

     context.Connection.Close();

     }

     }

     private static void MarkModifiedProperty(ObjectContext context, object entity, params string[] propertys)

     {

     context.ObjectStateManager.ChangeObjectState(entity, EntityState.Unchanged);

     ObjectStateEntry objectStateEntry =

    context.ObjectStateManager.GetObjectStateEntry(entity);

     PropertyDescriptorCollection properties =

    TypeDescriptor.GetProperties(entity.GetType());

     foreach (FieldMetadata metadata in

    objectStateEntry.CurrentValues.DataRecordInfo.FieldMetadata)

     {

     string name = objectStateEntry.CurrentValues.GetName(metadata.Ordinal);

     PropertyDescriptor descriptor = properties[name];

     if (propertys.Contains(descriptor.Name))

     objectStateEntry.SetModifiedProperty(descriptor.Name);

     }

     }

     public static void UpdateDirect(ObjectContext context, string orKeyFields)

     {

     object entityAdapter = GetEntityAdapter(context);

     object updateTranslator = CreateUpdateTranslator(context.ObjectStateManager,

    ((EntityConnection)context.Connection).GetMetadataWorkspace(),

     (EntityConnection)context.Connection, context.CommandTimeout);

     IEnumerable o =

    (IEnumerable)updateTranslator.GetType().InvokeMember("ProduceCommands",

     BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod,

    null, updateTranslator, null);

     Dictionary<int, object> identifierValues = new Dictionary<int, object>();

     object generateValues = CreatePropagatorResultDictionary();

     context.Connection.Open();

     try

     {

     foreach (var item in o)

     {

     DbCommand cmd = (DbCommand)item.GetType().InvokeMember("CreateCommand", BindingFlags.NonPublic | BindingFlags.Instance |

     BindingFlags.InvokeMethod, null, item,

     new object[] { updateTranslator, identifierValues });

     cmd.Connection = ((EntityConnection)context.Connection).StoreConnection;

     cmd.CommandText = cmd.CommandText + " OR " + orKeyFields;

     cmd.ExecuteReader(CommandBehavior.CloseConnection);

     }

     }

     finally

     {

     context.Connection.Close();

     }

     }

     public void UpdateBatch(EntityObject entity, IQueryable query)

     {

     if (!(query is ObjectQuery))

     throw new Exception("only support ObjectQuery.");

     object entityAdapter = GetEntityAdapter(_context);

     object updateTranslator = CreateUpdateTranslator(_context.ObjectStateManager,

    ((EntityConnection)_context.Connection).GetMetadataWorkspace(),

     (EntityConnection)_context.Connection, _context.CommandTimeout);

     IEnumerable o =

    (IEnumerable)updateTranslator.GetType().InvokeMember("ProduceCommands",

     BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod,

    null, updateTranslator, null);

     Dictionary<int, object> identifierValues = new Dictionary<int, object>();

     object generateValues = CreatePropagatorResultDictionary();

     _context.Connection.Open();

     try

     {

     foreach (var item in o)

     {

     DbCommand cmd = (DbCommand)item.GetType().InvokeMember("CreateCommand", BindingFlags.NonPublic | BindingFlags.Instance |

     BindingFlags.InvokeMethod, null, item,

     new object[] { updateTranslator, identifierValues });

     cmd.Connection = ((EntityConnection)_context.Connection).StoreConnection;

     string queryStatement = GetQueryStatement(query as ObjectQuery);

     if (queryStatement.ToLower().Contains("where"))

     queryStatement =

    queryStatement.Substring(queryStatement.ToLower().IndexOf("where ") + 5);

     cmd.CommandText = cmd.CommandText.Substring(0,

    cmd.CommandText.ToLower().IndexOf("where ") - 1) + " Where " +

     queryStatement.Replace("[Extent1].", "").Replace("\"Extent1\".", "").Replace("Extent1.", "");

     RemovePrimaryKeyParameter(cmd, entity);

     cmd.ExecuteReader(CommandBehavior.CloseConnection);

     }

     }

     finally

     {

     _context.Connection.Close();

     }

     }

     private static void RemovePrimaryKeyParameter(DbCommand cmd, EntityObject entity)

     {

     foreach (var prop in entity.GetType().GetProperties())

     {

     EdmScalarPropertyAttribute[] attrs = (EdmScalarPropertyAttribute[])prop.GetCustomAttributes(typeof(EdmScalarPropertyAttribute),

    true);

     if (attrs != null && attrs.Length > 0)

     {

     if (attrs[0].EntityKeyProperty)

     cmd.Parameters.RemoveAt(cmd.Parameters.Count - 1);

     }

     }

     }

     public void TrackEntity(EntityObject entity)

     {

     if (entity.EntityKey == null)

     {

     EntityKey keys = new EntityKey();

     List<EntityKeyMember> members = new List<EntityKeyMember>();

     foreach (var prop in entity.GetType().GetProperties())

     {

     EdmScalarPropertyAttribute[] attrs = (EdmScalarPropertyAttribute[])prop.GetCustomAttributes(typeof(EdmScalarPropertyAttribute),

    true);

     if (attrs != null && attrs.Length > 0)

     {

     if (attrs[0].EntityKeyProperty)

     {

     object defaultValue = null;

     if (prop.PropertyType == typeof(string))

     defaultValue = "";

     else if (prop.PropertyType == typeof(int) ||

     prop.PropertyType == typeof(double) ||

     prop.PropertyType == typeof(float) ||

     prop.PropertyType == typeof(Int32) ||

     prop.PropertyType == typeof(Int16) ||

     prop.PropertyType == typeof(Int64) ||

     prop.PropertyType == typeof(long) ||

     prop.PropertyType == typeof(short))

     defaultValue = -1;

     else if (prop.PropertyType == typeof(DateTime))

     defaultValue = DateTime.MinValue;

     else if (prop.PropertyType == typeof(TimeSpan))

     defaultValue = TimeSpan.MinValue;

     else if (prop.PropertyType == typeof(Char))

     defaultValue = 'C';

     prop.SetValue(entity, defaultValue, null);

     members.Add(new EntityKeyMember(prop.Name, defaultValue));

     }

     }

     }

     keys.EntityKeyValues = members.ToArray();

     EdmEntityTypeAttribute[] attrs1 = (EdmEntityTypeAttribute[])entity.GetType().GetCustomAttributes(typeof(EdmEntityTypeAttribute

    ), true);

     if (attrs1 != null && attrs1.Length > 0)

     {

     keys.EntityContainerName = _context.DefaultContainerName;

     keys.EntitySetName = attrs1[0].Name;

     }

     entity.EntityKey = keys;

     }

     _context.Attach(entity);

     entity.PropertyChanged += (s, args) =>

     {

     MarkModifiedProperty(_context, entity, args.PropertyName);

     };

     }

     public void Dispose()

     {

     _context.Dispose();

     }

     }

     }

     這個類別的程式碼;說穿了就是透過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"));

     }

     }

    }

Report this document

For any questions or suggestions please email
cust-service@docsford.com