PostgreSQL BatchDelete with join

[fabiopand]

Let´s supose we try to do something like this:

context.Items.Join(context.InnerItems, item => item.Id, inner => inner.ItemId, (item, inner) => new { ItemName = item.Name, InnerName = inner.Name }).BatchDelete();

It will generate a SQL like this:

DELETE [i] FROM [Item_29e378] AS [i] INNER JOIN [InnerItem_29e378] AS [i0] ON [i].[Id] = [i0].[ItemId]

But PostgreSQL does not support delete with inner join. I think that it´s possible to change QuerySqlGenerator class a little bit and translate delete join to with using statement, resulting in something like this:

DELETE FROM [Item_29e378] AS [i] USING [InnerItem_29e378] AS [i0] WHERE [i].[Id] = [i0].[ItemId]

[yang-er]

Well I think there is only BatchDelete for entities... (BTW, I think that situation is suppressed by my code manually... I don't why it works and still translates to sql. Will take a look later.)

The semantic of your sentence is unclear. Are we going to delete all Items or InnerItems or both? It looks so weird.

So if your idea is that deleting all items with inner items linked, I would like to rewrite the query as

context.Items
    .Where(item => context.InnerItems.Where(inner => inner.ItemId == item.Id).Any())
    .BatchDeleteAsync();

It's clear and have strong semantic.

So what do you think about the delete join?