Bulk Delete with IQueryable Problem

[Joe]

I've encountered an issue with the DeleteAll function, whereby a doing a Join against another IQueryable object that has a Take(x) operator declared, ignores the Take restriction, so instead of only the Top x records being deleted everything matching the Join is removed. For instance -

var entities = (from c in ctx.​Entities select c).Take(5).AsQueryable(); 

 var ​toDelete = ctx.Tags.Join(entities,
                    tag => tag.EntityId,
                    entity => entity.​EntityId,
                    (tag, entity) => tag)
                    .AsQueryable();

tagsToDelete.DeleteAll(); 

This does a join on all tags and entities rather than only the top 5 entities.

I can't see any documentation to get around this problem, which I'm assuming is a bug, rather than expected behaviour.

To workaround this I was wondering if it is possible to retrieve the commandtext (including parameters) that will be executed for ​an IQueryable object instead?
This would allow me to dynamically ​generate a sql statement at runtime to do a bulk delete instead.

 Any help much appreciated.

 

 

 

[Thomas]

Hi Joe,

yes, that might look like a shortcoming, but it is very hard to do the right thing for paging/top concerning all backends involved and deletion. I suppose the best thing that you could do is to limit the amount of data that is to be deleted logically, which means you need to use conditions on the fields of the first query and provide a limiting window manually. Unfortunately I do not see any other way to do this more nicely.

Regards,
Thomas
Telerik