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?
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?
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).
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).
Looks like the type is boolean and therefore can never be null and should be false by default.
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
};
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
};
Looks like the type is boolean and therefore can never be null and should be false by default.
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
};
Source: Stackoverflow.com