Select IsNull in Linq

Question

How do I write this in linq:

SELECT
    c.customerNumber,
    IsNull(l.loanNumber, '') AS loanNumber,
    dd.documentTypeName,
    dd.documentSubTypeName,
    d.filename,
    (SELECT propertyId FROM SystemProperties WHERE propertyKey LIKE 'acculoan.inputType.Other') AS inputType
FROM
    document AS d INNER JOIN qryDocumentDefinitions AS dd
        ON d.documentDefId = dd.documentDefId
        AND d.documentId =  @DocumentId
    INNER JOIN customer AS c
        ON c.customerId = d.customerId
    LEFT OUTER JOIN loan AS l
        ON l.loanId=d.loanId

Help needed with the isnull especially and the nested select.

Many thanks in advance..!

Please use SO search. http://stackoverflow.com/questions/413084/equivalent-of-sql-isnull-in-linq?rq=1 — lmo, Aug 12 '14 at 14:44
I don't understand why you have the `inputType` as a sub-query. UN-necessary!! — huMpty duMpty, Aug 12 '14 at 15:09

score 0 · Accepted Answer · answered Aug 12 '14 at 15:06

You can try the below (have not been able to test, maybe will require some adjustments, but should be in the same form);

from Doc in db.document 
join DocDef in db.qryDocumentDefinitions on doc.documentDefId equals DocDef.documentDefId 
join Cus in db.Customer on Cus.customerId = Doc.customerId
from loan in db.loan 
     .Where(loan => loan.loanId == Doc.loanId).DefaultIfEmpty()
select new
    {
         CustNo= Cus.customerNumber
        ,loan == null ? "" : loan.loanNumber
        ,DocType= DocDef.documentTypeName
        ,DocSubType= DocDef.documentSubTypeName
        ,filename = Doc.filename
        ,inputType = (?int)(from Sys in db.SystemProperties 
                              where Sys.propertyKey.Contains("acculoan.inputType.Other")
    }

Select IsNull in Linq

1 Answers1