LINQ question

  • mjohnson 71479

    SSC-Addicted

    Points: 492

    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

    SSChampion

    Points: 14053

    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
    )
    );

     

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply