Batch Updating in Entity Framework

2010-09-02

多數的O/R Mapping Framework都有個共同的行為模式，在刪除資料或是修改資料前，必須隱式的下達一個Query，由資料庫取得即將要更新的資料列，
然後轉成物件後再更新。
這個行為模式，多半也會成為設計師考慮是否使用O/R Mapping Framework的考量之一，因為多一個Query，就代表著效能會因此降低

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<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 });
                    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"));

}

當對要更新的物件呼叫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。

Visual Studio .NET Framework 4

回首頁

黃忠成

風雪之閣- i live,so i writing

Batch Updating in Entity Framework

聯絡我

著作

經歷

系列文章

標籤雲