LINQ ISNULL

Question

In SQL you can run a ISNULL(null,'') how would you do this in a linq query?

I have a join in this 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
        };

but I have a column that has a bit type that is non nullable (xx.online) how can I set this to false if it is null?

Answer 1

Hi Mart

Looks like the type is boolean and therefore can never be null and should be false by default.

Answer 2

You can use the ?? operator to set the default value but first you must set the Nullable property to true in your dbml file in the required field (xx.Online)

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 ?? false
        };

Answer 3

Since aa is the set/object that might be null, can you check aa == null ?

(aa / xx might be interchangeable (a typo in the question); the original question talks about xx but only defines aa)

i.e.

select new {
    AssetID = x.AssetID,
    Status = aa == null ? (bool?)null : aa.Online; // a Nullable<bool>
}

or if you want the default to be false (not null):

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

---

Update; in response to the downvote, I've investigated more... the fact is, this is the right approach! Here's an example on Northwind:

        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);
            }
        }

And here's the TSQL - pretty much what we want (it isn't ISNULL, but it is close enough):

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]
-- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

QED?

Answer 4

how can I perform same in VB.NET??

Answer 5

I often have this problem with sequences (as opposed to discrete values). If I have a sequence of ints, and I want to SUM them, when the list is empty I'll receive the error "InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.".

I find I can solve this by casting the sequence to a nullable type. SUM and the other aggregate operators don't throw this error if a sequence of nullable types is empty.

So for example something like this

MySum = MyTable.Where(x => x.SomeCondtion).Sum(x => x.AnIntegerValue);

becomes

MySum = MyTable.Where(x => x.SomeCondtion).Sum(x => (int?) x.AnIntegerValue);

The second one will return 0 when no rows match the where clause. (the first one throws an exception when no rows match).