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.

Debug the code by starting with one join and then add the other two joins to find out where the code is going wrong. — jdweng, Jun 19 '16 at 13:08
During debugging, Whole Linq Query executed once. what should i do? — Ahmad, Jun 19 '16 at 13:11
What is the **type** of (1) `checklist.LongKey` (2) `codeInspectionType.InspectionTypeID`? — Ivan Stoev, Jun 19 '16 at 13:13
`Cast(LEFT(Checklist.LongKey, 6) as int) = CodeInspectionTypeV.InspectionTypeID` is not same as `checklist.LongKey.Substring(0, 6) equals codeInspectionType.InspectionTypeID.ToString()` — Hamid Pourjam, Jun 19 '16 at 13:14
@dotctor I was thinking in the same direction. But except if `LongKey` (obviously string) contains leading zeroes, shouldn't they be equivalent? — Ivan Stoev, Jun 19 '16 at 13:18
How can i equate these Cast(LEFT(Checklist.LongKey, 6) as int) = CodeInspectionTypeV.InspectionTypeID with LINQ. any idea? as i search a lot but nothing find seriously helpful — Ahmad, Jun 19 '16 at 13:21
@IvanStoev. Type of checklist.LongKey is string while codeInspectionType.InspectionTypeID is int — Ahmad, Jun 19 '16 at 13:24
Unfortunately there is no function for converting string to int. Can you show sample `LongKey` left 6 characters? Are there leading zeroes? Non numeric characters? — Ivan Stoev, Jun 19 '16 at 13:27
actually the type of LongKey is varchar(255) which contains the vary long ID i need only first 6. taht's why i am converting it into int. — Ahmad, Jun 19 '16 at 13:30
When i try to use this Int32.parse( LongKey.Substring(0, 6) ) equals codeInspectionType.InspectionTypeID. It throws exception. LINQ cannot recognize Int32.Parse ot convert.int etc functions. — Ahmad, Jun 19 '16 at 13:35
@ivan stoev Yes aftre removing this join query working fine, it means we have issue here — Ahmad, Jun 19 '16 at 13:40
Ok, so now when the query returns data, can you show at least one value of the returned result InspectionTypeID property? — Ivan Stoev, Jun 19 '16 at 13:46
data is returning when i comment out the whole line of join db.codeinspectiontypevs. that mean there is no any InspectionTypeID Property/ — Ahmad, Jun 19 '16 at 13:52
Omg, then comment it out again, take one value and paste it here. There must be a reason the join condition to fail and I need to see it, in order to search for a solution. — Ivan Stoev, Jun 19 '16 at 14:06
Okay you can see { "iChecklistID": 4249, "InspectionTypeID": "019216" } — Ahmad, Jun 19 '16 at 14:50

score 3 · Accepted Answer · edited May 23 '17 at 11:58

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)

score 0 · Answer 2 · answered Jun 20 '16 at 06:59

Try to add "into [an alias name]" at the end of join line. After that add a from line using that alias name after that use that alias name in the where line

from checklist in db.Checklists  
join code in db.Codes on checklist.iCodeID equals code.iCodeID into Temp1
from t1 in Temp1  
where t1.bDeleted == false && t1.bObsolete == false

Linq query is not working while sql is working properly

2 Answers2