/// <summary>
/// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a very particular syntax that
/// needs to be used to work for all servers: SQLCE and MSSQL
/// </summary>
/// <returns></returns>
/// <remarks>
/// See: http://issues.umbraco.org/issue/U4-3876
/// </remarks>
public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery, WhereInType whereInType = WhereInType.In)
{
//TODO: This is no longer necessary since this used to be a specific requirement for MySql!
// Now we can do a Delete<T> + sub query, see RelationRepository.DeleteByParent for example
return
(new Sql(string.Format(
whereInType == WhereInType.In
? @"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)"
: @"DELETE FROM {0} WHERE {1} NOT IN (SELECT {1} FROM ({2}) x)",
sqlProvider.GetQuotedTableName(tableName),
sqlProvider.GetQuotedColumnName(columnName),
subQuery.SQL), subQuery.Arguments));
}
/// <summary>
/// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a very particular syntax that
/// needs to be used to work for all servers: SQLCE and MSSQL
/// </summary>
/// <returns></returns>
/// <remarks>
/// See: http://issues.umbraco.org/issue/U4-3876
/// </remarks>
public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery, WhereInType whereInType = WhereInType.In)
{
return
(new Sql(string.Format(
whereInType == WhereInType.In
? @"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)"
: @"DELETE FROM {0} WHERE {1} NOT IN (SELECT {1} FROM ({2}) x)",
sqlProvider.GetQuotedTableName(tableName),
sqlProvider.GetQuotedColumnName(columnName),
subQuery.SQL), subQuery.Arguments));
}