LINQ nullable Bool?

Epicor ERP 10
1

I have an SQL query that I’d like to convert to a LINQ expression but have an issue with a nullable bool.

SQL

SELECT j.Company, j.JobNum, j.AssemblySeq, j.PartNum, j.Description, j.IUM
		, j.RequiredQty, j.IssuedComplete, j.RelatedOperation, j.IssuedQty
		, j.WarehouseCode, j.MtlSeq, p.QtyBearing
		, (j.RequiredQty - j.IssuedQty) AS Shortage
FROM Erp.JobMtl as j 
	Left OUTER JOIN Erp.PartPlant p 
		ON j.Company = p.Company AND j.PartNum = p.PartNum
WHERE j.Company='HLI' and j.JobNum='TEST2' and j.AssemblySeq= 0 
	and (p.QtyBearing = 1 or p.QtyBearing IS NULL) 
	and (j.IssuedComplete = 0)

LINQ

var OutStandMtl = (
from jm in Db.JobMtl
	join pp in Db.PartPlant
		on new { jm.Company, jm.PartNum } equals new { pp.Company, pp.PartNum }
where jm.Company == "HLI" && jm.JobNum == "TEST2" && jm.AssemblySeq == 0 
	&& (pp.QtyBearing == true || pp.QtyBearing == null)  
	&& jm.IssuedComplete == false
select new { jm.Company, jm.JobNum, jm.AssemblySeq, jm.PartNum, jm.Description
	, jm.IUM, jm.RequiredQty, jm.IssuedComplete, jm.RelatedOperation
	, jm.IssuedQty, jm.WarehouseCode, jm.MtlSeq
	, Shortage = (jm.RequiredQty - jm.IssuedQty) });

Error

CS0472: The result of the expression is always ‘false’ since the value of type ‘bool’ is never equal to ‘null’ of type ‘bool’

It’s doesn’t like:
pp.QtyBearing == null

image
image1343×930 61.7 KB

2

This query gives me a list of all outstanding mtl on a job, except parts on the fly because they don’t have PartPlant records… In SQL I can use pp.QtyBearing IS NULL, which gives me the correct results. However, in C# LINQ it seems to knows that pp.QtyBearing is a non-nullable bool, which is why I get that message.

Any other way to tackle this?

3

Example LINQ segment code: of a INNER and LEFT join. When using the left join you will need to utilize the DefaultIfEmpty().

join s   in Db.QuoteHed.With(LockHint.NoLock) on new {c.Company, c.HDCaseNum}   equals new {s.Company, s.HDCaseNum} into jointData_s from jointRecord_s in jointData_s.DefaultIfEmpty()

join sd  in Db.QuoteDtl.With(LockHint.NoLock) on new {jointRecord_s.Company, jointRecord_s.QuoteNum}  equals new  {sd.Company, sd.QuoteNum} 

where c.Company == TaskListVar.Company && c.HDCaseNum == CaseNumber && sd.QuoteLine == 1 
               select new 
               {
                 HDCaseNum = c.HDCaseNum, 
                 QuoteNum  = jointRecord_s.QuoteNum,
                 SchedCode = jointRecord_s.rsiSchedCode_c,
                 Quoted    = jointRecord_s.Quoted,
                 OraSONum  = jointRecord_s.rsiOracle_SO_No_c,
                 XPartNum  = sd.XPartNum,

		 //JobTypeID = s.rsiJobType_c
                 JobTypeDesc = jointRecord_jt.CodeDesc,





               }).FirstOrDefault();

`
2 Likes
4

Thanks @asmar, that worked! Wish I understood LINQ a little more.

5

It took me many moons to discover the secret of how to concurrently use LINQ left and inner join.

2 Likes
6

Very nice, I recently ran into a similar issue as Norman where POTF bit me because the part is --of course-- not in the Part table. It was a Fill Table by Query BPM node, I was using the expression for a UD checkbox field to check the Part table and set the checkbox accordingly. So it’d need to be a single expression statement. Ended up making a C# node for it to check for the null.

7

in case it’s helpful here is the code I ended up with

foreach (var mtlPart in (
	from jm in Db.JobMtl.With(LockHint.NoLock)
		join pp in Db.PartPlant.With(LockHint.NoLock)
			on new {jm.Company, jm.PartNum} equals new {pp.Company, pp.PartNum}
			into jmj
			from pp in jmj.DefaultIfEmpty()
	where jm.Company == sCompany 
		&& jm.JobNum == sJobNum 
		&& jm.AssemblySeq == iAsmSeq 
		&& (pp.QtyBearing == true || pp.QtyBearing == null)  
		&& jm.IssuedComplete == false 
		&& jm.BackFlush == false
	select new { 
		jm.Company, jm.JobNum, jm.AssemblySeq, jm.PartNum, jm.Description
		, jm.IUM, jm.RequiredQty, jm.IssuedComplete, jm.RelatedOperation
		, jm.IssuedQty, jm.WarehouseCode, jm.MtlSeq
		, Shortage = (jm.RequiredQty - jm.IssuedQty) }))
{
	myDetail = myDetail + Environment.NewLine = string.Format
		(layoutFormat, mtlPart.JobNum, mtlPart.AssemblySeq, mtlPart.PartNum, 
		mtlPart.RelatedOperation, mtlPart.MtlSeq, mtlPart.Shortage);
	iRecordCount += 1;
}
	if (iRecordCount > 0) {
		PublishInfoMessage(myHeader + myDetail, 0,0,"","");	
		bFoundMaterials = true;
	}
1 Like
8

Note that you CAN make a nullable bool variable by putting the ? following the bool declaration. (I just got done doing this for a DateTime? value)…

bool? mynullableBool = null;

you may also be able to use this in Linq as well by casting the value in your condition like this:

	&& (pp.QtyBearing == true || (bool?)pp.QtyBearing == null)
9

@timshuwy, I tried casting the (bool?) but it didn’t solve my issue. It was the Left Join and DefaultIfEmpty() that ended up working.

10

fantabulous

11

could be that if you swapped the condition’s order around it might work… checking for the null first, becuase if you check for the “true” first, but it is null, it may give you an error, but checking for the null first, it wont try the true condition UNLESS it is not null.

	&& ((bool?)pp.QtyBearing == null) || pp.QtyBearing == true)