LINQ InvalidOperationException when calling ToList()-CodePudding

I want to update records in a table in my database using the code below:

context.Events
        .Where(eventDb => !events.Any(@event => eventDb.Guid == @event.Guid))
        .ToList()
        .ForEach(eventDb => eventDb.IsCurrent = false);

But every time on calling ToList() I'm getting the following error, which isn't clear to me:

System.InvalidOperationException: The LINQ expression 'event => EntityShaperExpression: 
    WawAPI.Models.Event
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Guid == event.Guid' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

I'm not sure what I'm doing wrong and I'd appreciate any help.

CodePudding user response：

I think the issue is that EventDb.Guid == @event.Guid can't be converted to the database raw SQL. What type are the Guid's? See what happens if you move the ToList() to before the Where clause.

CodePudding user response：

I think the problem is that you are asking the SQL server to perform a .Any() operation on events which is presumably some sort of in-memory C# object and does not exist on the SQL server and is too complicated to be sent to SQL.

If you extract the Guids from events into a simple list of strings, then that could be simple enough to send to sql. You also need to change your linq query slightly, but I think this will give you the results you are looking for.

var eventGuids = events.select(a=>a.Guid).ToList();

context.Events
        .Where(eventDb => !eventGuids.Contains(event.Guid))
        .ToList()
        .ForEach(eventDb => eventDb.IsCurrent = false);