none
Trying to convert/translate a Linq Query to a T Sql Query RRS feed

  • Question

  • I am trying to convert/translate the following Linq query to T Sql

    var apps = db.vDeptAppDtls.Where(a => !string.IsNullOrEmpty(a.DeptHL) && (a.AppCI.DispositionID != null 
                  && a.AppCI.AppDisposition.Disposition != "Retire") 
                  && !(a.AppCI.Deleted.HasValue && a.AppCI.Deleted.Value))
                  .Select(s => new { Dept = s.DeptHL, AppCIID = s.AppCIID }).Distinct().ToList();

    --this Linq Query returns a record/row count of 2763

    The following is my attempt to convert/translate the Linq Query above to a T Sql Query

    select distinct t1.depthl, t1.AppCIID  from (vDeptAppDtl t1 join AppCI t2 on t1.AppCIID = t2.AppCIID) 
               join  AppDisposition t3 on t2.DispositionID = t3.AppDispositionID
    where t1.DeptHL is not null and t2.DispositionID is not null and t3.Disposition != 'Retire'
          and t2.Deleted is not null

    This T Sql query returns 2769 rows from the exact same data tables.  I tried adding -- and (t2.Deleted = 0) -- which return 2541 rows.  If I changed t2.Deleted = 1 then it returns only 288 rows.  But if I leave out t2.Deleted ...  then I get 2769 rows.

    My question is this -- what is the exact translation of the Linq Query above to a T Sql Query?  so that I get the same record/row counts from each query?  

    Am I not joining the tables in the same way between the Linq and T sql queries?

    the tables involved are   AppCI,    AppDisposition,    vDeptAppDtls.  In the sql server database, these tables contain relationships -- the t sql joins depict the relationships.

    this linq syntax is confusing me -- a.AppCI.AppDisposition.Disposition != "Retire"

    Is this like an implicit join between AppCI and AppDisposition tables?


    Rich P


    • Edited by Rich P123 Thursday, October 5, 2017 4:41 PM .........
    Thursday, October 5, 2017 3:50 PM

Answers

  • If the original LINQ queries worked well with database, then you can see the SQL equivalents using “SQL Server Profiler”, which can be found in the Tools menu of SQL Server Management Studio too. It can be configured to trace each query. Then you can adjust the query or compare with your variant.

    • Marked as answer by Rich P123 Friday, October 6, 2017 2:58 PM
    Friday, October 6, 2017 4:56 AM

All replies

  • I modified the LinQ query (and added t2.Deleted = 0 to the T Sql query) as follows

    I changed !string.IsNullOrEmpty(a.DeptHL)   to   a.DeptHL != null   in the LinQ Query

    --Linq
    var apps = db.vDeptAppDtls.Where(a => a.DeptHL != null && (a.AppCI.DispositionID != null
    	&& a.AppCI.AppDisposition.Disposition != "Retire")
    	&& !(a.AppCI.Deleted.Value && a.AppCI.Deleted.Value))
    	.Select(s => new { Dept = s.DeptHL, AppCIID = s.AppCIID }).Distinct().ToList();
    			  
    --------------------------------------
    --Tsql
    select distinct t1.depthl, t1.AppCIID  from (vDeptAppDtl t1 join AppCI t2 on t1.AppCIID = t2.AppCIID) 
               join  AppDisposition t3 on t2.DispositionID = t3.AppDispositionID
    where t1.DeptHL is not null and t2.DispositionID is not null and t3.Disposition != 'Retire'
               and t2.Deleted is not null and (t2.Deleted = 0)

    Now both queries are returning the same row count of 2541.  It appears to me that

    a.DeptHL != null   -- in the LinQ query

    and

    !string.IsNullOrEmpty(a.DeptHL)   -- also in the LinQ query

    are not the same thing.  So what would be the correct conversion/translation of

    !string.IsNullOrEmpty(a.DeptHL)

    to T Sql?


    Rich P




    • Edited by Rich P123 Thursday, October 5, 2017 4:46 PM ......
    Thursday, October 5, 2017 4:41 PM
  • I finally came up with some consistent rowcount numbers -- I  modified the LinQ Query and Tsql Query as follows:

    var apps = db.vDeptAppDtls.Where(a => a.DeptHL != null && a.DeptHL != "" && a.AppCI.DispositionID != null && a.AppCI.AppDisposition.Disposition != "Retire" && (a.AppCI.Deleted == null || a.AppCI.Deleted == false)) .Select(s => new { Dept = s.DeptHL, AppCIID = s.AppCIID }).Distinct().ToList(); --------------------------------------------------- select distinct t1.depthl, t1.AppCIID from (vDeptAppDtl t1 join .AppCI t2 on t1.AppCIID = t2.AppCIID) join AppDisposition t3 on t2.DispositionID = t3.AppDispositionID where t1.DeptHL is not null and t1.deptHL != '' and t2.DispositionID is not null and t3.Disposition != 'Retire' and (t2.Deleted is null or t2.Deleted = 0)

    The Deleted field is a bit field and apparently in LinQ I can only evaluate it as null, true, or false.  But now both queries are returning the same number of records which are the same count as the original Linq Query. 

    So the T Sql above is the T Sql equivalent to the Linq Query in question.


    Rich P


    • Edited by Rich P123 Thursday, October 5, 2017 9:46 PM ......
    Thursday, October 5, 2017 9:44 PM
  • If the original LINQ queries worked well with database, then you can see the SQL equivalents using “SQL Server Profiler”, which can be found in the Tools menu of SQL Server Management Studio too. It can be configured to trace each query. Then you can adjust the query or compare with your variant.

    • Marked as answer by Rich P123 Friday, October 6, 2017 2:58 PM
    Friday, October 6, 2017 4:56 AM
  • If the original LINQ queries worked well with database, then you can see the SQL equivalents using “SQL Server Profiler”, which can be found in the Tools menu of SQL Server Management Studio too. It can be configured to trace each query. Then you can adjust the query or compare with your variant.


    Thank you for your reply.  That is a great idea.  I have used the sql profiler before, years ago.  Hopefully, I can remember how to use it again.  I vaguely remember I had to connect to it via Visual studio -- thinking aloud here, I had to go to Tools and select like a service/service thread?   I can't remember. I will have to google this (bing this).

    Rich P

    Friday, October 6, 2017 3:02 PM