private void GetAffectedEntitySets(System.Data.Entity.Core.Common.CommandTrees.DbCommandTree commandTree)
{
System.Data.Entity.Core.Common.CommandTrees.DbExpressionVisitor visitor = new FindAffectedEntitySetsVisitor(this.affectedEntitySets, this.functionsUsed);
System.Data.Entity.Core.Common.CommandTrees.DbQueryCommandTree queryTree = commandTree as System.Data.Entity.Core.Common.CommandTrees.DbQueryCommandTree;
if (queryTree != null)
{
queryTree.Query.Accept(visitor);
return;
}
System.Data.Entity.Core.Common.CommandTrees.DbFunctionCommandTree fxnTree = commandTree as System.Data.Entity.Core.Common.CommandTrees.DbFunctionCommandTree;
if (fxnTree != null)
{
this.IsStoredProcedure = true;
return;
}
System.Data.Entity.Core.Common.CommandTrees.DbUpdateCommandTree updateTree = commandTree as System.Data.Entity.Core.Common.CommandTrees.DbUpdateCommandTree;
if (updateTree != null)
{
this.IsModification = true;
updateTree.Target.Expression.Accept(visitor);
updateTree.Predicate.Accept(visitor);
if (updateTree.Returning != null)
{
updateTree.Returning.Accept(visitor);
}
return;
}
System.Data.Entity.Core.Common.CommandTrees.DbInsertCommandTree insertTree = commandTree as System.Data.Entity.Core.Common.CommandTrees.DbInsertCommandTree;
if (insertTree != null)
{
this.IsModification = true;
insertTree.Target.Expression.Accept(visitor);
if (insertTree.Returning != null)
{
insertTree.Returning.Accept(visitor);
}
return;
}
System.Data.Entity.Core.Common.CommandTrees.DbDeleteCommandTree deleteTree = commandTree as System.Data.Entity.Core.Common.CommandTrees.DbDeleteCommandTree;
if (deleteTree != null)
{
this.IsModification = true;
deleteTree.Target.Expression.Accept(visitor);
if (deleteTree.Predicate != null)
{
deleteTree.Predicate.Accept(visitor);
}
return;
}
throw new NotSupportedException("Command tree type " + commandTree.GetType() + " is not supported.");
}
public void Visit_DbScanExpression_appends_SQL_if_defining_query_is_not_set()
{
var builder = new SqlStringBuilder();
var insertTree = new DbInsertCommandTree();
new DmlSqlGenerator.ExpressionTranslator(
builder, insertTree, true, new SqlGenerator(SqlVersion.Sql10))
.Visit(CreateMockScanExpression(null).Object);
Assert.Equal("[Kontainer].[Binky]", builder.ToString());
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, "InsertFunction");
// insert [schemaName].[tableName]
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
if (tree.SetClauses.Count > 0)
{
// (c1, c2, c3, ...)
commandText.Append("(");
bool first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append(" VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(");");
}
else // No columns specified. Insert an empty row containing default values by inserting null into the rowid
{
commandText.AppendLine(" DEFAULT VALUES;");
}
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
/// <summary>
/// Determine whether we should use a generated values variable to return server generated values.
/// This is true when we're attempting to insert a row where the primary key is server generated
/// but is not an integer type (and therefore can't be used with scope_identity()). It is also true
/// where there is a compound server generated key.
/// </summary>
private static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion)
{
var useGeneratedValuesVariable = false;
if (sqlVersion > SqlVersion.Sql8
&& tree.Returning != null)
{
// Figure out which columns have values
var columnsWithValues =
new HashSet<EdmMember>(tree.SetClauses.Cast<DbSetClause>().Select(s => ((DbPropertyExpression)s.Property).Property));
// Only SQL Server 2005+ support an output clause for inserts
var firstKeyFound = false;
foreach (var keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
{
if (!columnsWithValues.Contains(keyMember))
{
if (firstKeyFound)
{
// compound server gen key
useGeneratedValuesVariable = true;
break;
}
else
{
firstKeyFound = true;
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
// unsupported type
useGeneratedValuesVariable = true;
break;
}
}
}
}
}
return useGeneratedValuesVariable;
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(
commandText, tree,
null != tree.Returning, sqlVersion);
var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlVersion);
var tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType;
if (useGeneratedValuesVariable)
{
// manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)"
commandText
.Append("declare ")
.Append(s_generatedValuesVariableName)
.Append(" table(");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
var columnType = SqlGenerator.GenerateSqlForStoreType(sqlVersion, column.TypeUsage);
if (columnType == "rowversion"
|| columnType == "timestamp")
{
// rowversion and timestamp are intrinsically read-only. use binary to gather server generated
// values for these types.
columnType = "binary(8)";
}
commandText
.Append(GenerateMemberTSql(column))
.Append(" ")
.Append(columnType);
Facet collationFacet;
if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet))
{
var collation = collationFacet.Value as string;
if (!string.IsNullOrEmpty(collation))
{
commandText.Append(" collate ").Append(collation);
}
}
}
Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values");
commandText.AppendLine(")");
}
// insert [schemaName].[tableName]
commandText.Append("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
}
else
{
commandText.AppendLine();
}
if (useGeneratedValuesVariable)
{
// output inserted.id into @generated_values
commandText.Append("output ");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
commandText.Append("inserted.");
commandText.Append(GenerateMemberTSql(column));
}
commandText
.Append(" into ")
.AppendLine(s_generatedValuesVariableName);
}
if (0 < tree.SetClauses.Count)
{
// values c1, c2, ...
var first = true;
commandText.Append("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
commandText.AppendLine("default values");
}
// generate returning sql
GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable);
parameters = translator.Parameters;
return commandText.ToString();
}
private static DbModificationCommandTree RebuildCommandTree(
DbModificationCommandTree originalTree, Dictionary<DbSetClause, DbSetClause> clauseMappings)
{
if (clauseMappings.Count == 0)
{
return originalTree;
}
DbModificationCommandTree result;
Debug.Assert(
originalTree.CommandTreeKind == DbCommandTreeKind.Insert || originalTree.CommandTreeKind == DbCommandTreeKind.Update,
"Set clauses specified for a modification tree that is not an update or insert tree?");
if (originalTree.CommandTreeKind
== DbCommandTreeKind.Insert)
{
var insertTree = (DbInsertCommandTree)originalTree;
result = new DbInsertCommandTree(
insertTree.MetadataWorkspace, insertTree.DataSpace,
insertTree.Target, ReplaceClauses(insertTree.SetClauses, clauseMappings).AsReadOnly(), insertTree.Returning);
}
else
{
var updateTree = (DbUpdateCommandTree)originalTree;
result = new DbUpdateCommandTree(
updateTree.MetadataWorkspace, updateTree.DataSpace,
updateTree.Target, updateTree.Predicate, ReplaceClauses(updateTree.SetClauses, clauseMappings).AsReadOnly(),
updateTree.Returning);
}
return result;
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters, bool generateParameters = true)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters);
bool first = true;
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
// (c1, c2, c3, ...)
commandText.Append("(");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
parameters = translator.Parameters;
return commandText.ToString();
}
protected virtual void VisitInsertCommandTree(DbInsertCommandTree insertTree)
{
Contract.Requires(insertTree != null);
VisitExpressionBindingPre(insertTree.Target);
VisitModificationClauses(insertTree.SetClauses);
if (insertTree.Returning != null)
{
VisitExpression(insertTree.Returning);
}
VisitExpressionBindingPost(insertTree.Target);
}
private string IntroduceRequiredLocalVariables(EntityType entityType, DbInsertCommandTree commandTree)
{
DebugCheck.NotNull(entityType);
DebugCheck.NotNull(commandTree);
var storeGeneratedKeys
= entityType
.KeyProperties
.Where(p => p.IsStoreGeneratedIdentity)
.ToList();
var sql = new SqlStringBuilder { UpperCaseKeywords = true };
if (storeGeneratedKeys.Any())
{
foreach (var keyProperty in storeGeneratedKeys)
{
sql.Append(sql.Length == 0 ? "DECLARE " : ", ");
sql.Append("@");
sql.Append(keyProperty.Name);
sql.Append(" ");
sql.Append(DmlSqlGenerator.GetVariableType(_sqlGenerator, keyProperty));
}
sql.AppendLine();
var translator
= new DmlSqlGenerator.ExpressionTranslator(
sql,
commandTree,
true,
_sqlGenerator,
entityType.KeyProperties);
DmlSqlGenerator.GenerateReturningSql(
sql,
commandTree,
entityType,
translator,
commandTree.Returning,
DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, _sqlGenerator.SqlVersion));
sql.AppendLine();
sql.AppendLine();
}
return sql.ToString();
}
internal static string[] GenerateInsertSql(
DbInsertCommandTree tree,
out List<DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandTexts = new List<String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
null != tree.Returning,
isLocalProvider,
createParameters);
// insert [schemaName].[tableName]
commandText.AppendKeyword("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.AppendKeyword("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
throw ADP1.NotSupported("Default values not supported");
}
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return commandTexts.ToArray();
}
/// <summary>
/// Builds insert command.
/// </summary>
/// <param name="newRow"> Row to insert. </param>
/// <param name="processor"> Context for the table we're inserting into. </param>
/// <returns> Insert command. </returns>
internal UpdateCommand BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor)
{
// Bind the insert target
var target = GetTarget(processor);
// Create set clauses and returning parameter
Dictionary<int, string> outputIdentifiers;
DbExpression returning;
var rowMustBeTouched = true; // for inserts, the row must always be touched
var setClauses = new List<DbModificationClause>();
foreach (var clause in BuildSetClauses(
target, newRow, null, processor, /* insertMode */ true, out outputIdentifiers,
out returning, ref rowMustBeTouched))
{
setClauses.Add(clause);
}
// Initialize DML command tree
var commandTree =
new DbInsertCommandTree(m_translator.MetadataWorkspace, DataSpace.SSpace, target, setClauses.AsReadOnly(), returning);
// Create command
UpdateCommand command = new DynamicUpdateCommand(
processor, m_translator, ModificationOperator.Insert, null, newRow, commandTree, outputIdentifiers);
return command;
}
/// <summary>
/// In case of an insert command we always assign the correct value to the userId
/// </summary>
static bool InterceptInsertCommand(DbCommandTreeInterceptionContext interceptionContext)
{
var insertCommand = interceptionContext.Result as DbInsertCommandTree;
if (insertCommand != null)
{
var column = UserAwareAttribute.GetUserColumnName(insertCommand.Target.VariableType.EdmType);
if (!string.IsNullOrEmpty(column))
{
// Get the userId (throw an exception if there is none)
var userId = GetCurrentUserId();
// Create the variable reference in order to create the property
var variableReference = DbExpressionBuilder.Variable(insertCommand.Target.VariableType,
insertCommand.Target.VariableName);
// Create the property to which will assign the correct value
var userProperty = DbExpressionBuilder.Property(variableReference, column);
// Create the set clause, object representation of sql insert command
var userSetClause =
DbExpressionBuilder.SetClause(userProperty, DbExpression.FromInt32(userId));
// Remove potential assignment of userId for extra safety
var filteredSetClauses =
insertCommand.SetClauses.Cast<DbSetClause>()
.Where(sc => ((DbPropertyExpression)sc.Property).Property.Name != column);
// Construct the final clauses, object representation of sql insert command values
var finalSetClauses =
new ReadOnlyCollection<DbModificationClause>(new List<DbModificationClause>(filteredSetClauses)
{
userSetClause
});
// Construct the new command
var newInsertCommand = new DbInsertCommandTree(
insertCommand.MetadataWorkspace,
insertCommand.DataSpace,
insertCommand.Target,
finalSetClauses,
insertCommand.Returning);
interceptionContext.Result = newInsertCommand;
// True means an interception successfully happened so there is no need to continue
return true;
}
}
return false;
}
private IEnumerable<CreateProcedureOperation> BuildCreateProcedureOperations(
StorageAssociationSetModificationFunctionMapping modificationFunctionMapping,
ModificationCommandTreeGenerator modificationCommandTreeGenerator,
MigrationSqlGenerator migrationSqlGenerator)
{
DebugCheck.NotNull(modificationFunctionMapping);
var insertCommandTrees = new DbInsertCommandTree[0];
var deleteCommandTrees = new DbDeleteCommandTree[0];
if (modificationCommandTreeGenerator != null)
{
var dynamicToFunctionModificationCommandConverter
= new DynamicToFunctionModificationCommandConverter(
modificationFunctionMapping,
_target.StorageEntityContainerMapping);
insertCommandTrees
= dynamicToFunctionModificationCommandConverter
.Convert(
modificationCommandTreeGenerator
.GenerateAssociationInsert(modificationFunctionMapping.AssociationSet.ElementType.Identity))
.ToArray();
deleteCommandTrees
= dynamicToFunctionModificationCommandConverter
.Convert(
modificationCommandTreeGenerator
.GenerateAssociationDelete(modificationFunctionMapping.AssociationSet.ElementType.Identity))
.ToArray();
}
string insertBodySql = null, deleteBodySql = null;
if (migrationSqlGenerator != null)
{
var providerManifestToken
= _target.ProviderInfo.ProviderManifestToken;
insertBodySql
= migrationSqlGenerator
.GenerateProcedureBody(insertCommandTrees, null, providerManifestToken);
deleteBodySql
= migrationSqlGenerator.GenerateProcedureBody(
deleteCommandTrees,
modificationFunctionMapping.DeleteFunctionMapping.RowsAffectedParameterName,
providerManifestToken);
}
yield return BuildCreateProcedureOperation(
modificationFunctionMapping.InsertFunctionMapping.Function,
insertBodySql);
yield return BuildCreateProcedureOperation(
modificationFunctionMapping.DeleteFunctionMapping.Function,
deleteBodySql);
}
