29

When I am trying to clear a collection (calling .Clear) I get the following exception:

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

The inner exception is:

A relationship from the 'User_Availability' AssociationSet is in the 'Deleted' state. Given multiplicity constraints, a corresponding 'User_Availability_Target' must also in the 'Deleted' state.

User looks like this:

....
ICollection<Availability> Availability { get; set; }

Availability looks like this:

int ID { get; set; }
User User { get; set; }
DateTime Start { get; set;
DateTime End { get; set; }

Configuration is as follows:

HasMany(x => x.Availability).WithRequired(x => x.User);
HasRequired(x => x.User).WithMany(x => x.Availability);

The code causing the problem is:

user.Availability.Clear();

I've looked at other alternatives such as using the DbSet to remove items, but I don't feel my code will be as clean. Is there a way to accomplish this by clearing the collection?

gunr2171
  • 16,104
  • 25
  • 61
  • 88
Sam
  • 4,219
  • 7
  • 52
  • 80

3 Answers3

33

The only way that I'm aware of to make it work is defining the relationship as an identifying relationship. It would required to introduce the foreign key from Availability to User as a foreign key into your model...

public int ID { get; set; }
public int UserID { get; set; }
public User User { get; set; }

...and make it part of the primary key:

modelBuilder.Entity<Availability>()
    .HasKey(a => new { a.ID, a.UserID });

You can extend your mapping to include this foreign key (just to be explicit, it isn't required because EF will recognize it by convention):

modelBuilder.Entity<Availability>()
    .HasRequired(a => a.User)
    .WithMany(u => u.Availability)
    .HasForeignKey(a => a.UserID);

(BTW: You need to configure the relationship only from one side. It is not required to have both these mappings in your question.)

Now you can clear the collection with user.Availability.Clear(); and the Availability entities will be deleted from the database.

Gebb
  • 6,371
  • 3
  • 44
  • 56
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • Thanks for posting this! When I run an update on the child object, I'm getting multiple SET statements on the same column, due to the FK property and navigation property. Is there a trick to only generate one SET statement? – awright Dec 12 '13 at 15:33
  • @Thomas: I have no clue. SQL generation is pretty much in the hand of EF and hard to control. I'm actually surprised that you get two SET expressions for the same FK column. EF should know that the FK property and the nav. property represent the same FK column and relationship. – Slauma Dec 16 '13 at 21:18
  • 1
    Are you sure your example is correct? You have modelBuilder.Entity().HasRequired(x => x.User) ... Shouldn't that be the configuration for Entity()? I tried this configuration, but got an error message: "The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. ..." . Maybe this works for the many side if the foreign keys are nullable? – Derek Greer Aug 05 '15 at 21:55
  • Solved my problem but I got a "Cannot insert explicit value for identity column in table xxx when IDENTITY_INSERT is set to OFF." The solution was to add the attribute `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]` to the original ID column on (in this case) Availability. – ADBailey Aug 17 '16 at 11:40
  • 1
    N.B. `` should indeed be `` as Derek commented. – ADBailey Aug 17 '16 at 15:24
  • @DerekGreer "thanks" to peer reviewers my edit with the fix was rejected. But anyway, the corrected answer is here https://stackoverflow.com/review/suggested-edits/19796520 – Monsignor May 22 '18 at 09:25
1

There is one trick. You can delete entities without using special DbSet:

(this.dataContext as IObjectContextAdapter).ObjectContext.DeleteObject(entity);

Execute this for each item in Availability collection before clearing it. You don't need 'identifying relationships' for this way.

poul_ko
  • 347
  • 2
  • 6
0

In case someone has the same problem using SQLite:

Unfortunately the accepted answer does not work with SQLite because SQLite does not support auto increment for composite keys.

You can also override the SaveChanges() Method in the Database context to delete the children: 

//// Long Version
//var localChilds = this.SubCategories.Local.ToList();
//var deletedChilds = localChilds.Where(w => w.Category == null).ToList();
//foreach(var child in deletedChilds) {
//   this.SubCategories.Remove(child);
//}

// Short in LINQ
this.SubCategories.Local
    .Where(w => w.Category == null).ToList()
    .ForEach(fe => this.SubCategories.Remove(fe));
#endregion

See this great Blogpost as my source (Unfortunately written in german).

Sven Weber
  • 1