LINQ question

• mjohnson 71479

  SSC Eights!

  Points: 846

  More actions

  July 12, 2019 at 9:30 pm

  #3662516

  I am hoping someone can answer this here

  I have the following code on an ASP.Net MVC controller

  db.AppQuestions.Include(a => a.AppResponses).Where(a => ((a.JobID == null) || (a.JobID == JobID)) && ((a.FamilyID == null) || (a.FamilyID == FamilyID)));

  it should return result similar to this SPROC

  CREATE PROCEDURE DisplayAllQuestions
  
  @JobID int,
  @FamilyID int = null
  
  AS
  
  SET NOCOUNT ON
  
  SELECT 
  Q.QuestionID,
  Q.QuestionType,
  Q.QuestionText,
  Q.RequireFollowUp,
  Q.FollowUpTo,
  Q.ResponseType,
  Q.CheckLegality,
  R.ResponseID,
  R.ResponseText,
  R.TriggersFollowUp
  FROM 
  dbo.AppQuestions Q LEFT OUTER JOIN 
  dbo.AppResponses R ON Q.QuestionID = R.QuestionID 
  WHERE 
  (((Q.JobID IS NULL AND  Q.FamilyID IS NULL)
  OR 
  (Q.JobID IS NULL AND  Q.FamilyID = @FamilyID))
  OR
  (Q.Prescreen = 1 AND Q.JobID = @JobID))
  AND
  Q.Deleted = 0
  
  GO
  

  In my development environment all of the correct responses are returned  but once in production it is not returning the questions where the JobID is not null.

  Ho do i know what Entity and LINQ are returning for the Select Query?

• frederico_fonseca

  SSCoach

  Points: 16011

  More actions

  July 13, 2019 at 11:50 am

  #3662657

  when using linq using https://www.linqpad.net/ is a must - with it you can see what SQL will be generated

  within your c# code you can also see what is the sql being generated if you hoover over the "results" at runtime and after it has been executed

  e.g.

  var result = db.AppQuestions.Include(a => a.AppResponses).Where(a => ((a.JobID == null) || (a.JobID == JobID)) && ((a.FamilyID == null) || (a.FamilyID == FamilyID)));

  hoovering over result will give you a "SQL" property with what you need.

   

  regarding the sample in question - what linq is defining is not the same as the sample sql so I would not expect to return the same results.

  the code above is being converted to

  from dbo.AppQuestions as extent1
  left outer join dbo.AppResponses as extent2
  on extent1.questionid = extent2.questionid
  where (extent1.jobid = @P__Linq__0 or extent1.jobid is null)
  and (extent1.familyid = @P__Linq__1 or extent1.familyid is null)

  which is not quite the same as

  from dbo.AppQuestions q
  left outer join dbo.AppResponses r
      on q.QuestionID = r.QuestionID
  where (((q.jobid is null and q.familyid is null)
      or (q.jobid is null and q.familyid = @FamilyId))
      or (q.Prescreen = 1 and q.jobid = @JobId))
      and q.deleted = 0

  This gives you the same "where" clause as your sample

  var result1 = db.AppQuestions.Include(a => a.AppResponses)
                  .Where(a => (
                               ( (a.jobid == null && a.familyid == null)
                              || (a.jobid == null && a.familyid == FamilyID)
                              || (a.Prescreen == 1 &&  a.jobid == JobID)
                              )
                               && a.deleted == 0
                              )
                  );