How do I make an "Except" LINQ to Entities query?

Question

I have a many-to-many relationship between Accounts and PaymentSystems. I want to list all PaymentSystems not yet assigned to an account. To achieve that, I'm trying to use the following LINQ to Entities queries:

PaymentGatewayEntities pge = new PaymentGatewayEntities();
Account account = pge.Accounts.Single(item => item.id == accountId);
var paymentSystems = pge.PaymentSystems.Except(account.PaymentSystems);

However, I get the following exception when trying to display the results: "System.NotSupportedException: Unable to create a constant value of type 'MyNamespace.Models.PaymentSystem'. Only primitive types ('such as Int32, String, and Guid') are supported in this context." What am I doing wrong? I'm using EF4.

UPD: var paymentSystems = pge.PaymentSystems.Where(item => !item.Accounts.Contains(account)) results in the same exception as well.

Answer 1

Looks like I've found the solution:

var paymentSystems = pge.PaymentSystems.Where(
    item => !item.Accounts.Any(t => t.id == accountId));

seems to do the trick.