Entity Framework Core 3.1 Nested Collections Remove Objects Linq Expression Fails

Question

My question is smiler to this, where I want to remove items from the nested collection. Entity Framework Core, deleting items from nested collection can you please help me find out what I'm doing wrong? thank you.

My DB update function looks like this:

public async Task<Step> Update(short id, Step entity)
        {
            entity.Id = id;
            var context = _formulaDBContext;
            var missingNodes = context.Nodes.Where(i => i.StepId == entity.Id).Except(entity.Nodes);
            context.Nodes.RemoveRange(missingNodes);
            var t = Task.Run(() => context.Set<Step>().Update(entity));
            t.Wait();
            await context.SaveChangesAsync();
            return entity;
        }

I'm doing the same as the accepted answer but it fails with below exception:

    System.AggregateException
  HResult=0x80131500
  Message=One or more errors occurred. (Processing of the LINQ expression 'DbSet<Node>
    .Where(i => (int)i.StepId == (int)__entity_Id_0)
    .Except(__p_1)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.)
  Source=System.Private.CoreLib
  StackTrace:
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.Wait()
   at Formula.UI.RecepiDataViewModel.<EditStep>d__8.MoveNext() in C:\Users\gje\source\repos\formula\Formula.UI\ViewModels\RecepiMaker\RecepiDataViewModel.cs:line 62
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Formula.UI.RecepiDataViewModel.<<-ctor>b__7_1>d.MoveNext() in C:\Users\gje\source\repos\formula\Formula.UI\ViewModels\RecepiMaker\RecepiDataViewModel.cs:line 46

  This exception was originally thrown at this call stack:
    [External Code]
    Formula.UI.StepDataService.Update(short, Formula.Models.Step) in StepDataService.cs

Inner Exception 1:
InvalidOperationException: Processing of the LINQ expression 'DbSet<Node>
    .Where(i => (int)i.StepId == (int)__entity_Id_0)
    .Except(__p_1)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

Here is my models:

 public class Step : BaseModel
        {
            public Step()
            {
                Nodes = new List<Node>();

            }
            public short RecepiId { get; set; }

            public short PVTagId { get; set; }
            public Operator Operator { get; set; }

            public TagMetaData PVTag { get; set; }
            public string Value { get; set; }
            public Activity Activity { get; set; }
            [ForeignKey("StepId")]
            public List<Node> Nodes { get; set; }

        }

        public class Step : BaseModel
        {
            public Step()
            {
                Nodes = new List<Node>();

            }
            public short RecepiId { get; set; }

            public short PVTagId { get; set; }
            public Operator Operator { get; set; }

            public TagMetaData PVTag { get; set; }
            public string Value { get; set; }
            public Activity Activity { get; set; }
            [ForeignKey("StepId")]
            public List<Node> Nodes { get; set; }

        }

        public class BaseModel
        {
            public short Id { get; set; }
            public string Name { get; set; }
        }

Guru Stron · Accepted Answer · 2020-06-26T13:00:06.020

Try changing your missingNodes query to something like this:

var missingNodes = context.Nodes
    .Where(i => i.StepId == entity.Id)
    .Except(context.Nodes.Where(n => entity.Nodes.Select(ei => ei.Id).Contains(n.Id)));

or:

var missingNodes = context.Nodes
        .Where(i => i.StepId == entity.Id && !entity.Nodes.Select(ei => ei.Id).Contains(i.Id));

In short the issue is that EF can't pass local collection of complex types to query during translation into SQL and execution (at least in some cases).

Entity Framework Core 3.1 Nested Collections Remove Objects Linq Expression Fails

1 Answers1