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