Using Contains with an Entity DbSet

When using Entity, the DbSet is generally the gateway to your data. The great thing about that is that you can run LINQ queries on it and they will be converted to SQL, allowing you to selectively acquire data without pulling over the entire set. One thing I've found, however, is that because it must eventually translate to SQL, not everything LINQ can do is supported.

I had a case where I needed a list of only items added to the database since my last query. I wanted to use the Contains method as a way to determine what I already had. Consider the following example Context and Item class

class Context : DbContext
{
    public DbSet<Item> Items { get; set; }
}

class Item
{
    public int Id { get; set; }
}

With this context and a list of previously queried items (previousItems), I thought the following would work

context.Items.Where(x => !previousItems.Contains(x));

However when this query is executed, it throws a NotSupportedException with the following message:

"Unable to create a constant value of type 'Item'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

So what's going on here? What's this business about creating a constant value? I didn't tell it to do that. Well it turns out that's exactly what I told it to do. It comes back to the point made earlier about Entity converting the LINQ to SQL. Remember that this query is actually run against a database, so my Item object has no meaning. Entity wants to convert it to something SQL will understand, but it cannot. That's the reasoning behind only primitives being supported. The query must use types that SQL knows. With this in mind, I'll work on the Item IDs rather than the item itself.

var itemIds = previousItems.Select(x => x.Id);

var newItems = context.Items.Where(x => !itemIds.Contains(x.Id));

I created a list of the IDs from Items that I already have, and use that to compare against the database IDs to determine if I already have the item. Now that I'm working with type Int32 rather than Item, it works! The query runs and I get the correct results.

So how does the Contains method convert to SQL? The IN operator. My LINQ query ends up looking more like this when run against the database

SELECT * FROM items
WHERE Id NOT IN ( ... )

Pretty cool and very useful!