IsNull

2005
2008
2012
2014
2016
2017
2019
2022

2005, 2008, 2012, 2014, 2016, 2017, 2019, 2022

IsNull Expression

Use the IsNull function to return the first non-null item from two expressions.

Syntax

db.fx.IsNull({first_expression}, {second_expression})

Arguments

first_expression: – The first value to evaluate for null.

second_expression: – The second value to evaluate for null.

Returns

A value that is the same type as `first_expression`.

Examples

Select Statement

Select the expected delivery date, else the ship date (both can be null, so the return type is DateTime?).

IEnumerable<DateTime?> result = db.SelectMany(
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate).As("latest_date")
    )
    .From(dbo.Purchase)
    .Execute();

Select the expected delivery date and if it is null return the current date.

IEnumerable<DateTime> result = db.SelectMany(
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, DateTime.UtcNow).As("latest_date")
    )
    .From(dbo.Purchase)
    .Execute();

Where Clause

Select purchases where relevant dates or over a week ago.

IEnumerable<Purchase> purchases = db.SelectMany<Purchase>()
    .From(dbo.Purchase)
    .Where(
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate) < DateTime.UtcNow.AddDays(-7)
    )
    .Execute();

Order By Clause

Select a list of purchases, ordered by a relevant date.

IEnumerable<Purchase> products = db.SelectMany<Purchase>()
    .From(dbo.Purchase)
    .OrderBy(db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate).Desc())
    .Execute();

Group By Clause

Select the cast of all product quantities, grouped by product category type and cast of product quantity.

IEnumerable<dynamic> values = db.SelectMany(
        dbo.Purchase.PaymentMethodType,
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate).As("relevant_date")
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType,
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate)
    )
    .Execute();

Having Clause

Select a list of payment methods and a 'relevant date(s)' where no activity has taken place for more than 7 days.

IEnumerable<dynamic> values = db.SelectMany(
        dbo.Purchase.PaymentMethodType,
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate).As("relevant_date")
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType, 
        dbo.Purchase.ExpectedDeliveryDate, 
        dbo.Purchase.ShipDate
    )
    .Having(
        db.fx.IsNull(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate) < DateTime.UtcNow.Date.AddDays(-7)
    )
    .Execute();