none
LINQ performance advice RRS feed

  • Question

  • Hi

    I have two related entities Applicants and ApplicantNotes. I have the following LINQ;

    From a In Applicants
    Where a.Stage = "abc" And a.JobType = "xyz"
    Select a.ApplicantID, a.first_name, a.last_name, a.Email, a.Tel, a.Mobile, a.ApplicantNotes

    Which only involves selecting a few fields of a large table. However when I run it in LINQPad the SQL generated is quiet complex as below;

    SELECT <long list of all fields, not just those in LINQ select> 
    FROM ( SELECT <long list of all fields, not just those in LINQ select> CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] 
    FROM [dbo].[Applicants] AS [Extent1] LEFT OUTER JOIN [dbo].[ApplicantNotes] AS [Extent2] ON [Extent1].[ApplicantID] = [Extent2].[ApplicantID] 
    WHERE (N'abc' = [Extent1].[Stage]) AND (N'xyz' = [Extent1].[JobType]) ) AS [Project1] 
    ORDER BY [Project1].[ApplicantID] ASC, [Project1].[C1] ASC 

    If however I take out the ApplicantNotes related entity, so the LINQ is now as;

    From a In Applicants
    Where a.Stage = "abc" And a.JobType = "xyz"
    Select a.ApplicantID, a.first_name, a.last_name, a.Email, a.Tel, a.Mobile 

    then SQL becomes quite small and quick;

    SELECT [Extent1].[ApplicantID] AS [ApplicantID], [Extent1].[Name] AS [Name], [Extent1].[Email] AS [Email], [Extent1].[Mobile] AS [Mobile], [Extent1].[Over18] AS [Over18], [Extent1].[NearestTube] AS [NearestTube], [Extent1].[IntroSource] AS [IntroSource], [Extent1].[JobType] AS [JobType], [Extent1].[Postcode] AS [Postcode], [Extent1].[Stage] AS [Stage], [Extent1].[CVFileSize] AS [CVFileSize], [Extent1].[CoverLetterFileSize] AS [CoverLetterFileSize]
    FROM [dbo].[Applicants] AS [Extent1]
    WHERE N'Waiter/Waitress' = [Extent1].[JobType]

    So the issue seems to be the inclusion of the related entity. What can I do to efficiently include the related entity?

    Thanks

    Regards


    • Edited by Y a h y a Thursday, October 20, 2011 1:31 AM
    Thursday, October 20, 2011 12:28 AM

Answers

All replies