protected virtual SelectStatement GenerateReturningSql(DbModificationCommandTree tree, DbExpression returning)
{
SelectStatement select = base.GenerateReturningSql(tree, returning);
ListFragment where = new ListFragment();
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
bool foundIdentity = false;
where.Append(" row_count() > 0");
foreach (EdmMember keyMember in table.ElementType.KeyMembers)
{
SqlFragment value;
if (!values.TryGetValue(keyMember, out value))
{
if (foundIdentity)
throw new NotSupportedException();
foundIdentity = true;
value = new LiteralFragment("last_insert_id()");
}
where.Append(String.Format(" AND `{0}`=", keyMember));
where.Append(value);
}
select.Where = where;
return select;
}
// effects: try to find setter expression for the given member
// requires: command tree must be an insert or update tree (since other DML trees hnabve
private static bool TryGetSetterExpression(
DbModificationCommandTree tree, EdmMember member, ModificationOperator op, out DbSetClause setter)
{
Debug.Assert(op == ModificationOperator.Insert || op == ModificationOperator.Update, "only inserts and updates have setters");
IEnumerable<DbModificationClause> clauses;
if (ModificationOperator.Insert == op)
{
clauses = ((DbInsertCommandTree)tree).SetClauses;
}
else
{
clauses = ((DbUpdateCommandTree)tree).SetClauses;
}
foreach (DbSetClause setClause in clauses)
{
// check if this is the correct setter
if (((DbPropertyExpression)setClause.Property).Property.EdmEquals(member))
{
setter = setClause;
return true;
}
}
// no match found
setter = null;
return false;
}
private void DbScanExpressionThrowsTest(string functionName, DbModificationCommandTree commandTree)
{
Assert.Equal(
Strings.Update_SqlEntitySetWithoutDmlFunctions("Binky", functionName, "ModificationFunctionMapping"),
Assert.Throws<UpdateException>(
() => new DmlSqlGenerator.ExpressionTranslator(new StringBuilder(), commandTree, true, new SqlGenerator(SqlVersion.Sql10))
.Visit(CreateMockScanExpression("I am defined.").Object)).Message);
}
protected override SelectStatement GenerateReturningSql(DbModificationCommandTree tree, DbExpression returning)
{
SelectStatement select = base.GenerateReturningSql(tree, returning);
ListFragment where = new ListFragment();
where.Append(" row_count() > 0 and ");
where.Append( ((System.Data.Entity.Core.Common.CommandTrees.DbUpdateCommandTree)tree).Predicate.Accept(this) );
select.Where = where;
return select;
}
internal DynamicUpdateCommand(
TableChangeProcessor processor, UpdateTranslator translator,
ModificationOperator modificationOperator, PropagatorResult originalValues, PropagatorResult currentValues,
DbModificationCommandTree tree, Dictionary<int, string> outputIdentifiers)
: base(translator, originalValues, currentValues)
{
Contract.Requires(processor != null);
Contract.Requires(translator != null);
Contract.Requires(tree != null);
_processor = processor;
_operator = modificationOperator;
_modificationCommandTree = tree;
_outputIdentifiers = outputIdentifiers; // may be null (not all commands have output identifiers)
// initialize identifier information (supports lateral propagation of server gen values)
if (ModificationOperator.Insert == modificationOperator
|| ModificationOperator.Update == modificationOperator)
{
const int capacity = 2; // "average" number of identifiers per row
_inputIdentifiers = new List<KeyValuePair<int, DbSetClause>>(capacity);
foreach (var member in
Helper.PairEnumerations(
TypeHelpers.GetAllStructuralMembers(CurrentValues.StructuralType),
CurrentValues.GetMemberValues()))
{
DbSetClause setter;
var identifier = member.Value.Identifier;
if (PropagatorResult.NullIdentifier != identifier
&&
TryGetSetterExpression(tree, member.Key, modificationOperator, out setter)) // can find corresponding setter
{
foreach (var principal in translator.KeyManager.GetPrincipals(identifier))
{
_inputIdentifiers.Add(new KeyValuePair<int, DbSetClause>(principal, setter));
}
}
}
}
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText"> Command text with which to populate commands </param>
/// <param name="commandTree"> Command tree generating SQL </param>
/// <param name="preserveMemberValues"> Indicates whether the translator should preserve member values while compiling t-SQL (only needed for server generation) </param>
internal ExpressionTranslator(
StringBuilder commandText, DbModificationCommandTree commandTree,
bool preserveMemberValues, SqlVersion version)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
_commandText = commandText;
_commandTree = commandTree;
_version = version;
_parameters = new List<SqlParameter>();
_memberValues = preserveMemberValues
? new Dictionary<EdmMember, SqlParameter>()
: null;
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestampValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
///
/// On SQL Server 2005 and up, we have an additional syntax used for non integer return types:
///
/// declare @generatedValues table(ID uniqueidentifier)
/// insert dbo.MyTable
/// output ID into @generated_values
/// values (...);
/// select ID
/// from @generatedValues as g join dbo.MyTable as t on g.ID = t.ID
/// where @@ROWCOUNT > 0;</code>
/// </summary>
/// <param name="commandText"> Builder containing command text </param>
/// <param name="tree"> Modification command tree </param>
/// <param name="tableType"> Type of table. </param>
/// <param name="translator"> Translator used to produce DML SQL statement for the tree </param>
/// <param name="returning"> Returning expression. If null, the method returns immediately without producing a SELECT statement. </param>
private static void GenerateReturningSql(
StringBuilder commandText, DbModificationCommandTree tree, EntityType tableType,
ExpressionTranslator translator, DbExpression returning, bool useGeneratedValuesVariable)
{
// Nothing to do if there is no Returning expression
if (null == returning)
{
return;
}
// select
commandText.Append("select ");
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = "t";
}
returning.Accept(translator);
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = null;
}
commandText.AppendLine();
if (useGeneratedValuesVariable)
{
// from @generated_values
commandText.Append("from ");
commandText.Append(s_generatedValuesVariableName);
commandText.Append(" as g join ");
tree.Target.Expression.Accept(translator);
commandText.Append(" as t on ");
var separator = string.Empty;
foreach (var keyMember in tableType.KeyMembers)
{
commandText.Append(separator);
separator = " and ";
commandText.Append("g.");
var memberTSql = GenerateMemberTSql(keyMember);
commandText.Append(memberTSql);
commandText.Append(" = t.");
commandText.Append(memberTSql);
}
commandText.AppendLine();
commandText.Append("where @@ROWCOUNT > 0");
}
else
{
// from
commandText.Append("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.Append("where @@ROWCOUNT > 0");
var table = ((DbScanExpression)tree.Target.Expression).Target;
var identity = false;
foreach (var keyMember in table.ElementType.KeyMembers)
{
commandText.Append(" and ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
SqlParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the scope_identity() function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey(table.Name));
}
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw new InvalidOperationException(
Strings.Update_NotSupportedIdentityType(
keyMember.Name, keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
identity = true;
}
}
}
}
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;
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText"> Command text with which to populate commands </param>
/// <param name="commandTree"> Command tree generating SQL </param>
/// <param name="preserveMemberValues"> Indicates whether the translator should preserve member values while compiling t-SQL (only needed for server generation) </param>
internal ExpressionTranslator(
StringBuilder commandText,
DbModificationCommandTree commandTree,
bool preserveMemberValues,
SqlGenerator sqlGenerator,
ICollection<EdmProperty> localVariableBindings = null)
{
DebugCheck.NotNull(commandText);
DebugCheck.NotNull(commandTree);
_commandText = commandText;
_commandTree = commandTree;
_sqlGenerator = sqlGenerator;
_localVariableBindings = localVariableBindings;
_parameters = new List<SqlParameter>();
_memberValues = preserveMemberValues
? new Dictionary<EdmMember, SqlParameter>()
: null;
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestamptValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning)
{
// Nothing to do if there is no Returning expression
if (null == returning) { return; }
// select
commandText.Append("SELECT ");
returning.Accept(translator);
commandText.AppendLine();
// from
commandText.Append("FROM ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
#if USE_INTEROP_DLL && INTEROP_EXTENSION_FUNCTIONS
commandText.Append("WHERE last_rows_affected() > 0");
#else
commandText.Append("WHERE changes() > 0");
#endif
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
bool identity = false;
foreach (EdmMember keyMember in table.ElementType.KeyMembers)
{
commandText.Append(" AND ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the scope_identity() function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(string.Format("Server generated keys are only supported for identity columns. More than one key column is marked as server generated in table '{0}'.", table.Name));
}
commandText.AppendLine("last_insert_rowid();");
identity = true;
}
}
}
// <summary>
// Initialize a new expression translator populating the given string builder
// with command text. Command text builder and command tree must not be null.
// </summary>
// <param name="commandText"> Command text with which to populate commands </param>
// <param name="commandTree"> Command tree generating SQL </param>
// <param name="preserveMemberValues"> Indicates whether the translator should preserve member values while compiling t-SQL (only needed for server generation) </param>
internal ExpressionTranslator(
SqlStringBuilder commandText,
DbModificationCommandTree commandTree,
bool preserveMemberValues,
bool isLocalProvider,
bool createParameters = true)
{
DebugCheck.NotNull(commandText);
DebugCheck.NotNull(commandTree);
_commandText = commandText;
_commandTree = commandTree;
_parameters = new List<DbParameter>();
_sqlGenerator = new SqlGenerator();
_memberValues
= preserveMemberValues
? new Dictionary<EdmMember, DbParameter>()
: null;
_isLocalProvider = isLocalProvider;
_createParameters = createParameters;
}
// <summary>
// Generates SQL fragment returning server-generated values.
// Requires: translator knows about member values so that we can figure out
// how to construct the key predicate.
// <code>Sample SQL:
//
// select IdentityValue
// from MyTable
// where IdentityValue = @@identity
//
// NOTE: not scope_identity() because we don't support it.</code>
// </summary>
// <param name="commandText"> Builder containing command text </param>
// <param name="tree"> Modification command tree </param>
// <param name="translator"> Translator used to produce DML SQL statement for the tree </param>
// <param name="returning"> Returning expression. If null, the method returns immediately without producing a SELECT statement. </param>
private static void GenerateReturningSql(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
ExpressionTranslator translator,
DbExpression returning)
{
if (returning != null)
{
commandText.AppendKeyword("select ");
returning.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("where ");
var target = ((DbScanExpression)tree.Target.Expression).Target;
var flag = false;
var isFirst = true;
foreach (var member in target.ElementType.KeyMembers)
{
if (!isFirst)
{
commandText.AppendKeyword(" and ");
}
else
{
isFirst = false;
}
commandText.Append(GenerateMemberTSql(member));
commandText.Append(" = ");
flag = HandleIdentity(commandText, translator, member, flag, target);
}
}
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText">Command text with which to populate commands</param>
/// <param name="commandTree">Command tree generating SQL</param>
/// <param name="preserveMemberValues">Indicates whether the translator should preserve
/// member values while compiling t-SQL (only needed for server generation)</param>
internal ExpressionTranslator(
StringBuilder commandText, DbModificationCommandTree commandTree,
bool preserveMemberValues, bool isLocalProvider)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
_commandText = commandText;
_commandTree = commandTree;
_parameters = new List<DbParameter>();
_memberValues = preserveMemberValues
? new Dictionary<EdmMember, DbParameter>()
: null;
_isLocalProvider = isLocalProvider;
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from MyTable
/// where IdentityValue = @@identity
///
/// NOTE: not scope_identity() because we don't support it.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
private static void GenerateReturningSql(
StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning)
{
if (returning != null)
{
commandText.Append("select ");
returning.Accept(translator);
commandText.AppendLine();
commandText.Append("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
commandText.Append("where ");
var target = ((DbScanExpression)tree.Target.Expression).Target;
var flag = false;
var isFirst = true;
foreach (var member in target.ElementType.KeyMembers)
{
DbParameter parameter;
if (!isFirst)
{
commandText.Append(" and ");
}
else
{
isFirst = false;
}
commandText.Append(GenerateMemberTSql(member));
commandText.Append(" = ");
if (translator.MemberValues.TryGetValue(member, out parameter))
{
commandText.Append(parameter.ParameterName);
}
else
{
if (flag)
{
throw ADP1.NotSupported(ADP1.Update_NotSupportedServerGenKey(target.Name));
}
if (!IsValidIdentityColumnType(member.TypeUsage))
{
throw ADP1.InvalidOperation(ADP1.Update_NotSupportedIdentityType(member.Name, member.TypeUsage.ToString()));
}
commandText.Append("@@IDENTITY");
flag = true;
}
}
}
}
/// <summary>
/// Initialize a new expression translator populating the given string builder
/// with command text. Command text builder and command tree must not be null.
/// </summary>
/// <param name="commandText">Command text with which to populate commands</param>
/// <param name="commandTree">Command tree generating SQL</param>
/// <param name="preserveMemberValues">Indicates whether the translator should preserve
/// member values while compiling t-SQL (only needed for server generation)</param>
internal ExpressionTranslator(
StringBuilder commandText,
DbModificationCommandTree commandTree,
bool preserveMemberValues,
bool generateParameters)
{
Debug.Assert(null != commandText);
Debug.Assert(null != commandTree);
this.commandText = commandText;
this.commandTree = commandTree;
this.parameters = new List<DbParameter>();
this.memberValues = preserveMemberValues ? new Dictionary<EdmMember, List<DbParameter>>() : null;
this.generateParameters = generateParameters;
}
