2

I have an entity called Entry connected to multiple TimeWindows. I want to clear all time windows, then add new ones. At first I tried:

target.TimeWindows.Clear();

but this didn't really delete them, and only tried to remove the relationship, which caused an exception since there is foreign key from TimeWindows to Entry. Then I thought I should do this:

foreach (var tw in target.TimeWindows)
    context.DeleteObject(tw);

but this throw an exception as well, since the collection was modified inside the foreach statement. So I thought of this:

while (target.TimeWindows.Count > 0)
    context.DeleteObject(target.TimeWindows.Last());

But now I am a bit concerned about using Count property, because it might cause a SQL SELECT COUNT statement to be executed. Does it? If yes, how can I delete all time windows in Entity Framework?

Rafid
  • 18,991
  • 23
  • 72
  • 108
  • 1
    No, the `Count` will not trigger a `SELECT COUNT` on the database, it is worse :-). It will do a `SELECT * FROM TimeWindows WHERE TargetId = @P0` and do a count on the in-memory collection. – Steven Mar 29 '12 at 09:49
  • Oh my God!! Then what is your suggestion?! – Rafid Mar 29 '12 at 12:42
  • 2
    When you want to delete an entity, it should first be loaded into memory. All O/RM tools work this way. Entity Framework is no different. If the performance is too low, write a stored procedure that does the delete. – Steven Mar 29 '12 at 13:31

1 Answers1

3

Calling count on navigation property will cause select only if lazy loading is enabled and the property is not loaded yet. So the first call can cause something like:

SELECT * FROM TimeWindows WHERE TargetId = @targetId

and all count evaluations will just execute on loaded data.

You can also use this to avoid the second exception:

foreach (var tw in target.TimeWindows.ToList())
    context.DeleteObject(tw);

Or you can change your database and model to support identifying relation (FK to parent will became part of TimeWindow's PK) and in such case your first code snippet will work.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • But executing ToList() means fetching all the time windows, right?! Well, if that is right, then it is even worse actually :-) – Rafid Mar 29 '12 at 12:42
  • In SQL, I could really execute one statement and delete all the time windows, which is something like `DELETE * FROM TimeWindows WHERE EntryId = ...`, so is it possible to de the same in Entity Framework? – Rafid Mar 29 '12 at 12:43
  • Executing `ToList` will fetch data only once. Batch deletes are not possible without [little hacking](http://stackoverflow.com/questions/9776964/what-is-the-recommended-practice-to-update-or-delete-multiple-entities-in-entity/9777017#9777017) but when using this approach changes performed in the database will not be reflected in your context. – Ladislav Mrnka Mar 29 '12 at 13:06