Linq query is not working while sql is working properly

Question

I am converting some SQL queries to Linq (Entity Framework). Most of queries are working fine, but I am facing little problem with the following one.

When I try this query in SQL Server Management Studio, it returns multiple records.

SELECT
    bDrillDown,
    Icon
FROM 
    dbo.Checklist 
INNER JOIN 
    dbo.Codes ON Checklist.iCodeID = Codes.iCodeID 
              AND Codes.bDeleted = 0 AND Codes.bObsolete = 0
INNER JOIN 
    dbo.CodeGroup ON Codes.iGroupID = CodeGroup.iGroupID 
                  AND CodeGroup.bDeleted = 0 AND CodeGroup.bInspection = 1
INNER JOIN 
    dbo.CodeInspectionTypeV ON Cast(LEFT(Checklist.LongKey, 6) as int) = CodeInspectionTypeV.InspectionTypeID
WHERE 
    Checklist.bDeleted = 0
ORDER BY 
    iChecklistID

When I convert it into LINQ query like:

var checkList = from checklist in db.Checklists
                join code in db.Codes on checklist.iCodeID equals code.iCodeID
                where code.bDeleted == false && code.bObsolete == false
                join codeGroup in db.CodeGroups on code.iGroupID equals codeGroup.iGroupID
                where codeGroup.bDeleted == false && codeGroup.bInspection == true
                join codeInspectionType in db.CodeInspectionTypeVs on checklist.LongKey.Substring(0, 6) equals codeInspectionType.InspectionTypeID.ToString()
                where checklist.bDeleted == false
                orderby checklist.iChecklistID
                select new
                        {
                            checklist.iChecklistID,
                            InspectionTypeID = checklist.LongKey.Substring(0, 6).ToString()
                        };

It does not return any records, only an empty array.

Answer 1

The problem is apparently in the following join condition

on checklist.LongKey.Substring(0, 6) equals
   codeInspectionType.InspectionTypeID.ToString()

which is not equivalent to the SQL query one.

Unfortunately EF does not support string to numeric data conversions, so your attempt is good, but doesn't work when the string value contains leading zeroes as in your case.

To make it work, you need to left pad with zeroes the result of the codeInspectionType.InspectionTypeID.ToString(), which can be done (at least in the latest EF6.1.3) by using the DbFunctions.Right canonical function (similar to how to sort varchar column containing numeric values with linq lambdas to Entity):

on checklist.LongKey.Substring(0, 6) equals 
   DbFunctions.Right("00000" + codeInspectionType.InspectionTypeID, 6)