Coalesce

2005
2008
2012
2014
2016
2017
2019
2022

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

Coalesce Expression

Use the Coalesce function to return the first non-null item from the list of expressions.

Coalesce is a curious function. Columns/expressions of different database types can be provided in the list, but depending upon what is provided, the function may fail. For example, given two fields where field1 is a uniqueidentifier and field2 is a varchar and both fields allow null. On execution of a SELECT (many) query using Coalesce(field1, field2), if the first record in the rowset has a non-null value for field1 and the second record in the rowset has a NULL value for field1 and a non-null value for field2, an error will occur. Why? - The first row causes the Coalesce function to conclude the return type should be uniqueidentifier. The second row returns a varchar, which is not convertible to a uniqueidentifier, so Microsoft SQL Server raises an error.

See the Microsoft docs for a more technically correct explanation and in-depth explanation.

Non-Generic Version

Syntax

db.fx.Coalesce({first_expression}, {second_expression}[, ...{n-expression}])

Arguments

first_expression: – The first value to seek a non-null value.

second_expression: – An expression or literal value, the second value to evaluate for null.

n-expression: – A list of expressions or literal values, if `first_expression` and `second_expression` are null, the first non-null value (or null) will be returned.

Returns

object?

Generic Version

Syntax

db.fx.Coalesce<T>({first_expression}, {second_expression}[, ...{n-expression}])

Arguments

T: – The resulting expression will be typed as `T`.

first_expression: – The first value to seek a non-null value.

second_expression: – An expression or literal value, the second value to evaluate for null.

n-expression: – A list of expressions or literal values, if `first_expression` and `second_expression` are null, the first non-null value (or null) will be returned.

Returns

If the first non-null value in the list is not convertible to `T`, an error will occur.

Examples

Select Statement

Select the first non-null date.

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

Select the first non-null date, indicating the return type should be DateTime.

IEnumerable<DateTime> result = db.SelectMany(
        db.fx.Coalesce<DateTime>(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate).As("latest_date")
    )
    .From(dbo.Purchase)
    .Execute();
    // dbo.Purchase.PurchaseDate does not allow nulls, so the last parameter will always return a non-null value
    // therefore, the return can be DateTime, not DateTime?

Try and select the first non-null item in a list of different data types. This query will fail with: 'The conversion of the varchar value overflowed an int column.'

object? result = db.SelectOne(
        db.fx.Coalesce(db.fx.Cast(dbo.Purchase.OrderNumber).AsBigInt(), dbo.Purchase.Id).As("relevant_identifier")
    )
    .From(dbo.Purchase)
    .Execute();
    // dbo.Purchase.OrderNumber is a varchar (string) while
    // dbo.Purchase.Id is an int

Where Clause

Select purchases where the last relevant date is over a week ago.

IEnumerable<Purchase> purchases = db.SelectMany<Purchase>()
    .From(dbo.Purchase)
    .Where(
        db.fx.Coalesce(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate) < 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.Coalesce(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate).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.Coalesce(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate).As("relevant_date")
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType,
        db.fx.Coalesce(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate)
    )
    .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.Coalesce(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate).As("relevant_date")
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType, 
        dbo.Purchase.ExpectedDeliveryDate, 
        dbo.Purchase.ShipDate, 
        dbo.Purchase.PurchaseDate
    )
    .Having(
        db.fx.Coalesce(dbo.Purchase.ExpectedDeliveryDate, dbo.Purchase.ShipDate, dbo.Purchase.PurchaseDate) < DateTime.UtcNow.Date.AddDays(-7)
    )
    .Execute();