Entity Framework .Remove() Vs. .Deleteobject()

Entity Framework .Remove() vs. .DeleteObject()

It's not generally correct that you can "remove an item from a database" with both methods. To be precise it is like so:

ObjectContext.DeleteObject(entity) marks the entity as Deleted in the context. (It's EntityState is Deleted after that.) If you call SaveChanges afterwards EF sends a SQL DELETE statement to the database. If no referential constraints in the database are violated the entity will be deleted, otherwise an exception is thrown.
EntityCollection.Remove(childEntity) marks the relationship between parent and childEntity as Deleted. If the childEntity itself is deleted from the database and what exactly happens when you call SaveChanges depends on the kind of relationship between the two:
- If the relationship is optional, i.e. the foreign key that refers from the child to the parent in the database allows NULL values, this foreign will be set to null and if you call SaveChanges this NULL value for the childEntity will be written to the database (i.e. the relationship between the two is removed). This happens with a SQL UPDATE statement. No DELETE statement occurs.
- If the relationship is required (the FK doesn't allow NULL values) and the relationship is not identifying (which means that the foreign key is not part of the child's (composite) primary key) you have to either add the child to another parent or you have to explicitly delete the child (with DeleteObject then). If you don't do any of these a referential constraint is violated and EF will throw an exception when you call SaveChanges - the infamous "The relationship could not be changed because one or more of the foreign-key properties is non-nullable" exception or similar.
- If the relationship is identifying (it's necessarily required then because any part of the primary key cannot be NULL) EF will mark the childEntity as Deleted as well. If you call SaveChanges a SQL DELETE statement will be sent to the database. If no other referential constraints in the database are violated the entity will be deleted, otherwise an exception is thrown.

I am actually a bit confused about the Remarks section on the MSDN page you have linked because it says: "If the relationship has a referential integrity constraint, calling the Remove method on a dependent object marks both the relationship and the dependent object for deletion.". This seems unprecise or even wrong to me because all three cases above have a "referential integrity constraint" but only in the last case the child is in fact deleted. (Unless they mean with "dependent object" an object that participates in an identifying relationship which would be an unusual terminology though.)

Entity Framework Remove vs State.Deleted

Not that I can tell, the MSDN article for Remove says

Marks the given entity as Deleted such that it will be deleted from the database when SaveChanges is called. Note that the entity must exist in the context in some other state before this method is called.

.Remove is likely just the preferred way to remove items.

EDIT:

Also, the MSDN article for the EntityState says this for deleted.

The entity is being tracked by the context and exists in the database, but has been marked for deletion from the database the next time SaveChanges is called.

Which just further solidifies that they are effectively the same thing.

How to delete a list of objects in EF6 when the object is detached from the context

To be able to remove records, you need to make sure your ObjectContext is tracking them. Right now you have detached objects, and your context has no knowledge of them so it's impossible to delete them. One way to remove them is to do like you say, Attach all your objects to the context, then delete them. The other way is to fetch the records from the database so you can remove them:

//Find all groups in database with an Id that is in your group collection 'ug'
var groups = context.My_Groups.Where(g => ug.Any(u => u.Id == g.Id));
context.My_Groups.RemoveRange(groups);
context.SaveChanges();

However, note that even while using RemoveRange, a delete command will be send to the database per item you want to remove. The only difference between RemoveRange and Remove is that the first will only call DetectChanges once, which can really improve performance.

Entity Framework Core, deleting items from nested collection

That is because the rows in the database are not marked for deletion.

Only new or changed items are updated. 'Missing' items from a collection are not considered to be deleted.

So what you'll need to do is mark the items for deletion yourself. Something like this:

public void Update(Invoice record)
{
    var missingRows = dB.InvoiceRows.Where(i => i.InvoiceId == record.Id)
                        .Except(record.Rows);
    dB.InvoiceRows.RemoveRange(missingRows);

    dB.Invoices.Update(record);
    dB.SaveChanges();
}

Entity Framework Reference constraint error on delete when using selectmany

Problem

Additionally to the 3 standard ways of loading related data (eager, explicit and lazy), EF6 supports another way through a process called "navigation property fix-up", which is utilized by queries like your

rolesQuery.SelectMany(x => x.Permissions).Load();

Note that the name Load is a bit misleading. Load is EF custom extension method which simply executes the query and iterates the result set, similar to ToList, but without creating a list.

The first 3 methods work for any type of relationship. However the last doesn't work for many-to-many with implicit link entity relationships, because there is no way to specify the "link" entity in the LINQ query, so the following

rolesQuery.SelectMany(x => x.Organizations).Load();

and

context.Organizations.Load();

are equivalent - both return (and load) Organization entities.

The standard solution is to use some of the 3 standard ways. But eager loading generates huge union data set, while explicit and lazy loading generate N + 1 database queries.

Solution

Many-to-many with implicit link entity relationships are implemented as independent associations and are maintained purely by the context change tracker. DbContext APIs do not provide a way to maintain such relationships state, but as mentioned in the Creating and modifying relationships section of the EF6 documentation, ObjectContext APIs do with ChangeRelationshipState method of the ObjectStateManager.

Following is a custom generic extension method which solves the problem by utilizing the aforementioned method. The essential part is

// Query to retrieve IEnumerable<Tuple<TSourceKey, TTarget>> from database
// and group it by TSourceKey in memory
var groupedLinksQuery = sourceDbQuery
    .SelectLinks(keySelector, collectionSelector)
    .AsEnumerable()
    .GroupBy(e => e.Item1, e => e.Item2);
// Execute the query and perform the fix-up
foreach (var group in groupedLinksQuery)
{
    var source = sourceDbSet.Find(group.Key);
    foreach (var target in group)
        stateManager.ChangeRelationshipState(source, target, collectionPropertyName, EntityState.Unchanged);
}

Sample usage:

var roles = rolesQuery.ToArray();
rolesQuery.SelectMany(role => role.Permissions).Load();
context.LoadLinks(rolesQuery, role => role.Id, role => role.Organizations); // <--

Full code:

using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;

namespace System.Data.Entity
{
    public static partial class EF6Extensions
    {
        public static void LoadLinks<TSource, TSourceKey, TTarget>(this DbContext dbContext, IQueryable<TSource> sourceDbQuery, Expression<Func<TSource, TSourceKey>> keySelector, Expression<Func<TSource, ICollection<TTarget>>> collectionSelector)
            where TSource : class
            where TTarget : class
        {
            // Disable AutoDetectChanges for better performance
            bool autoDetectChanges = dbContext.Configuration.AutoDetectChangesEnabled;
            dbContext.Configuration.AutoDetectChangesEnabled = false;
            try
            {
                var sourceDbSet = dbContext.Set<TSource>();
                var collectionPropertyName = ((MemberExpression)collectionSelector.Body).Member.Name;
                var stateManager = dbContext.GetObjectStateManager();
                // Query to retrieve IEnumerable<Tuple<TSourceKey, TTarget>> from database
                // and group it by TSourceKey in memory
                var groupedLinksQuery = sourceDbQuery
                    .SelectLinks(keySelector, collectionSelector)
                    .AsEnumerable()
                    .GroupBy(e => e.Item1, e => e.Item2);
                // Execute the query and perform the fix-up
                foreach (var group in groupedLinksQuery)
                {
                    var source = sourceDbSet.Find(group.Key);
                    foreach (var target in group)
                        stateManager.ChangeRelationshipState(source, target, collectionPropertyName, EntityState.Unchanged);
                }
            }
            finally { dbContext.Configuration.AutoDetectChangesEnabled = autoDetectChanges; }
        }

        static IQueryable<Tuple<TSourceKey, TTarget>> SelectLinks<TSource, TSourceKey, TTarget>(this IQueryable<TSource> sourceQuery, Expression<Func<TSource, TSourceKey>> keySelector, Expression<Func<TSource, ICollection<TTarget>>> collectionSelector)
        {
            // sourceQuery.SelectMany(source => source.Collection, (source, target) => Tuple(source.Key, target))
            var source = keySelector.Parameters[0];
            var target = Expression.Parameter(typeof(TTarget), "target");
            var resultType = typeof(Tuple<TSourceKey, TTarget>);
            var constructor = resultType.GetConstructor(new[] { typeof(TSourceKey), typeof(TTarget) });
            var args = new[] { keySelector.Body, target };
            var members = new[] { resultType.GetProperty("Item1"), resultType.GetProperty("Item2") };
            var body = Expression.New(constructor, args, members);
            var selector = Expression.Lambda<Func<TSource, TTarget, Tuple<TSourceKey, TTarget>>>(
                body, source, target);
            return sourceQuery.SelectMany(collectionSelector.AsEnumerable(), selector);
        }

        static Expression<Func<TSource, IEnumerable<TTarget>>> AsEnumerable<TSource, TTarget>(this Expression<Func<TSource, ICollection<TTarget>>> collectionSelector)
            => Expression.Lambda<Func<TSource, IEnumerable<TTarget>>>(collectionSelector.Body, collectionSelector.Parameters);

        public static ObjectContext GetObjectContext(this IObjectContextAdapter source) => source.ObjectContext;

        public static ObjectStateManager GetObjectStateManager(this IObjectContextAdapter source) => source.ObjectContext.ObjectStateManager;
    }
}

Update: The above executes 2 db queries and the second contains duplicate TTarget records paired with TSourceKey. The difference with include is that it eliminates the TSource columns from the query.

Retrieving only the data needed w/o duplicates is possible, and requires executing 3 db queries:


public static partial class EF6Extensions
{
    public static void LoadLinks<TSource, TTarget, TSourceKey, TTargetKey>(this DbContext dbContext, IQueryable<TSource> sourceQuery, Expression<Func<TSource, ICollection<TTarget>>> collectionSelector, Expression<Func<TSource, TSourceKey>> sourceKeySelector, Expression<Func<TTarget, TTargetKey>> targetKeySelector)
        where TSource : class
        where TTarget : class
    {
        // Disable AutoDetectChanges for better performance
        bool autoDetectChanges = dbContext.Configuration.AutoDetectChangesEnabled;
        dbContext.Configuration.AutoDetectChangesEnabled = false;
        try
        {
            var sourceDbSet = dbContext.Set<TSource>();
            var targetDbSet = dbContext.Set<TTarget>();
            // Query to retrieve link keys from database
            var linksDbQuery = sourceQuery.SelectLinks(collectionSelector, sourceKeySelector, targetKeySelector);
            // Query to retrieve distinct target keys from database
            var targetKeysDbQuery = linksDbQuery.Select(e => e.Item2).Distinct();
            // Query to retrieve unique target entities
            var targetDbQuery = targetDbSet
                .Join(targetKeysDbQuery, targetKeySelector, key => key, (target, key) => target);
            // Execute the target entities query and build map by Id in memory
            var targetMap = targetDbQuery
                .ToDictionary(targetKeySelector.Compile());
            // Execute the links query and perform the fix-up 
            var stateManager = dbContext.GetObjectStateManager();
            var collectionPropertyName = ((MemberExpression)collectionSelector.Body).Member.Name;
            var sourceMap = new Dictionary<TSourceKey, TSource>();
            foreach (var link in linksDbQuery)
            {
                if (!sourceMap.TryGetValue(link.Item1, out var source))
                    sourceMap.Add(link.Item1, source = sourceDbSet.Find(link.Item1));
                var target = targetMap[link.Item2];
                stateManager.ChangeRelationshipState(source, target, collectionPropertyName, EntityState.Unchanged);
            }
        }
        finally { dbContext.Configuration.AutoDetectChangesEnabled = autoDetectChanges; }
    }

    static IQueryable<Tuple<TSourceKey, TTargetKey>> SelectLinks<TSource, TTarget, TSourceKey, TTargetKey>(this IQueryable<TSource> sourceQuery, Expression<Func<TSource, ICollection<TTarget>>> collectionSelector, Expression<Func<TSource, TSourceKey>> sourceKeySelector, Expression<Func<TTarget, TTargetKey>> targetKeySelector)
    {
        // sourceQuery.SelectMany(source => source.Collection, (source, target) => Tuple(source.Key, target.Key))
        var source = sourceKeySelector.Parameters[0];
        var target = targetKeySelector.Parameters[0];
        var resultType = typeof(Tuple<TSourceKey, TTargetKey>);
        var constructor = resultType.GetConstructor(new[] { typeof(TSourceKey), typeof(TTargetKey) });
        var args = new[] { sourceKeySelector.Body, targetKeySelector.Body };
        var members = new[] { resultType.GetProperty("Item1"), resultType.GetProperty("Item2") };
        var body = Expression.New(constructor, args, members);
        var selector = Expression.Lambda<Func<TSource, TTarget, Tuple<TSourceKey, TTargetKey>>>(
            body, source, target);
        return sourceQuery.SelectMany(collectionSelector.AsEnumerable(), selector);
    }
}

and the usage requires passing a selectors for both keys e.g.

context.LoadLinks(rolesQuery, role => role.Organizations, role => role.Id, organization => organization.Id));

Can't call DeleteObject in Entity framework - missing an assembly reference?

The documentation for the DbContext in EF4.1 seems to show that it doesn't include a delete method on that class: http://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext(v=vs.103).aspx...

This question looks similar - might be some help: MVC 3 EF 4.1 dbContext - Deleting one-to-many data object with non-nullable foreign-key relation

Deleting entites in nested collections

Unfortunately, it seems that with EF6 it is (mostly) impossible to have full persistence ignorance with entities when one wants to correctly handle so-called orphaned records.

How to remove child one to many related records in EF code first database?

So, what are the alternatives?

Bite the bullet and make entities persistence aware (minimally).

Obviously not what you want, but if we

remodel our entities in a more DDD-like way as larger operation/task-oriented aggregates instead of plain entities
and judiciously abstract persistence

it may not end up being as bad as it might look now

Add some clever custom orphan handling on SaveChanges(or etc.)

Something like https://stackoverflow.com/a/42048505/3745022

Or try to get the best out of the identifying relationships - for them orphan deletion works.

Do not delete entities

Unless there is a requirement (domain restriction, DB size/performance, GDPR...) to actually delete those entities, you can just mark them as deleted or add them to some DeletedParent.

Well, I understand that it may not be feasible and it will make some aspects of the system more complex and error-prone, but in many cases not deleting entities can be quite advantageous (ability to roll operations back, produce historical data and etc.)

Use EF Core (2.2?)

EF Core seems to support proper orphan removal.

And while you won't be able to use EF Core 3.0 with .NET Framework, you can still try EF Core 2.2, as it should support orphan removal as well.

Yes, there are some differences between EF and EF Core, but for a project seemingly at its early phases, they are not insurmountable.

Entity Framework .Remove() Vs. .Deleteobject()