DBSet.Where(...).Delete() -> "no matching element" which is not true

Question

I am using EF 6.1 with EF.Extended and I am trying to execute the following:

if (allRevisions != null && allRevisions.Any(r => r.Item.Id == itemId))
    allRevisions.Where(r => r.Item.Id == itemId).Delete();

allRevisions is a DbSet<Revision> from my current DbContext (this code is inside a generic helper method).
When I execute this I get the following exception:

Sequence contains no matching element.

Which is not true as there is a matching revision and the Any is also true.
Furthermore if I execute the following it works fine:

if (allRevisions != null && allRevisions.Any(r => r.Item.Id == itemId))
{
    foreach (var revision in allRevisions.Where(r => r.Item.Id == itemId))
        allRevisions.Remove(revision);
}

But that is exactly the way you should be able to avoid with EF.Extended.

Am I doing something wrong or is this a bug in EF.Extended?

P.S.: I know that the Any is pointless - I added that to make shure there are revisions to delete after I got the error the first time. There is also no race-condition as on my dev-machine no one else is hitting the DB.
Better to materialize the query then check if it has items and delete those you need to delete all in memory. => but thats exactly what I want to avoid (and what EF.Extened is good for). I actually don't care if something has changed - I would expect it to simply execute a query like DELETE from Revisions WHERE Item_Id = @Id; in the DB.

UPDATE:
I created a small demo-project to reproduce the problem: HERE
It seems to be connected to inheritance. If I try the same thing with the ContentRevision it works, but with MyRevision, which inherits from it, it does not.

Well, technically, the database could have been mutated between the two calls, and the only item could have been removed. Of course, if this is consistently failing that's probably not the case here. However, it probably isn't best to do this in production code. In addition to doing a second DB round trip, you have that race condition. Better to materialize the query then check if it has items and delete those you need to delete all in memory. Note that with your second code snippet, the `Any` is pointless, the `foreach` simply won't iterate if the query has no items. — Servy, May 06 '14 at 17:33
Doing two round trips to the DB to do both an `Any` and a `Delete` is likely going to be less efficient for queries that don't have quite a lot of items... — Servy, May 06 '14 at 17:42
As I already said: The `Any` is just there "to make the point" that the exception is wrong. I do NOT plan to leave it there. Point of the "exercise" is to do "everything in on query" which is what EF.Extended should do and NOT load the entites to delete into memory first... — Christoph Fink, May 06 '14 at 17:44
From the documentation that you link, `Delete` takes a predicate lambda that does exactly what you're asking for - you want `allRevisions.Delete(r => r.Item.Id == itemId)` — Preston Guillot, May 06 '14 at 17:56
@PrestonGuillot The documentation is obsolete. This syntax is deprecated. The preferred syntax is now as in the post. chrfin: using the current version of EF.Extended I can execute *any* delete statement, also ones that for sure don't hit any data. Is `allRevisions` itself a query? — Gert Arnold, May 06 '14 at 18:49
@GertArnold: No, `allRevisions` is a `DbSet` passed in as a parameter, e.g. `item.Delete(db.Revisions)`. — Christoph Fink, May 06 '14 at 19:05
Can you add what SQL is being generated by the `Delete` call? — Zairja, May 09 '14 at 21:45
@Zairja: Using `db.Database.Log` I do not get any SQL logged for the `Delete`... — Christoph Fink, May 10 '14 at 06:59

score 4 · Answer 1 · answered Feb 16 '15 at 15:16

I faced the same problem. So I used your example to locate the issue. It seems to be in inheritance. In class MetadataMappingProvider is following code

        // Get the entity set that uses this entity type
        var entitySet = metadata
            .GetItems<EntityContainer>(DataSpace.CSpace)
            .Single()
            .EntitySets
            .Single(s => s.ElementType.Name == entityType.Name);

and the second Single seems to be the trouble, because in EntitySets property are just entity sets for base classes. There is a simple solution to this problem. Always use the base class (from EF point of view) in query. For example if we have following mapping:

    public class Item
{
    public long Id { get; set; }
}

public class ItemWithContent : Item
{
    public string Content { get; set; } 
}

public class TestContext : DbContext
{
    public IDbSet<Item> Items { get; set; }
}

this code will throw an error:

    using (var context = new TestContext())
{
    context.Items.OfType<ItemWithContent>()
        .Where(o => string.IsNullOrWhiteSpace(o.Content)).Delete();
}

but this code will work correctly:

using (var context = new TestContext())
{
    context.Items
        .Where(o => o is ItemWithContent && 
            string.IsNullOrWhiteSpace((o as ItemWithContent).Content)).Delete();
}

Great Hint, but does not work in all cases. If I need to update a property of `ItemWithContent` (e.g. a "downloads counter" in my case) it does not help... — Christoph Fink, Feb 28 '15 at 11:07

DBSet.Where(...).Delete() -> "no matching element" which is not true

1 Answers1