EXCEPT (Entity SQL)
Returns a collection of any distinct values from the query expression to the left of the EXCEPT operand that are not also returned from the query expression to the right of the EXCEPT operand. All expressions must be of the same type or of a common base or derived type as expression.
expression EXCEPT expression
Arguments
- expression
Any valid query expression that returns a collection to compare with the collection returned from another query expression.
Return Value
A collection of the same type or of a common base or derived type as expression.
Remarks
EXCEPT is one of the Entity SQL set operators. All Entity SQL set operators are evaluated from left to right. The following table shows the precedence of the Entity SQL set operators.
| Precedence | Operators |
|---|---|
Highest |
INTERSECT |
UNION UNION ALL |
|
EXCEPT |
|
Lowest |
EXISTS OVERLAPS FLATTEN SET |
Example
The following Entity SQL query uses the EXCEPT operator to return a collection of any distinct values from two query expressions. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:
Follow the procedure in How to: Execute a Query that Returns StructuralType Results (EntityClient).
Pass the following query as an argument to the
ExecuteStructuralTypeQuerymethod:
(SELECT product from AdventureWorksEntities.Product as product
where product.ListPrice > 20 ) except
(select product from AdventureWorksEntities.Product as product
where product.ListPrice > 50)
The output is shown below:
ProductID: 707
Name: Sport-100 Helmet, Red
ProductNumber: HL-U509-R
MakeFlag: False
ProductID: 708
Name: Sport-100 Helmet, Black
ProductNumber: HL-U509
MakeFlag: False
ProductID: 711
Name: Sport-100 Helmet, Blue
ProductNumber: HL-U509-B
MakeFlag: False
ProductID: 713
Name: Long-Sleeve Logo Jersey, S
ProductNumber: LJ-0192-S
MakeFlag: False
ProductID: 714
Name: Long-Sleeve Logo Jersey, M
ProductNumber: LJ-0192-M
MakeFlag: False
....