/// <inheritdoc/> protected override SqlProvider VisitExcept(ExceptProvider provider) { var left = Compile(provider.Left); var right = Compile(provider.Right); var leftSelect = left.Request.Statement; var keepOrderForLeft = (leftSelect.HasLimit || leftSelect.HasOffset) && providerInfo.Supports(ProviderFeatures.PagingRequiresOrderBy); if (!keepOrderForLeft) { leftSelect.OrderBy.Clear(); } var rightSelect = right.Request.Statement; var keepOrderForRight = (leftSelect.HasLimit || leftSelect.HasOffset) && providerInfo.Supports(ProviderFeatures.PagingRequiresOrderBy); if (!keepOrderForRight) { rightSelect.OrderBy.Clear(); } var result = SqlDml.Except(leftSelect, rightSelect); var queryRef = SqlDml.QueryRef(result); SqlSelect query = SqlDml.Select(queryRef); query.Columns.AddRange(queryRef.Columns.Cast <SqlColumn>()); return(CreateProvider(query, provider, left, right)); }
public void ExceptTest() { SqlSelect s1 = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["main"].Tables["track"])); s1.Columns.Add(SqlDml.Asterisk); SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["main"].Tables["track"])); s2.Columns.Add(SqlDml.Asterisk); SqlSelect s3 = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["main"].Tables["track"])); s3.Columns.Add(SqlDml.Asterisk); Console.WriteLine(sqlDriver.Compile(s1.Except(s2)).GetCommandText()); Console.WriteLine(sqlDriver.Compile(s1.Except(s2).Except(s3)).GetCommandText()); Console.WriteLine(sqlDriver.Compile(s1.Except(s2.Except(s3))).GetCommandText()); Console.WriteLine(sqlDriver.Compile(SqlDml.Except(s1, s2)).GetCommandText()); Console.WriteLine(sqlDriver.Compile(SqlDml.Except(s1, s1.Except(s2))).GetCommandText()); Console.WriteLine(sqlDriver.Compile(SqlDml.Except(s1.Except(s2), s1)).GetCommandText()); Console.WriteLine(sqlDriver.Compile(SqlDml.Except(s1.Except(s2), s1.Except(s2))).GetCommandText()); }
public SqlQueryExpression Except(ISqlQueryExpression operand) { return(SqlDml.Except(this, operand)); }