SqlDelete query text misplaced bracket

[edwardch]

Hey folks!

I'm getting a SQL syntax error when using a SqlDelete.

I have three tables

• SourceTable (PK: SourceTableId)
• TableA (FK to SourceTableId)
• TableB (FK to SourceTableId)

I am trying to write a custom SqlDelete that avoids foreign key deletions, like so:

new SqlDelete(MyRow.Fields.TableName)
    .Where(MyRow.Fields.SourceTableId.NotIn(
        new SqlQuery()
            .Select(TableARow.Fields.SourceTableId)
            .From(TableARow.Fields)
            .Union()
            .Select(TableBRow.Fields.SourceTableId)
            .From(TableBRow.Fields)
        )
    )
    .Execute(uow.Connection, ExpectedRows.Ignore);

I'm getting an error Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near ')'.

I checked the SQL it was generating, and I get this:

DELETE FROM [SourceTable] WHERE ([SourceTableId] NOT IN 
SELECT 
[SourceTableId] AS [SourceTableId] 
FROM [dbo].[TableA] T0

UNION

SELECT 
[SourceTableId] AS [SourceTableId] 
FROM [dbo].[TableB] T0)

This seems to be placing the bracket in the wrong place.

What I'm expecting to see is this

DELETE FROM [SourceTable] WHERE [SourceTableId] NOT IN 
(SELECT 
[SourceTableId] AS [SourceTableId] 
FROM [dbo].[TableA] T0

UNION

SELECT 
[SourceTableId] AS [SourceTableId] 
FROM [dbo].[TableB] T0)

Is my syntax incorrect, or is the NotIn putting the brackets in the wrong place?

Or has anyone got an idea of how I could achieve this?

Thanks in advance!
Edward

[VictorTomaili]

use ~(new Criteria(subquery)) for add parenthesis

[edwardch]

Thanks Victor,

I tried this

new SqlDelete(MyRow.Fields.TableName)
    .Where(new Criteria(MyRow.Fields.SourceTableId).NotIn(
        new SqlQuery()
            .Select(TableARow.Fields.SourceTableId)
            .From(TableARow.Fields)
            .Union()
            .Select(TableBRow.Fields.SourceTableId)
            .From(TableBRow.Fields)
        )
    )
    .Execute(uow.Connection, ExpectedRows.Ignore);

which gives the same result as before.

Then when I try

(new Criteria(MyRow.Fields.Id.NotIn(
                new SqlQuery().Select(TableARow.Fields.SourceTableId)
.From(TableARow.Fields) )...

I get an error: cannot convert BaseCriteria to string.

Maybe I'm misunderstanding?

[VictorTomaili]

new SqlDelete(MyRow.Fields.TableName)
    .Where(MyRow.Fields.SourceTableId.NotIn(
        ~(new Criteria(new SqlQuery()
            .Select(TableARow.Fields.SourceTableId)
            .From(TableARow.Fields)
            .Union()
            .Select(TableBRow.Fields.SourceTableId)
            .From(TableBRow.Fields)
        ))
    )
    .Execute(uow.Connection, ExpectedRows.Ignore);

[edwardch]

I also tried

new SqlDelete(MyRow.Fields.TableName)
    .Where(new Criteria(MyRow.Fields.SourceTableId).NotIn(new Criteria(
        new SqlQuery()
            .Select(TableARow.Fields.SourceTableId)
            .From(TableARow.Fields)
            .Union()
            .Select(TableBRow.Fields.SourceTableId)
            .From(TableBRow.Fields)
        ))
    )
    .Execute(uow.Connection, ExpectedRows.Ignore);

and get same SQL syntax error as before

[edwardch]

Ignore my last message, I think we posted at the same time...

Thanks so much Victor, that worked perfectly 👍