How to Handle NULL Values in LINQ: The Equivalent of SQL's ISNULL?

LINQ Equivalent of SQL's ISNULL

In SQL, the ISNULL function allows one to substitute a default value in place of a null value. This article demonstrates how to achieve a similar result using LINQ.

LINQ Query with a Nullable Column

Consider the following LINQ query:

var hht = from x in db.HandheldAssets
        join a in db.HandheldDevInfos on x.AssetID equals a.DevName into DevInfo
        from aa in DevInfo.DefaultIfEmpty()
        select new
        {
        AssetID = x.AssetID,
        Status = xx.Online
        };

In this query, aa represents the row from HandheldDevInfos that may or may not exist for a given asset. Additionally, the xx.Online column is a non-nullable bit type.

Setting Null Values to False

To set null values for xx.Online to false, we can use the null-coalescing operator (??). The updated query would look like this:

select new {
    AssetID = x.AssetID,
    Status = aa == null ? false : aa.Online;
}

In this revised query, if aa is null, the Status property will be set to false. Otherwise, it will be set to the value of aa.Online.

Example and Equivalent T-SQL

To illustrate this approach in a larger context, consider the following Northwind sample query:

        using(var ctx = new DataClasses1DataContext())
        {
            ctx.Log = Console.Out;
            var qry = from boss in ctx.Employees
                      join grunt in ctx.Employees
                          on boss.EmployeeID equals grunt.ReportsTo into tree
                      from tmp in tree.DefaultIfEmpty()
                      select new
                             {
                                 ID = boss.EmployeeID,
                                 Name = tmp == null ? "" : tmp.FirstName
                        };
            foreach(var row in qry)
            {
                Console.WriteLine("{0}: {1}", row.ID, row.Name);
            }
        }

The equivalent T-SQL query, which uses a similar approach, is shown below:

SELECT [t0].[EmployeeID] AS [ID],
    (CASE
        WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(10),@p0)
        ELSE [t2].[FirstName]
     END) AS [Name]
FROM [dbo].[Employees] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[FirstName], [t1].[ReportsTo]
    FROM [dbo].[Employees] AS [t1]
    ) AS [t2] ON ([t0].[EmployeeID]) = [t2].[ReportsTo]

By utilizing the null-coalescing operator, LINQ queries can handle null values elegantly, providing a convenient way to substitute default values or perform other operations based on the presence or absence of data.

The above is the detailed content of How to Handle NULL Values in LINQ: The Equivalent of SQL's ISNULL?. For more information, please follow other related articles on the PHP Chinese website!