Comparison Semantics
Performing any of the following Entity SQL operators involves comparison of type instances:
Explicit comparison
Equality operations:
=
!=
Ordering operations:
<
<=
>
>=
Nullability operations:
IS NULL
IS NOT NULL
Explicit distinction
Equality distinction:
DISTINCT
GROUP BY
Ordering distinction:
- ORDER BY
Implicit distinction
Set operations and predicates (equality):
UNION
INTERSECT
EXCEPT
SET
OVERLAPS
Item predicates (equality):
- IN
Supported Combinations
The following table shows all the supported combinations of comparison operators for each kind of type:
Type |
= != |
GROUP BY DISTINCT |
UNION INTERSECT EXCEPT SET OVERLAPS |
IN |
< <= > >= |
ORDER BY |
IS NULL IS NOT NULL |
Entity type |
Ref1 |
All properties2 |
All properties2 |
All properties2 |
Throw3 |
Throw3 |
Ref1 |
Complex type |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Row |
All properties4 |
All properties4 |
All properties4 |
Throw3 |
Throw3 |
All properties4 |
Throw3 |
Primitive type |
Provider-specific |
Provider-specific |
Provider-specific |
Provider-specific |
Provider-specific |
Provider-specific |
Provider-specific |
Multiset |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Throw3 |
Ref |
Yes5 |
Yes5 |
Yes5 |
Yes5 |
Throw |
Throw |
Yes5 |
Association type |
Throw3 |
Throw |
Throw |
Throw |
Throw3 |
Throw3 |
Throw3 |
1The references of the given entity type instances are implicitly compared, as shown in the following example:
SELECT p1, p2
FROM AdventureWorksEntities.Product AS p1
JOIN AdventureWorksEntities.Product AS p2
WHERE p1 != p2 OR p1 IS NULL
An entity instance cannot be compared to an explicit reference. If this is attempted, an exception is thrown. For example, the following query will throw an exception:
SELECT p1, p2
FROM AdventureWorksEntities.Product AS p1
JOIN AdventureWorksEntities.Product AS p2
WHERE p1 != REF(p2)
2Properties of complex types are flattened out before being sent to the store, so they become comparable (as long as all their properties are comparable). Also see 4.
3The Entity Framework runtime detects the unsupported case and throws a meaningful exception without engaging the provider/store.
4An attempt is made to compare all properties. If there is a property that is of a non-comparable type, such as text, ntext, or image, a server exception might be thrown.
5All individual elements of the references are compared (this includes the entity set name and all the key properties of the entity type).