Chloe
Chloe copied to clipboard
shuxinqin
如何批量更改数据
DbContext的update方法，貌似只能一次更改一条。如何做到一次更改多条数据（同一实体类型）。EF里实现查询，后修改，再savechanges. Chloe该怎么做？
谢谢你的回复，如果这样操作，你那边能否实现？ db.UpdateRange(ListEntitys,m => m.Key) 表示批理更新这个集合，条件为指定的某个属性（如：Key）
生成sql 语句时，和Update一样。指的Key忽略更新，将其作为条件。 例如： var list = new List<T>(); list.Add(new Table() { id = 2,name = "chloe"} list.Add(new Table() { id = 7,name = "i like chloe"} .. db.UpdateRange(list,m=>m.id); //如果不指定更新条件，则默认以关键字作为本次的条件
生成的SQL出来为： update table set name='chloe' where id = 2; update table set name='i like chloe' where id = 7;
我们团队用了个临时方法，感觉还行，请参照 public override int UpdateRange<TEntity>(List<TEntity> entities, string table = null) { PublicHelper.CheckNull(entities); if (entities.Count == 0) return 0;
int maxParameters = 2100;
int batchSize = 50; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
List<PrimitivePropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PrimitivePropertyDescriptors.Where(a => a.IsAutoIncrement == false).ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
string sqlTemplate = this.AppendUpdateRangeSqlTemplate(dbTable, mappingPropertyDescriptors);
var primaryMappingProperty = typeDescriptor.PrimitivePropertyDescriptors.FirstOrDefault(p => p.IsPrimaryKey);
Action updateAction = () =>
{
int batchCount = 0;
List<DbParam> dbParams = new List<DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
sqlBuilder.Append(";" + Environment.NewLine);
var str = sqlTemplate;
//一个实体对象构造SQL
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
var mappingPropertyDescriptor = mappingPropertyDescriptors[j];
object val = mappingPropertyDescriptor.GetValue(entity);
if (val == null)
{
str = str.Replace("{" + j + "}", "= NULL");
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (Utils.IsToStringableNumericType(valType))
{
str = str.Replace("{" + j + "}", " = " + val.ToString());
continue;
}
if (val is bool)
{
if ((bool)val == true)
str = str.Replace("{" + j + "}", "= true");
else
str = str.Replace("{" + j + "}", "= false");
continue;
}
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val) { DbType = mappingPropertyDescriptor.Column.DbType };
dbParams.Add(dbParam);
str = str.Replace("{" + j + "}", "= " + paramName);
}
//主键
if (primaryMappingProperty != null)
{
object val = primaryMappingProperty.GetValue(entity);
string pkName = UtilConstants.ParameterNamePrefix + primaryMappingProperty.Column.Name + i;
DbParam pkParam = new DbParam(pkName, val) { DbType = primaryMappingProperty.Column.DbType };
dbParams.Add(pkParam);
str = str.Replace("{PK}", pkName);
}
sqlBuilder.Append(str);
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 120/*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
//sqlBuilder.Insert(0, sqlTemplate);
string sql = sqlBuilder.ToString();
this.Session.ExecuteNonQuery(sql, dbParams.ToArray());
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Action fAction = () =>
{
updateAction();
};
if (this.Session.IsInTransaction)
{
fAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
fAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
this.Session.RollbackTransaction();
throw;
}
}
return entities.Count;
}
private string AppendUpdateRangeSqlTemplate(DbTable table, List<PrimitivePropertyDescriptor> mappingPropertyDescriptors) { var sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE ");
sqlBuilder.Append(this.AppendTableName(table));
sqlBuilder.Append(" SET ");
var primaryKeyDescriptor = mappingPropertyDescriptors.FirstOrDefault(p => p.IsPrimaryKey == true);
var mappingProperties = mappingPropertyDescriptors.Where(p => p.IsPrimaryKey != true).ToList();
for (int i = 0; i < mappingProperties.Count; i++)
{
var mappingPropertyDescriptor = mappingProperties[i];
if (i > 0) sqlBuilder.Append(", ");
sqlBuilder.Append(Utils.QuoteName(mappingPropertyDescriptor.Column.Name, this.ConvertToLowercase));
sqlBuilder.Append(" {" + i + "} ");
}
sqlBuilder.Append(" WHERE " + Utils.QuoteName(primaryKeyDescriptor.Column.Name, this.ConvertToLowercase));
sqlBuilder.Append(" = {PK} ");
string sqlTemplate = sqlBuilder.ToString();
return sqlTemplate;
}
一直想怎么去删参数， 把值直接写入到生成SQL中， 看到楼上的给我提示， PublicHelper.cs 中 MakeCondition函数似乎可以这样改写了。 public static DbExpression MakeCondition(PairList<PrimitivePropertyDescriptor, object> propertyValuePairs, DbTable dbTable) { DbExpression conditionExp = null; foreach (var pair in propertyValuePairs) { PrimitivePropertyDescriptor propertyDescriptor = pair.Item1; object val = pair.Item2;
DbExpression right = null;
DbExpression left = new DbColumnAccessExpression(dbTable, propertyDescriptor.Column);
if (Utils.IsToStringableNumericType(propertyDescriptor.PropertyType) )
{
right = DbExpression.Constant(val, propertyDescriptor.PropertyType);
}
else
{
right = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
}
DbExpression equalExp = new DbEqualExpression(left, right);
conditionExp = conditionExp.And(equalExp);
}
return conditionExp;
}
