public void GenerateMemberTSql_returns_the_quoted_member_name()
{
Assert.Equal("[Magic]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic").Object));
Assert.Equal("[Magic]]Unicorn]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic]Unicorn").Object));
Assert.Equal("[[Magic]]]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("[Magic]").Object));
Assert.Equal("[Magic[Unicorn]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic[Unicorn").Object));
}
public string GenerateDelete(ICollection <DbDeleteCommandTree> commandTrees, string rowsAffectedParameter)
{
DebugCheck.NotNull(commandTrees);
List <SqlParameter> _;
var sql = new StringBuilder();
sql.AppendLine(
DmlSqlGenerator.GenerateDeleteSql(
commandTrees.First(),
_sqlGenerator,
out _));
sql.AppendLine();
foreach (var commandTree in commandTrees.Skip(1))
{
sql.AppendLine(
DmlSqlGenerator.GenerateDeleteSql(
commandTree,
_sqlGenerator,
out _));
sql.AppendLine("AND @@ROWCOUNT > 0");
sql.AppendLine();
}
AppendSetRowsAffected(sql, rowsAffectedParameter);
return(sql.ToString().TrimEnd());
}
private string IntroduceRequiredLocalVariables(
EntityType entityType,
DbInsertCommandTree commandTree)
{
List <EdmProperty> list = entityType.KeyProperties.Where <EdmProperty>((Func <EdmProperty, bool>)(p => p.IsStoreGeneratedIdentity)).ToList <EdmProperty>();
SqlStringBuilder commandText = new SqlStringBuilder()
{
UpperCaseKeywords = true
};
if (list.Any <EdmProperty>())
{
foreach (EdmProperty edmProperty in list)
{
commandText.Append(commandText.Length == 0 ? "DECLARE " : ", ");
commandText.Append("@");
commandText.Append(edmProperty.Name);
commandText.Append(" ");
commandText.Append(DmlSqlGenerator.GetVariableType(this._sqlGenerator, (EdmMember)edmProperty));
}
commandText.AppendLine();
DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)commandTree, true, this._sqlGenerator, (ICollection <EdmProperty>)entityType.KeyProperties, true);
DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)commandTree, entityType, translator, commandTree.Returning, DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, this._sqlGenerator.SqlVersion));
commandText.AppendLine();
commandText.AppendLine();
}
return(commandText.ToString());
}
public string GenerateUpdate(
ICollection <DbUpdateCommandTree> commandTrees,
string rowsAffectedParameter)
{
if (!commandTrees.Any <DbUpdateCommandTree>())
{
return((string)null);
}
StringBuilder sql = new StringBuilder();
List <SqlParameter> parameters;
sql.AppendLine(DmlSqlGenerator.GenerateUpdateSql(commandTrees.First <DbUpdateCommandTree>(), this._sqlGenerator, out parameters, false, true));
foreach (DbUpdateCommandTree tree in commandTrees.Skip <DbUpdateCommandTree>(1))
{
sql.Append(DmlSqlGenerator.GenerateUpdateSql(tree, this._sqlGenerator, out parameters, false, true));
sql.AppendLine("AND @@ROWCOUNT > 0");
sql.AppendLine();
}
List <DbUpdateCommandTree> list = commandTrees.Where <DbUpdateCommandTree>((Func <DbUpdateCommandTree, bool>)(ct => ct.Returning != null)).ToList <DbUpdateCommandTree>();
if (list.Any <DbUpdateCommandTree>())
{
DmlFunctionSqlGenerator.ReturningSelectSqlGenerator selectSqlGenerator = new DmlFunctionSqlGenerator.ReturningSelectSqlGenerator();
foreach (DbUpdateCommandTree updateCommandTree in list)
{
updateCommandTree.Target.Expression.Accept((DbExpressionVisitor)selectSqlGenerator);
updateCommandTree.Returning.Accept((DbExpressionVisitor)selectSqlGenerator);
updateCommandTree.Predicate.Accept((DbExpressionVisitor)selectSqlGenerator);
}
sql.AppendLine(selectSqlGenerator.Sql);
sql.AppendLine();
}
DmlFunctionSqlGenerator.AppendSetRowsAffected(sql, rowsAffectedParameter);
return(sql.ToString().TrimEnd());
}
internal static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion)
{
bool flag1 = false;
if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null)
{
HashSet <EdmMember> edmMemberSet = new HashSet <EdmMember>(tree.SetClauses.Cast <DbSetClause>().Select <DbSetClause, EdmMember>((Func <DbSetClause, EdmMember>)(s => ((DbPropertyExpression)s.Property).Property)));
bool flag2 = false;
foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
{
if (!edmMemberSet.Contains(keyMember))
{
if (flag2)
{
flag1 = true;
break;
}
flag2 = true;
if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
flag1 = true;
break;
}
}
}
}
return(flag1);
}
public string GenerateUpdate(ICollection <DbUpdateCommandTree> commandTrees, string rowsAffectedParameter)
{
DebugCheck.NotNull(commandTrees);
if (!commandTrees.Any())
{
return(null);
}
List <SqlParameter> _;
var sql = new StringBuilder();
sql.AppendLine(
DmlSqlGenerator.GenerateUpdateSql(
commandTrees.First(),
_sqlGenerator,
out _,
generateReturningSql: false));
foreach (var commandTree in commandTrees.Skip(1))
{
sql.Append(
DmlSqlGenerator.GenerateUpdateSql(
commandTree,
_sqlGenerator,
out _,
generateReturningSql: false));
sql.AppendLine("AND @@ROWCOUNT > 0");
sql.AppendLine();
}
var returningCommandTrees
= commandTrees
.Where(ct => ct.Returning != null)
.ToList();
if (returningCommandTrees.Any())
{
var returningSelectSqlGenerator = new ReturningSelectSqlGenerator();
foreach (var commandTree in returningCommandTrees)
{
commandTree.Target.Expression.Accept(returningSelectSqlGenerator);
commandTree.Returning.Accept(returningSelectSqlGenerator);
commandTree.Predicate.Accept(returningSelectSqlGenerator);
}
sql.AppendLine(returningSelectSqlGenerator.Sql);
sql.AppendLine();
}
AppendSetRowsAffected(sql, rowsAffectedParameter);
return(sql.ToString().TrimEnd());
}
public override void Visit(DbPropertyExpression expression)
{
Check.NotNull <DbPropertyExpression>(expression, nameof(expression));
if (!string.IsNullOrEmpty(this.PropertyAlias))
{
this._commandText.Append(this.PropertyAlias);
this._commandText.Append(".");
}
this._commandText.Append(DmlSqlGenerator.GenerateMemberTSql(expression.Property));
}
internal static string GenerateUpdateSql(
DbUpdateCommandTree tree,
SqlGenerator sqlGenerator,
out List <SqlParameter> parameters,
bool generateReturningSql = true,
bool upperCaseKeywords = true)
{
SqlStringBuilder commandText = new SqlStringBuilder(256)
{
UpperCaseKeywords = upperCaseKeywords
};
DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlGenerator, (ICollection <EdmProperty>)null, true);
if (tree.SetClauses.Count == 0)
{
commandText.AppendKeyword("declare ");
commandText.AppendLine("@p int");
}
commandText.AppendKeyword("update ");
tree.Target.Expression.Accept((DbExpressionVisitor)translator);
commandText.AppendLine();
bool flag = true;
commandText.AppendKeyword("set ");
foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses)
{
if (flag)
{
flag = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept((DbExpressionVisitor)translator);
commandText.Append(" = ");
setClause.Value.Accept((DbExpressionVisitor)translator);
}
if (flag)
{
commandText.Append("@p = 0");
}
commandText.AppendLine();
commandText.AppendKeyword("where ");
tree.Predicate.Accept((DbExpressionVisitor)translator);
commandText.AppendLine();
if (generateReturningSql)
{
DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, (EntityType)null, translator, tree.Returning, false);
}
parameters = translator.Parameters;
return(commandText.ToString());
}
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());
}
public string GenerateDelete(
ICollection <DbDeleteCommandTree> commandTrees,
string rowsAffectedParameter)
{
StringBuilder sql = new StringBuilder();
List <SqlParameter> parameters;
sql.AppendLine(DmlSqlGenerator.GenerateDeleteSql(commandTrees.First <DbDeleteCommandTree>(), this._sqlGenerator, out parameters, true, true));
sql.AppendLine();
foreach (DbDeleteCommandTree tree in commandTrees.Skip <DbDeleteCommandTree>(1))
{
sql.AppendLine(DmlSqlGenerator.GenerateDeleteSql(tree, this._sqlGenerator, out parameters, true, true));
sql.AppendLine("AND @@ROWCOUNT > 0");
sql.AppendLine();
}
DmlFunctionSqlGenerator.AppendSetRowsAffected(sql, rowsAffectedParameter);
return(sql.ToString().TrimEnd());
}
public string GenerateInsert(ICollection <DbInsertCommandTree> commandTrees)
{
StringBuilder stringBuilder = new StringBuilder();
DbInsertCommandTree insertCommandTree1 = commandTrees.First <DbInsertCommandTree>();
List <SqlParameter> parameters;
stringBuilder.Append(DmlSqlGenerator.GenerateInsertSql(insertCommandTree1, this._sqlGenerator, out parameters, false, true, false));
stringBuilder.AppendLine();
EntityType elementType = (EntityType)((DbScanExpression)insertCommandTree1.Target.Expression).Target.ElementType;
stringBuilder.Append(this.IntroduceRequiredLocalVariables(elementType, insertCommandTree1));
foreach (DbInsertCommandTree tree in commandTrees.Skip <DbInsertCommandTree>(1))
{
stringBuilder.Append(DmlSqlGenerator.GenerateInsertSql(tree, this._sqlGenerator, out parameters, false, true, false));
stringBuilder.AppendLine();
}
List <DbInsertCommandTree> list = commandTrees.Where <DbInsertCommandTree>((Func <DbInsertCommandTree, bool>)(ct => ct.Returning != null)).ToList <DbInsertCommandTree>();
if (list.Any <DbInsertCommandTree>())
{
DmlFunctionSqlGenerator.ReturningSelectSqlGenerator selectSqlGenerator = new DmlFunctionSqlGenerator.ReturningSelectSqlGenerator();
foreach (DbInsertCommandTree insertCommandTree2 in list)
{
insertCommandTree2.Target.Expression.Accept((DbExpressionVisitor)selectSqlGenerator);
insertCommandTree2.Returning.Accept((DbExpressionVisitor)selectSqlGenerator);
}
foreach (EdmProperty keyProperty1 in elementType.KeyProperties)
{
EdmProperty keyProperty = keyProperty1;
DbExpression right = insertCommandTree1.SetClauses.Cast <DbSetClause>().Where <DbSetClause>((Func <DbSetClause, bool>)(sc => ((DbPropertyExpression)sc.Property).Property == keyProperty)).Select <DbSetClause, DbExpression>((Func <DbSetClause, DbExpression>)(sc => sc.Value)).SingleOrDefault <DbExpression>() ?? (DbExpression)keyProperty.TypeUsage.Parameter(keyProperty.Name);
insertCommandTree1.Target.Variable.Property(keyProperty).Equal(right).Accept((DbExpressionVisitor)selectSqlGenerator);
}
stringBuilder.Append(selectSqlGenerator.Sql);
}
return(stringBuilder.ToString().TrimEnd());
}
public string GenerateInsert(ICollection <DbInsertCommandTree> commandTrees)
{
DebugCheck.NotNull(commandTrees);
var sql = new StringBuilder();
List <SqlParameter> _;
var firstCommandTree = commandTrees.First();
sql.Append(
DmlSqlGenerator.GenerateInsertSql(
firstCommandTree,
_sqlGenerator,
out _,
generateReturningSql: false,
createParameters: false));
sql.AppendLine();
var firstTable
= (EntityType)((DbScanExpression)firstCommandTree.Target.Expression).Target.ElementType;
sql.Append(IntroduceRequiredLocalVariables(firstTable, firstCommandTree));
foreach (var commandTree in commandTrees.Skip(1))
{
sql.Append(
DmlSqlGenerator.GenerateInsertSql(
commandTree,
_sqlGenerator,
out _,
generateReturningSql: false,
createParameters: false));
sql.AppendLine();
}
var returningCommandTrees
= commandTrees
.Where(ct => ct.Returning != null)
.ToList();
if (returningCommandTrees.Any())
{
var returningSelectSqlGenerator = new ReturningSelectSqlGenerator();
foreach (var commandTree in returningCommandTrees)
{
commandTree.Target.Expression.Accept(returningSelectSqlGenerator);
commandTree.Returning.Accept(returningSelectSqlGenerator);
}
foreach (var keyProperty in firstTable.KeyProperties)
{
var parameterReference
= firstCommandTree
.SetClauses
.Cast <DbSetClause>()
.Where(sc => ((DbPropertyExpression)sc.Property).Property == keyProperty)
.Select(sc => sc.Value)
.SingleOrDefault()
?? keyProperty.TypeUsage.Parameter(keyProperty.Name);
firstCommandTree
.Target
.Variable
.Property(keyProperty)
.Equal(parameterReference)
.Accept(returningSelectSqlGenerator);
}
sql.Append(returningSelectSqlGenerator.Sql);
}
return(sql.ToString().TrimEnd());
}
internal static string GenerateInsertSql(
DbInsertCommandTree tree,
SqlGenerator sqlGenerator,
out List <SqlParameter> parameters,
bool generateReturningSql = true,
bool upperCaseKeywords = true,
bool createParameters = true)
{
SqlStringBuilder commandText = new SqlStringBuilder(256)
{
UpperCaseKeywords = upperCaseKeywords
};
DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlGenerator, (ICollection <EdmProperty>)null, createParameters);
bool useGeneratedValuesVariable = DmlSqlGenerator.UseGeneratedValuesVariable(tree, sqlGenerator.SqlVersion);
EntityType elementType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType;
if (useGeneratedValuesVariable)
{
commandText.AppendKeyword("declare ").Append("@generated_keys").Append(" table(");
bool flag = true;
foreach (EdmMember keyMember in elementType.KeyMembers)
{
if (flag)
{
flag = false;
}
else
{
commandText.Append(", ");
}
commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember)).Append(" ").Append(DmlSqlGenerator.GetVariableType(sqlGenerator, keyMember));
Facet facet;
if (keyMember.TypeUsage.Facets.TryGetValue("Collation", false, out facet))
{
string s = facet.Value as string;
if (!string.IsNullOrEmpty(s))
{
commandText.AppendKeyword(" collate ").Append(s);
}
}
}
commandText.AppendLine(")");
}
commandText.AppendKeyword("insert ");
tree.Target.Expression.Accept((DbExpressionVisitor)translator);
if (0 < tree.SetClauses.Count)
{
commandText.Append("(");
bool flag = true;
foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses)
{
if (flag)
{
flag = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept((DbExpressionVisitor)translator);
}
commandText.AppendLine(")");
}
else
{
commandText.AppendLine();
}
if (useGeneratedValuesVariable)
{
commandText.AppendKeyword("output ");
bool flag = true;
foreach (EdmMember keyMember in elementType.KeyMembers)
{
if (flag)
{
flag = false;
}
else
{
commandText.Append(", ");
}
commandText.Append("inserted.");
commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember));
}
commandText.AppendKeyword(" into ").AppendLine("@generated_keys");
}
if (0 < tree.SetClauses.Count)
{
bool flag = true;
commandText.AppendKeyword("values (");
foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses)
{
if (flag)
{
flag = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept((DbExpressionVisitor)translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
commandText.AppendKeyword("default values");
commandText.AppendLine();
}
if (generateReturningSql)
{
DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, elementType, translator, tree.Returning, useGeneratedValuesVariable);
}
parameters = translator.Parameters;
return(commandText.ToString());
}
internal static void GenerateReturningSql(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
EntityType tableType,
DmlSqlGenerator.ExpressionTranslator translator,
DbExpression returning,
bool useGeneratedValuesVariable)
{
if (returning == null)
{
return;
}
commandText.AppendKeyword("select ");
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = "t";
}
returning.Accept((DbExpressionVisitor)translator);
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = (string)null;
}
commandText.AppendLine();
if (useGeneratedValuesVariable)
{
commandText.AppendKeyword("from ");
commandText.Append("@generated_keys");
commandText.AppendKeyword(" as ");
commandText.Append("g");
commandText.AppendKeyword(" join ");
tree.Target.Expression.Accept((DbExpressionVisitor)translator);
commandText.AppendKeyword(" as ");
commandText.Append("t");
commandText.AppendKeyword(" on ");
string keyword = string.Empty;
foreach (EdmMember keyMember in tableType.KeyMembers)
{
commandText.AppendKeyword(keyword);
keyword = " and ";
commandText.Append("g.");
string memberTsql = DmlSqlGenerator.GenerateMemberTSql(keyMember);
commandText.Append(memberTsql);
commandText.Append(" = t.");
commandText.Append(memberTsql);
}
commandText.AppendLine();
commandText.AppendKeyword("where @@ROWCOUNT > 0");
}
else
{
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept((DbExpressionVisitor)translator);
commandText.AppendLine();
commandText.AppendKeyword("where @@ROWCOUNT > 0");
EntitySetBase target = ((DbScanExpression)tree.Target.Expression).Target;
bool flag = false;
foreach (EdmMember keyMember in target.ElementType.KeyMembers)
{
commandText.AppendKeyword(" and ");
commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember));
commandText.Append(" = ");
SqlParameter sqlParameter;
if (translator.MemberValues.TryGetValue(keyMember, out sqlParameter))
{
commandText.Append(sqlParameter.ParameterName);
}
else
{
if (flag)
{
throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey((object)target.Name));
}
if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw new InvalidOperationException(Strings.Update_NotSupportedIdentityType((object)keyMember.Name, (object)keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
flag = true;
}
}
}
}