ISNULL (Entity SQL)

Artikkeli
09/15/2021

Determines if a query expression is null.

Syntax

expression IS [ NOT ] NULL

Arguments

expression Any valid query expression. Cannot be a collection, have collection members, or a record type with collection type properties.

NOT Negates the EDM.Boolean result of IS NULL.

Return Value

true if expression returns null; otherwise, false.

Remarks

Use IS NULL to determine if the element of an outer join is null:

select c
      from LOB.Customers as c left outer join LOB.Orders as o
                              on c.ID = o.CustomerID
      where o is not null and o.OrderQuantity = @x

Use IS NULL to determine if a member has an actual value:

select c from LOB.Customer as c where c.DOB is not null

The following table shows the behavior of IS NULL over some patterns. All exceptions are thrown from the client side before the provider gets invoked:

Pattern	Behavior
null IS NULL	Returns `true`.
TREAT (null AS EntityType) IS NULL	Returns `true`.
TREAT (null AS ComplexType) IS NULL	Throws an error.
TREAT (null AS RowType) IS NULL	Throws an error.
EntityType IS NULL	Returns `true` or `false`.
ComplexType IS NULL	Throws an error.
RowType IS NULL	Throws an error.

Example

The following Entity SQL query uses the IS NOT NULL operator to determine if a query expression is not null. 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.
Pass the following query as an argument to the ExecuteStructuralTypeQuery method:

SELECT VALUE product FROM AdventureWorksEntities.Products 
    AS product WHERE product.Color IS NOT NULL

Jaa