EF - Unable to create a constant value of type Only primitive types or enumeration types are supported in this context

Question

I have table named PickupLocations in my db that has columns externalID and Status. I get from an api a list of LockerIDs.

What I would like to do is to change the status of the rows who are not received from the api to 0.

E.g:

In this scenario, only "456" wasn't received from the api therefore it status should be updated to 0.

DataBase:PickupLocations       API
_________________           LockerID
|ExternalID|  Status|       |"789"|
--------------------|       |"AA1"|
|     "123" |  1    |       |"123"|
|     "456" |  1    |       |"665"|
|     "789" |  1    |       |"555"|

If this was SQL I would do something like this:

Update PickupLocations set status = 0 
where ExternalID in
(
 select pl.ExternalID 
 from pickuplocations pl left join LockerIDs l on pl.ExternalID = l.LockerID 
 where l.LockerID is null
)

I am trying to achieve this SQL update in code, but with no success:

List<LockerList> activeLockers = results.LockerList;
using (var dbc = new MyDBContext())
{
    //First attemp
    dbc.PickupLocations.Where(pl => !activeLockers.Select(x => x.LockerId).Any(l => l.Contains(pl.ExternalID))).ToList().ForEach(x => x.Status = 0);
    //Second attemp
    dbc.PickupLocations.Where(pl => !activeLockers.Any(l => l.LockerId == pl.ExternalID))
                                   .ToList()
                                   .ForEach(x => x.Status = 0);
    dbc.SaveChanges();
}

Error for both attempts:

Unable to create a constant value of type 'BoxitObjects.LockerList'. Only primitive types or enumeration types are supported in this context.

On the first one you're trying to compare a list of `LockerId`s to one `ExternalID`, which doesn't make sense (hence one of the errors). On the second one your `l` lambda variable is one `LockerId` item, not a list, since you're running `.Any()` on it (unless `LockerId` itself is a list, in which case you want `SelectMany()` to flatten the list). So try changing it to something like `dbc.PickupLocations.Where(pl => !activeLockers.Select(x => x.LockerId).Any(l => l == ExternalID)).ToList().ForEach(x=>x.Status=0);` (I'm just guessing at this point since you haven't provided actual errors) — Arian Motamedi, Feb 13 '18 at 17:46
beside all the other stuff that is already said. you cannot return anything in the `ForEach` because it takes an `Action` as parameter which is **void** — Mong Zhu, Feb 13 '18 at 17:47
What is your motivation behind using `List.ForEach` rather than the `foreach` keyword? Most people find `foreach` more readable. Splitting up your 141 character line may be beneficial. — Sinjai, Feb 13 '18 at 18:03

GBreen12 · Answer 1 · 2018-02-13T19:27:08.153

2

Try this:

List<int> activeLockers = results.LockerList.Select(al => al.LockerId).ToList();
using (var dbc = new MyDBContext())
{
    dbc.PickupLocations.Where(pl => !activeLockers.Contains(pl.ExternalID)).ToList().ForEach(x => x.Status=0);

    dbc.SaveChanges();
}

This should work but it depends on what your errors are. Try this and post the error if you get one.

edited Feb 13 '18 at 19:27

answered Feb 13 '18 at 17:47

GBreen12

1,832
2
20
38

With your answer I got this error: `Additional information: Unable to create a constant value of type 'BoxitObjects.LockerList'. Only primitive types or enumeration types are supported in this context.` Only after I added a variable that holds the list for Locker IDs it worked properly. – Offir Feb 14 '18 at 15:34
My solution does create a list of locker ids. See line 1. It just does it all in one step. Did you do something different than that? – GBreen12 Feb 14 '18 at 19:31
When I replace the variable `activeLockersIDs` with it's value `activeLockers.Select(al => al.LockerId).ToList()` in the lambda expression I get the error, but when I save it as a variable it works.. I read somewhere it's a memory related issue.. – Offir Feb 15 '18 at 08:48

EF - Unable to create a constant value of type Only primitive types or enumeration types are supported in this context

1 Answers1