Batch Updating in Entity Framework

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

greystar

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

阅读量1.5k

点赞数

分类专栏： .NET 文章标签： object null query string properties descriptor

.NET 专栏收录该内容

156 篇文章 0 订阅

订阅专栏

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 可切換資料庫的特色呢？答案是有，下列的類別可以辦到。

 
     001using System; 
 
     002using System.Collections.Generic; 
 
     003using System.Linq; 
 
     004using System.Text; 
 
     005using System.Data.Objects; 
 
     006using System.ComponentModel; 
 
     007using System.Data.Common; 
 
     008using System.Data; 
 
     009using System.Data.EntityClient; 
 
     010using System.Data.Objects.DataClasses; 
 
     011using System.Reflection; 
 
     012using System.Collections; 
 
     013  
 
     014  
 
     015namespace 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}