Translating TSQL query to LINQ

Question

I have created a SQL statement that gathers unprocessed files from the database. It uses the DicomDataType_LK table to limit results to those I am interested in. When I run it, I get I get two results.

SELECT * FROM [Procedure].DicomData dicoms
JOIN [Procedure].DicomDataType_LK dtype
    ON dicoms.DicomDataTypeID = dtype.DicomDataTypeID
WHERE dicoms.ProcedureID = '00000000-0000-0000-0000-000000000000'
    AND dicoms.CreateDate > '3/27/2017'
    AND (dtype.DicomDataType LIKE 'V Density'
        OR dtype.DicomDataType LIKE 'Q Density'
        OR dtype.DicomDataType LIKE 'i Density'
        OR dtype.DicomDataType LIKE 'u Density')

I have translated this into LINQ to SQL, but with the same connection I am getting zero results.

            IQueryable<DicomData> rows = from dicoms in db.DicomDatas
               join dtype in db.DicomDataType_LKs
                  on dicoms.DicomDataTypeID equals dtype.DicomDataTypeID
               where dicoms.ProcedureID == Guid.Empty
                  && dicoms.CreateDate > fromTime
                  && (dtype.DicomDataType.Equals("V Density")
                  || dtype.DicomDataType.Equals("Q Density")
                  || dtype.DicomDataType.Equals("i Density")
                  || dtype.DicomDataType.Equals("u Density"))
               select dicoms;

I have confirmed that fromTime gives a value that should include my two results. Could someone please let me know what I am doing wrong in my translation?

`AND dicoms.CreateDate >= '3/27/2017'` is not the same as `&& dicoms.CreateDate > fromTime`... I.e. `>=` is not the same as `>`... — Sani Huttunen, Apr 05 '17 at 23:45
There are other questions around that indicate that `Guid.Empty` is not the same as (for example) `new Guid("00000000-0000-0000-0000-000000000000")` in LINQ. See [this question](http://stackoverflow.com/questions/358345/hanging-linq-query-with-guid-empty-in-the-where-expression) for an example. So try converting to the new Guid syntax above, and see if it works. I suspect that all zeroes is a valid GUID, but an empty GUID is not. — Laughing Vergil, Apr 05 '17 at 23:48
@SaniSinghHuttunen - Thank you for the correction. The statement that made in SQL was just for testing to see if the LINQ query was correct. The CreateDate is from one week ago, but the items being processed are from today, so either should work. — Tim, Apr 05 '17 at 23:49
@SaniSinghHuttunen - I was wrong. The date that was being used was not from today, but from when the DICOM was created. If you make your comment an answer, I will select it as correct. — Tim, Apr 05 '17 at 23:55

score 1 · Accepted Answer · answered Apr 05 '17 at 23:57

1

From what I can see:

AND dicoms.CreateDate >= '3/27/2017' is not the same as && dicoms.CreateDate > fromTime

I.e. >= is not the same as >

answered Apr 05 '17 at 23:57

Sani Huttunen

23,620
6
72
79

Translating TSQL query to LINQ

1 Answers1