IS [NOT] NULL (Entity SQL)

Determines if a query expression is null.

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 Boolean result of IS [NOT] 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:

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:

1. Follow the procedure in How to: Execute a Query that Returns StructuralType Results (EntityClient).

2. Pass the following query as an argument to the ExecuteStructuralTypeQuery method:

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

The output is shown below:

ProductID: 317
Name: LL Crankarm
ProductNumber: CA-5965
MakeFlag: False
ProductID: 318
Name: ML Crankarm
ProductNumber: CA-6738
MakeFlag: False
ProductID: 319
Name: HL Crankarm
ProductNumber: CA-7457
MakeFlag: False
ProductID: 320
Name: Chainring Bolts
ProductNumber: CB-2903
MakeFlag: False
ProductID: 321
Name: Chainring Nut
ProductNumber: CN-6137
MakeFlag: False
...

See Also

Concepts

Entity SQL Reference

Other Resources

Comparison Operators