Batch Updating in Entity Framework

最新推荐文章于 2022-06-08 08:45:08 发布

最新推荐文章于 2022-06-08 08:45:08 发布 · 60 阅读

文章标签：

#C #C++ #C# #Linq #SQL

本文介绍如何在Entity Framework中实现批量更新操作，通过自定义类EntityBatchUpdater，可以在不损失数据库可移植性的情况下提高批量更新效率。

http://www.dotblogs.com.tw/code6421/archive/2010/09/02/17526.aspx

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  

    016{ 

public class EntityBatchUpdater<T>: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 });  

    168