Entity Framework Reference constraint error on delete when using selectmany

Question

Edit: Please read carefully, I have and issue Loading data for without cartesian production. Deleting data works fine if the references are loaded properly.

I'm trying to delete a large group of entities, However due to a many-to-many assoication OwnerRoles whenever I try to delete them I receive an error:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_OwnerRoles_aspnet_Roles_RoleId"

When I try to avoid cartesian production by loading the aspnet_roles using a select many,

var rolesQuery = context.Organizations
       .Where(x => x.OrganizationId == organizationId)
       .SelectMany(x => x.aspnet_Roles);

var roles = rolesQuery.ToArray();
rolesQuery.SelectMany(x => x.Permissions).Load();
rolesQuery.SelectMany(x => x.Organizations).Load();

The assoicated OwnerRoles is not loaded, so when I attempt to remove all of the references:

roles.ForEach(r => r.Organizations.ToArray().ForEach(o => r.Organizations.Remove(o)));

context.Permissions.RemoveRange(roles.SelectMany(x => x.Permissions));
context.aspnet_Roles.RemoveRange(roles);

context.SaveChanges();

There is nothing loaded to remove so, I get my referencial constraint when deleting.

This is my db structure

Organizations: * => * aspnet_Roles (Many To Many connected by intermediate table **OwnerRoles**)
aspnet_Roles: 1 => * permissions (aspnet_Roles has many permissions)

Notes:

1. I cannot use CascadeOnDelete
2. I'm using and Edmx with EntityFramework v6.2
3. I'd prefer to not use save changes multiple times.

Everything works fine if I use includes instead of SelectMany, Because include loads the tables connecting However I would like to make seperate queries to avoid Cartesian production, so the result set sent back over the wire isn't so large.

How can I Load my data properly to avoid caretesian production, while still being able to delete many to many collections?

I'm looking for a way to explicitly load the references of a collection table (e.g there is no Poco class or DB set for that entity) OR I'm looking for a way to explicitly delete from EntityFramework (with out calling a stored procedure because that will circumvent the audit log)

Answer 1

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));