LINQ query Where ID does not exist as a property in a list of objects

user_5819046 0
Viewed 4287

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

An 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 ItemID in allItems:

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?

entity-framework-6 entity-framework sql linq c#
1 Answers

I will rewrite the question

I have a list (someItems) of objects with numeric IDs. I want to query a table PurchasedItems in my database to give me all the rows where the FK_ItemID do not match the ID of any of the objects in my list someItems.

My answer:

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 someItemIDs first.

user_109313880
Related