public static SqlQueryData GetUpdateQuery(this Dictionary <string, string> updateProps, ObjectContext objectContext, EntityMap entityMap)
{
if (updateProps == null || (updateProps != null && updateProps.Count() == 0))
{
throw new ArgumentException("updateProps must have at least one property.", "updateProps");
}
int nameCount = 0;
bool wroteSet = false;
var sqlBuilder = new StringBuilder();
var sqlQueryData = new SqlQueryData();
sqlBuilder.Append("UPDATE ");
sqlBuilder.Append(entityMap.TableName);
sqlBuilder.AppendLine(" SET ");
// Iterate over the member bindings
foreach (KeyValuePair <string, string> keyVal in updateProps)
{
if (wroteSet)
{
sqlBuilder.AppendLine(", ");
}
string propName = keyVal.Key;
string colName = entityMap
.PropertyMaps
.Where(x => x.PropertyName == propName)
.Select(x => x.ColumnName)
.FirstOrDefault();
if (string.IsNullOrEmpty(colName))
{
throw new ArgumentException("The table " + entityMap.TableName + " doesn't has a column mapped with a Property name " + keyVal.Key);
}
if (!string.IsNullOrEmpty(keyVal.Value))
{
string parameterName = "p__update__" + nameCount++;
sqlQueryData.Parameters.Add(parameterName, keyVal.Value);
sqlQueryData.SqlParameters.Add(new SqlParameter()
{
ParameterName = parameterName, Value = keyVal.Value
});
sqlBuilder.AppendFormat("[{0}] = @{1}", colName, parameterName);
}
else
{
sqlBuilder.AppendFormat("[{0}] = NULL", colName);
}
wroteSet = true;
}
sqlQueryData.QueryString = sqlBuilder.ToString();
return(sqlQueryData);
}
public static SqlQueryData GetUpdateQuery <TEntity>(this Expression <Func <TEntity, TEntity> > updateExpr, ObjectContext objectContext, EntityMap entityMap)
where TEntity : class
{
var initExpr = updateExpr.Body as MemberInitExpression;
if (initExpr == null)
{
throw new ArgumentException("updateExpr expression must be MemberInitExpression.", "updateExpr");
}
int nameCount = 0;
bool wroteSet = false;
var sqlBuilder = new StringBuilder();
var sqlQueryData = new SqlQueryData();
sqlBuilder.Append("UPDATE ");
sqlBuilder.Append(entityMap.TableName);
sqlBuilder.AppendLine(" SET ");
// Iterate over the member bindings
foreach (var binding in initExpr.Bindings)
{
if (wroteSet)
{
sqlBuilder.AppendLine(", ");
}
string propName = binding.Member.Name;
string colName = entityMap
.PropertyMaps
.Where(x => x.PropertyName == propName)
.Select(x => x.ColumnName)
.FirstOrDefault();
var memberAssignment = binding as MemberAssignment;
var memberExpression = memberAssignment.Expression;
ParameterExpression parameterExpression = null;
memberExpression.Visit((ParameterExpression p) =>
{
if (p.Type == entityMap.EntityType)
{
parameterExpression = p;
}
return(p);
});
if (parameterExpression == null)
{
object value;
if (memberExpression.NodeType == ExpressionType.Constant)
{
var constantExpression = memberExpression as ConstantExpression;
if (constantExpression == null)
{
throw new ArgumentException(
"The MemberAssignment expression is not a ConstantExpression.", "updateExpression");
}
value = constantExpression.Value;
}
else
{
LambdaExpression lambda = Expression.Lambda(memberExpression, null);
value = lambda.Compile().DynamicInvoke();
}
if (value != null)
{
string parameterName = "p__update__" + nameCount++;
sqlQueryData.Parameters.Add(parameterName, value);
sqlQueryData.SqlParameters.Add(new SqlParameter()
{
ParameterName = parameterName, Value = value
});
sqlBuilder.AppendFormat("[{0}] = @{1}", colName, parameterName);
}
else
{
sqlBuilder.AppendFormat("[{0}] = NULL", colName);
}
}
else
{
// create clean objectset to build query from
var objectSet = objectContext.CreateObjectSet <TEntity>();
Type[] typeArguments = new[] { entityMap.EntityType, memberExpression.Type };
ConstantExpression constantExpression = Expression.Constant(objectSet);
LambdaExpression lambdaExpression = Expression.Lambda(memberExpression, parameterExpression);
MethodCallExpression selectExpression = Expression.Call(
typeof(Queryable),
"Select",
typeArguments,
constantExpression,
lambdaExpression);
// create query from expression
var selectQuery = objectSet.CreateQuery(selectExpression, entityMap.EntityType);
string sql = selectQuery.ToTraceString();
// parse select part of sql to use as update
string regex = @"SELECT\s*\r\n\s*(?<ColumnValue>.+)?\s*AS\s*(?<ColumnAlias>\[\w+\])\r\n\s*FROM\s*(?<TableName>\[\w+\]\.\[\w+\]|\[\w+\])\s*AS\s*(?<TableAlias>\[\w+\])";
Match match = Regex.Match(sql, regex);
if (!match.Success)
{
throw new ArgumentException("The MemberAssignment expression could not be processed.", "updateExpression");
}
string value = match.Groups["ColumnValue"].Value;
string alias = match.Groups["TableAlias"].Value;
value = value.Replace(alias + ".", "");
foreach (ObjectParameter objectParameter in selectQuery.Parameters)
{
string parameterName = "p__update__" + nameCount++;
sqlQueryData.Parameters.Add(parameterName, objectParameter.Value ?? DBNull.Value);
sqlQueryData.SqlParameters.Add(new SqlParameter()
{
ParameterName = objectParameter.Name, Value = objectParameter.Value ?? DBNull.Value
});
value = value.Replace(objectParameter.Name, parameterName);
}
sqlBuilder.AppendFormat("[{0}] = {1}", colName, value);
}
wroteSet = true;
}
sqlQueryData.QueryString = sqlBuilder.ToString();
return(sqlQueryData);
}
//AuditTrail Related Extensions
public static SqlQueryData GetSelectQuery <TEntity>(this ObjectQuery <TEntity> query, EntityMap entityMap)
where TEntity : class
{
var sqlQueryData = new SqlQueryData();
// changing query to only select keys
var selector = new StringBuilder(50);
selector.Append("new(");
foreach (var propertyMap in entityMap.KeyMaps)
{
if (selector.Length > 4)
{
selector.Append((", "));
}
selector.Append(propertyMap.PropertyName);
}
//get security column in the select
string securityLevelColName = "";
if (entityMap.EntityType.GetProperty("SecurityLevelId") != null)
{
securityLevelColName = "SecurityLevelId";
}
if (entityMap.EntityType.GetProperty("FinalSecurityLevel") != null)
{
securityLevelColName = "FinalSecurityLevel";
}
if (!string.IsNullOrEmpty(securityLevelColName))
{
if (selector.Length > 4)
{
selector.Append((", "));
}
selector.Append(securityLevelColName);
}
selector.Append(")");
var selectQuery = DynamicQueryable.Select(query, selector.ToString());
var objectQuery = selectQuery as ObjectQuery;
if (objectQuery == null)
{
throw new ArgumentException("The query must be of type ObjectQuery.", "query");
}
sqlQueryData.QueryString = objectQuery.ToTraceString();
// create parameters
foreach (var objectParameter in objectQuery.Parameters)
{
sqlQueryData.Parameters.Add(objectParameter.Name, objectParameter.Value ?? DBNull.Value);
sqlQueryData.SqlParameters.Add(new SqlParameter()
{
ParameterName = objectParameter.Name, Value = objectParameter.Value ?? DBNull.Value
});
}
return(sqlQueryData);
}
