How do I make an "Except" LINQ to Entities query?
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开发者_JAVA百科.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.
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.
A slightly different variant of the same answer:
var paymentSystems = pge.PaymentSystems.Where(
item => item.Accounts.All(t => t.accountId != t.ID));
精彩评论