I have a list of objects that contain ID numbers. I want to query a table in my database to give me all the rows where the ID do not match the ID of any of the objects in my list.
List<Item> allItems = new List<Item>(); // Populate allItems
Item is a simple object with a
public int ItemID and
public string Name.
Here is the LINQ I have tried to query a particular table to give me only the rows that do not exist as an
var filtered = ctx.PurchasedItems.Where(x => allItems.Select(y => y.ItemID).ToList().Contains(x.FK_ItemID)).ToList();
As you can see,
dbo.PurchasedItems has a column
FK_ItemID that matches up to the
ItemID in the object Item.
Although this compiles, I get the following error when I run:
"Unable to create a constant value of type 'DemoProject.Models.ItemDatabase.PurchasedItems'. Only primitive types or enumeration types are supported in this context."
Another method I tried was:
var p = ctx.PurchasedItems.Where(x => x.FK_ItemID.IsAnyOf(allItems.Select(y => y.ItemID).ToArray())).ToList();
...but this produced a similar error.
Can anyone help me out?
I will rewrite the question
I have a list (
someItems) of objects with numeric IDs. I want to query a table
PurchasedItemsin my database to give me all the rows where the
FK_ItemIDdo not match the ID of any of the objects in my list
List<Item> someItems = new List<Item>(); // Populate someItems List<int> someItemIDs = someItems.Select(i=>i.ID).ToList(); var filtered = ctx.PurchasedItems .Where(x => !someItemIDs.Contains(x.FK_ItemID)) .ToList();
As pointed out by @juharr 's comment, EF cannot deal with a collection of non primitive types because it's trying to translate that into SQL. You can create the collection