CODEWITHSUNDEEP
entity-frameworklinq
LeRamme
LeRamme

Reputation: 39

EF LINQ Get list of records by values not existing in another list

So, I have junction table called:

AppointmentsActivities

consisting of:

AppointmentID
ActivityID

I need to implement Update operation. Since it's a junction table the update should be able to not only to update existing records, but also to insert new records, or to delete one who no longer need to exists in the table(because i'm passing an entity with an appointmentID and list of ActivityID's).

I'm struggling to delete the records, that no longer should exist in the table. I have to delete every record which have the same AppointmentId, but his ActivityID should not be present in any of the objects from the new list of Activities.

The query I have written looks like this :

var remove = _context.AppointmentsActivities.
Where(i => i.AppointmentID == entity.ID && entity.Activities.Any(u => u.ActivityID != i.ActivityID)).
ToList();

Where:

i => i.AppointmentID == entity.ID

Checks if the appointmentID of the newly passed entity is the same as the one in the database table.

And:

entity.Activities.Any(u => u.ActivityID != i.ActivityID)

Is supposed to check if any of the activityID's in the list of Activities equals the activityID from the database table.

Obviosly,I'm missing something, because EF cannot resolve this LINQ query. What am I missing? Any help will be appreciated. Thank you.

Upvotes: 1

Views: 737

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

Try to rewrite your LINQ query to be acceptable by EF. Any with local collections will not work, so replace with Contains:

var activityIds = entity.Activities.Select(a => a.ActivityID).ToList();

var remove = _context.AppointmentsActivities
   .Where(i => i.AppointmentID == entity.ID && !activityIds.Contains(i.ActivityID))
   .ToList();

Upvotes: 2

Related Questions