locked
LINQ To Entities SQL Output RRS feed

  • Question

  •   Below is my linq query.  It works fine and returns the correct result.  However, it creates a subquery when it sends to sql server.


    It essentially does this 

    SELECT TOP (1) 
    a.InterviewQuestionsID
    FROM ( SELECT 
    InterviewQuestionsID, 
    QuestionSequence,
    FROM InterviewQuestions
    WHERE InterviewID = 109
    )  AS a
    ORDER BY QuestionSequence DESC


    WHEN I THINK IT SHOULD BE DOING THIS...


    Select TOP (1) InterviewQuestionsID FROM InterviewQuestions
    WHERE InterviewID = 109 
    ORDER BY QuestionSequence DESC


    LINQ Creates a subquery, which also selects the QuestionSequence field (when it doesn't need to).

    Is my linq query not optimal? 
    public Guid GetLastQuestionId(int InterviewID)
        {
          Guid lastQuestionID = (from iq in _db.InterviewQuestionSet
                       where iq.Interview.InterviewID == InterviewID
                       orderby iq.QuestionSequence descending
                       select iq.InterviewQuestionsID).FirstOrDefault<Guid>();
    
          return lastQuestionID;
        }
    

    Tuesday, July 20, 2010 5:56 PM

Answers

  • It has no extra overhead so it shouldn't really matter if the subquery is there or not. Compare the execution plan for the two SQL statements and I'm sure you'll find they're identical.
       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    • Proposed as answer by liurong luo Thursday, July 22, 2010 7:04 AM
    • Marked as answer by peryan77 Thursday, July 22, 2010 11:59 AM
    Wednesday, July 21, 2010 2:25 AM
    Answerer

All replies

  • That is one of the linq quirks, is grabbing InterviewQuestionsID because it thinks that you might need it for anything else afterward as it is on the ordering part,  your query seems fine.  If you want to, simply use sql.

    Regards

    • Proposed as answer by liurong luo Thursday, July 22, 2010 7:04 AM
    Tuesday, July 20, 2010 8:12 PM
  • It has no extra overhead so it shouldn't really matter if the subquery is there or not. Compare the execution plan for the two SQL statements and I'm sure you'll find they're identical.
       Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools - add-in with new useful features for the L2S and EF designers in VS2008 and VS2010
    huagati.com/L2SProfiler - Query profiler for Linq-to-SQL, Entity Framework v4, and LLBLGen Pro
    • Proposed as answer by liurong luo Thursday, July 22, 2010 7:04 AM
    • Marked as answer by peryan77 Thursday, July 22, 2010 11:59 AM
    Wednesday, July 21, 2010 2:25 AM
    Answerer