Entity Framework Core: remove a relationship but not delete the entities

Question

How would you delete a relationship assuming you had the 2 entities, but did not have the 'relationship' entity?

Assuming the following entities...

Model classes:

public class DisplayGroup
{
        [Key]
        public int GroupId { get; set; }
        public string Description { get; set; }
        public string Name { get; set; }
        public ICollection<LookUpGroupItem> LookUpGroupItems { get; set; }
}

public class DisplayItem
{
        [Key]
        public int ItemId { get; set; }
        public string Description { get; set; }
        public string FileType { get; set; }
        public string FileName { get; set; }
        public ICollection<LookUpGroupItem> LookUpGroupItems { get; set; }
}

public class LookUpGroupItem
{
        public int ItemId { get; set; }
        public DisplayItem DisplayItem { get; set; }
        public int GroupId { get; set; }
        public DisplayGroup DisplayGroup { get; set; }
}

Here is the code for deleting a relationship. Note: I do not want to delete the entities, they just no longer share a relationship.

public void RemoveLink(DisplayGroup g, DisplayItem d)
{
    _dataContext.Remove(g.LookUpGroupItems.Single(x => x.ItemId == d.ItemId));
}

The method above causes an error:

System.ArgumentNullException occurred
Message=Value cannot be null.

It looks like this is the case because LookUpGroupItems is null, but these were called from the database. I would agree that I do not want to load all entity relationship objects whenever I do a Get from the database, but then, what is the most efficient way to do this?

Additional NOTE: this question is not about an argument null exception. It explicitly states how to delete a relationship in Entity Framework Core.

If you do not set your navigation properties to `virtual` then EF will not be able to populate them when you retrieve the entity from the database. Also, you need to initialize the collection properties in the entity constructor to avoid `NullReferenceException` on newly created objects. — Federico Dipuma, Dec 03 '17 at 09:54
I didn't see you tagged EF Core: it [does not support Lazy Loading yet](https://github.com/aspnet/EntityFrameworkCore/issues/3797), so to load your related entities (e.g. your collections) you need to manually load them using [Eager or Explicit loading](https://learn.microsoft.com/en-us/ef/core/querying/related-data) — Federico Dipuma, Dec 03 '17 at 10:29

score 4 · Answer 1 · answered Dec 03 '17 at 16:24

The following is not the most efficient, but is the most reliable way:

public void RemoveLink(DisplayGroup g, DisplayItem d)
{
    var link = _dataContext.Find<LookUpGroupItem>(g.GroupId, d.ItemId); // or (d.ItemId, g.GroupId) depending of how the composite PK is defined
    if (link != null)
        _dataContext.Remove(link);
}

It's simple and straightforward. Find method is used to locate the entity in the local cache or load it the from the database. If found, the Remove method is used to mark it for deletion (which will be applied when you call SaveChanges).

It's not the most efficient because of the database roundtrip when the entity is not contained in the local cache.

The most efficient is to use "stub" entity (with only FK properties populated):

var link = new LookUpGroupItem { GroupId = g.GroupId, ItemId = d.ItemId };
_dataContext.Remove(link);

This will only issue DELETE SQL command when ApplyChanges is called. However it has the following drawbacks:

(1) If _dataContext already contains (is tracking) a LookUpGroupItem entity with the same PK, the Remove call will throw InvalidOperationException saying something like "The instance of entity type 'LookUpGroupItem' cannot be tracked because another instance with the key value 'GroupId:1, ItemId:1' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached."

(2) If database table does not contain a record with the specified composite PK, the SaveChanges will throw DbUpdateConcurrencyException saying "Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions." (this behavior is actually considered a bug by many people including me, but this is how it is).

Shorty, you can use the optimized method only if you use short lived newly create DbContext just for that operation and you are absolutely sure the record with such PK exists in the database. In all other cases (and in general) you should use the first method.

I may have to ask a new question and ask it differently. I want to avoid the second roundtrip, and obviously not have an exception, as that is inherently going to slow down transactions. But in this instance it might work, since i know my two entities will almost surely have a relationship defined. — christopher clark, Dec 03 '17 at 17:36
I don't see how asking a new question differently would help, since I covered all possible options. Eventually a combination of the two - search `DbSet.Local` (no good method for that), then use stub if not found. This will prevent (1), but (2) still may happen and currently there is no way to control that behavior. — Ivan Stoev, Dec 03 '17 at 17:54

Entity Framework Core: remove a relationship but not delete the entities

1 Answers1