/// <summary>
/// get Update Sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static (string, List <object>) GetSqlUpdate <T>(IQueryable <T> query, DbContext context, Type type, Expression <Func <T, T> > expression) where T : class
{
(string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: true);
var createUpdateBodyData = new BatchUpdateCreateBodyData(sql, context, innerParameters, query, type, tableAlias, expression);
CreateUpdateBody(createUpdateBodyData, expression.Body);
var sqlParameters = ReloadSqlParameters(context, createUpdateBodyData.SqlParameters); // Sqlite requires SqliteParameters
var sqlColumns = (createUpdateBodyData.DatabaseType == DbServer.SQLServer)
? createUpdateBodyData.UpdateColumnsSql
: createUpdateBodyData.UpdateColumnsSql.Replace($"[{tableAlias}].", "");
var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} SET {sqlColumns} {sql}";
if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
{
string tableAliasPrefix = "[" + tableAlias + "].";
resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C SET {sqlColumns.Replace(tableAliasPrefix, "")}";
}
if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
{
resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
}
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
if (databaseType == DbServer.PostgreSQL)
{
resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);
var npgsqlParameters = new List <object>();
foreach (var param in sqlParameters)
{
var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);
string paramName = npgsqlParam.ParameterName.Replace("@", "");
var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?))
{
npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
}
npgsqlParameters.Add(npgsqlParam);
}
sqlParameters = npgsqlParameters;
}
return(resultQuery, sqlParameters);
}
// SELECT [a].[Column1], [a].[Column2], .../r/n
// FROM [Table] AS [a]/r/n
// WHERE [a].[Column] = FilterValue
// --
// UPDATE [a] SET [UpdateColumns] = N'updateValues'
// FROM [Table] AS [a]
// WHERE [a].[Columns] = FilterValues
public static (string, List <object>) GetSqlUpdate(IQueryable query, DbContext context, Type type, object updateValues, List <string> updateColumns)
{
var(sql, tableAlias, tableAliasSufixAs, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: true);
var sqlParameters = new List <object>(innerParameters);
string sqlSET = GetSqlSetSegment(context, updateValues.GetType(), updateValues, updateColumns, sqlParameters);
sqlParameters = ReloadSqlParameters(context, sqlParameters); // Sqlite requires SqliteParameters
var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} {sqlSET}{sql}";
if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
{
resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C {sqlSET}";
}
if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
{
resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
}
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
if (databaseType == DbServer.PostgreSQL)
{
resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);
var npgsqlParameters = new List <object>();
foreach (var param in sqlParameters)
{
var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);
string paramName = npgsqlParam.ParameterName.Replace("@", "");
var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?)) // for JsonDocument works without fix
{
npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
}
npgsqlParameters.Add(npgsqlParam);
}
sqlParameters = npgsqlParameters;
}
return(resultQuery, sqlParameters);
}
// In comment are Examples of how SqlQuery is changed for Sql Batch
// SELECT [a].[Column1], [a].[Column2], .../r/n
// FROM [Table] AS [a]/r/n
// WHERE [a].[Column] = FilterValue
// --
// DELETE [a]
// FROM [Table] AS [a]
// WHERE [a].[Columns] = FilterValues
public static (string, List <object>) GetSqlDelete(IQueryable query, DbContext context)
{
var(sql, tableAlias, _, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: false);
innerParameters = ReloadSqlParameters(context, innerParameters.ToList()); // Sqlite requires SqliteParameters
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
string resultQuery;
if (databaseType == DbServer.SQLServer)
{
tableAlias = $"[{tableAlias}]";
int outerQueryOrderByIndex = -1;
var useUpdateableCte = false;
var lastOrderByIndex = sql.LastIndexOf(Environment.NewLine + $"ORDER BY ", StringComparison.OrdinalIgnoreCase);
if (lastOrderByIndex > -1)
{
var subQueryEnd = sql.LastIndexOf($") AS {tableAlias}" + Environment.NewLine, StringComparison.OrdinalIgnoreCase);
if (subQueryEnd == -1 || lastOrderByIndex > subQueryEnd)
{
outerQueryOrderByIndex = lastOrderByIndex;
if (topStatement.Length > 0)
{
useUpdateableCte = true;
}
else
{
int offSetIndex = sql.LastIndexOf(Environment.NewLine + "OFFSET ", StringComparison.OrdinalIgnoreCase);
if (offSetIndex > outerQueryOrderByIndex)
{
useUpdateableCte = true;
}
}
}
}
if (useUpdateableCte)
{
var cte = "cte" + Guid.NewGuid().ToString().Substring(0, 8); // 8 chars of Guid as tableNameSuffix to avoid same name collision with other tables
resultQuery = $"{leadingComments}WITH [{cte}] AS (SELECT {topStatement}* {sql}) DELETE FROM [{cte}]";
}
else
{
if (outerQueryOrderByIndex > -1)
{
// ORDER BY is not allowed without TOP or OFFSET.
sql = sql.Substring(0, outerQueryOrderByIndex);
}
resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
}
}
else
{
resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
}
if (databaseType == DbServer.PostgreSQL)
{
resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery, isDelete: true);
var npgsqlParameters = new List <object>();
foreach (var param in innerParameters)
{
npgsqlParameters.Add(new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value));
}
innerParameters = npgsqlParameters;
}
return(resultQuery, new List <object>(innerParameters));
}