Linq to SQL query taking forever

Question

Ok, first I thought I had a problem with how I was querying things. But apparently the problem lies in how linq translates my query to sql.

Here's my linq:

var items = (from p in ctx.bam_Prestatie_AllInstances
             join q in ctx.bam_Zending_AllRelationships on p.ActivityID equals q.ReferenceData
             join r in ctx.bam_Zending_AllInstances on q.ActivityID equals r.ActivityID
             orderby p.LastModified descending
             where r.PrestatieOntvangen >= vanaf && r.PrestatieOntvangen <= tm
             select new Data.BAMPrestatieInstance
               {
                Aanvaard = p.PrestatieAanvaard,
                Contactnummer = r.ContactNr,
                Identificatie = p.Identificatie,
                Foutmelding = ((p.Foutmelding == "" || p.Foutmelding == null) && p.PrestatieAanvaard == null) ? "De prestatie is bezig met verwerkt te worden." : p.Foutmelding.Replace("\r\n", " "),
                Ontvangen = p.PrestatieZendingOntvangen,
                Uitvoerdatum = p.Uitvoerdatum.Replace('-', '/'),
                Zender = r.Zender,
                PrestatieCode = p.PrestatieCode,
                ZendingsNr = r.Zendingnummer.ToString(),
                GroepsAanvaarding = r.Identificatie
               }).Take(100);

Which gets translated in:

SELECT TOP (100) [t3].[Zender], [t3].[ContactNr] AS [Contactnummer], [t3].[Identificatie], [t3].[value] AS [Uitvoerdatum], [t3].[PrestatieZendingOntvangen] AS [Ontvangen], [t3].[PrestatieAanvaard] AS [Aanvaard], [t3].[value2] AS [Foutmelding], [t3].[PrestatieCode], [t3].[value3] AS [ZendingsNr], [t3].[Identificatie2] AS [GroepsAanvaarding]

FROM (

    SELECT [t2].[Zender], [t2].[ContactNr], [t0].[Identificatie], REPLACE([t0].[Uitvoerdatum], @p0, @p1) AS [value], [t0].[PrestatieZendingOntvangen], [t0].[PrestatieAanvaard], 

        (CASE 

            WHEN (([t0].[Foutmelding] = @p2) OR ([t0].[Foutmelding] IS NULL)) AND ([t0].[PrestatieAanvaard] IS NULL) THEN CONVERT(NVarChar(3800),@p3)

            ELSE REPLACE([t0].[Foutmelding], @p4, @p5)

         END) AS [value2], [t0].[PrestatieCode], CONVERT(NVarChar,[t2].[Zendingnummer]) AS [value3], [t2].[Identificatie] AS [Identificatie2], [t2].[PrestatieOntvangen], [t0].[LastModified]

    FROM [dbo].[bam_Prestatie_AllInstances] AS [t0]

    INNER JOIN [dbo].[bam_Zending_AllRelationships] AS [t1] ON [t0].[ActivityID] = [t1].[ReferenceData]

    INNER JOIN [dbo].[bam_Zending_AllInstances] AS [t2] ON [t1].[ActivityID] = [t2].[ActivityID]

    ) AS [t3]

WHERE ([t3].[PrestatieOntvangen] >= @p6) AND ([t3].[PrestatieOntvangen] <= @p7)

ORDER BY [t3].[LastModified] DESC

As you can see, first it selects EVERYTHING and then it takes the top 100 and does the where. Why is this? Why can't it directly do the top 100, I think the problem why my queries run so long is because of this. Is there a better way to construct my linq query then?

Thanks

Answer 1

Try this:

var items =  from p in ctx.bam_Prestatie_AllInstances.OrderByDesc(p => p.LastModified).Take(100) 
             join q in ctx.bam_Zending_AllRelationships on p.ActivityID equals q.ReferenceData 
             join r in ctx.bam_Zending_AllInstances on q.ActivityID equals r.ActivityID 
             where r.PrestatieOntvangen >= vanaf && r.PrestatieOntvangen <= tm 
             select new Data.BAMPrestatieInstance 
               { 
                Aanvaard = p.PrestatieAanvaard, 
                Contactnummer = r.ContactNr, 
                Identificatie = p.Identificatie, 
                Foutmelding = ((p.Foutmelding == "" || p.Foutmelding == null) && p.PrestatieAanvaard == null) ? "De prestatie is bezig met verwerkt te worden." : p.Foutmelding.Replace("\r\n", " "), 
                Ontvangen = p.PrestatieZendingOntvangen, 
                Uitvoerdatum = p.Uitvoerdatum.Replace('-', '/'), 
                Zender = r.Zender, 
                PrestatieCode = p.PrestatieCode, 
                ZendingsNr = r.Zendingnummer.ToString(), 
                GroepsAanvaarding = r.Identificatie 
               }; 

Since the sort applies only to your first table, I'd try to force the order by and take 100 to be applied before the join.