https://www.cnblogs.com/lori/archive/2013/01/10/2855073.html
EF架构~性能高效的批量操作(Update篇）
很多时间之长，我写了EF架构~性能高效的批量操作(Insert篇），而今天我把Update篇也写一下，这对于批量处理数据很有帮助，它解决了EF与linq to sql批量更新数据上的效率问题。
对于EF架构中的批量更新操作，需要我们为实体的导航属性进行手动的标示，因为EF生成的实体中没有一个特殊的说明，所以，我们必须要告诉系统，哪个属性是导航属性，而导航属性是我们不去进行update的。
/// <summary>
/// 属性的导航属性
/// </summary>
public class NavigationAttribute : Attribute
{
}
而对于要进行批量更新的实体，我们需要为导航属性添加这个特性
public class User
{
public int UserID { get; set; }
[Navigation]
public User_Extension User_Extension { get; set; }
}
而对于我们构建批量Update语句，请看代码，它需要对导航属性进行过滤
/// <summary>
/// 构建Update语句串
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
private Tuple<string, object[]> CreateUpdateSQL<TEntity>(TEntity entity) where TEntity : class
{
if (entity == null)
throw new ArgumentException("The database entity can not be null.");
List<string> pkList = GetPrimaryKey<TEntity>().Select(i => i.Name).ToList();
Type entityType = entity.GetType();
var table = entityType.GetProperties().Where(i =>
!pkList.Contains(i.Name)
&& i.GetValue(entity, null) != null
&& i.PropertyType != typeof(EntityState)
&& !(i.GetCustomAttributes(false).Length > 0
&& i.GetCustomAttributes(false).Where(j => j.GetType() == typeof(NavigationAttribute)) != null)
&& (i.PropertyType.IsValueType || i.PropertyType == typeof(string)) //过滤导航属性
).ToArray();
//过滤主键，航行属性，状态属性等
if (pkList == null || pkList.Count == 0)
throw new ArgumentException("The Table entity have not a primary key.");
List<object> arguments = new List<object>();
StringBuilder builder = new StringBuilder();
foreach (var change in table)
{
if (pkList.Contains(change.Name))
continue;
if (arguments.Count != 0)
builder.Append(", ");
builder.Append(change.Name + " = {" + arguments.Count + "}");
if (change.PropertyType == typeof(string) || change.PropertyType == typeof(DateTime))
arguments.Add("'" + change.GetValue(entity, null).ToString().Replace("'", "char(39)") + "'");
else
arguments.Add(change.GetValue(entity, null));
}
if (builder.Length == 0)
throw new Exception("没有任何属性进行更新");
builder.Insert(0, " UPDATE " + string.Format("[{0}]", entityType.Name) + " SET ");
builder.Append(" WHERE ");
bool firstPrimaryKey = true;
foreach (var primaryField in pkList)
{
if (firstPrimaryKey)
firstPrimaryKey = false;
else
builder.Append(" AND ");
object val = entityType.GetProperty(primaryField).GetValue(entity, null);
builder.Append(GetEqualStatment(primaryField, arguments.Count));
arguments.Add(val);
}
return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray());
}
而对子类公开的Update方法，我们进行了一个封装，它通过操作枚举来确实你是要insert,update还是delete，看代码
/// <summary>
/// 执行ＳＱＬ，根据ＳＱＬ操作的类型
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="list"></param>
/// <param name="sqlType"></param>
/// <returns></returns>
protected string DoSQL<TEntity>(IEnumerable<TEntity> list, SQLType sqlType) where TEntity : class
{
StringBuilder sqlstr = new StringBuilder();
switch (sqlType)
{
case SQLType.Insert:
list.ToList().ForEach(i =>
{
Tuple<string, object[]> sql = CreateInsertSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
break;
case SQLType.Update:
list.ToList().ForEach(i =>
{
Tuple<string, object[]> sql = CreateUpdateSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
break;
case SQLType.Delete:
list.ToList().ForEach(i =>
{
Tuple<string, object[]> sql = CreateDeleteSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
break;
default:
throw new ArgumentException("请输入正确的参数");
}
return sqlstr.ToString();
}