Hey,

I am using: - VS 2008 SP1 - LLBLGen v2.6.8.624 - SQL Server 2005

I have been using LINQ to LLBLGen all over but can't seem to get the Take() to generate a TOP xxx statement.

Here is the LINQ:

var locations = from l in metaData.DoctorLocation
    where (l.DoctorId == doctorId.GetValueOrDefault())
    select l.DoctorLocationId;

sampleList = (from a in metaData.Analyse
    join s in metaData.Sample on a.SampleId equals s.SampleId
    join r in metaData.Request on s.RequestId equals r.RequestId
    join d in metaData.RequestDoctorLocation on r.RequestId equals d.RequestId
    join rs in metaData.SampleReceiveStatus on s.SampleReceiveStatusId equals rs.SampleReceiveStatusId
    where testIdList.Contains(a.TestId) &&
    (a.AnalyseStateId == AnalyseStateEntity.NotLoaded) &&
    (rs.CanTestingProceed == true) &&
    locations.Contains(d.DoctorLocationId)
    orderby r.RequestNumber ascending, s.Suffix ascending
    select CustomFunction.CreateSampleName(r.RequestNumber, s.Suffix))
    .Take(testCount).ToList();

The sql that is generated is:

SELECT dbo.CreateSampleName([LPA_L3].[RequestNumber], [LPA_L2].[Suffix]) AS [LPFA_15] 
FROM (((( [preLinkV2].[dbo].[Analyse] [LPA_L1]  
INNER JOIN [preLinkV2].[dbo].[Sample] [LPA_L2]  ON  [LPA_L1].[SampleId] = [LPA_L2].[SampleId]) 
INNER JOIN [preLinkV2].[dbo].[Request] [LPA_L3]  ON  [LPA_L2].[RequestId] = [LPA_L3].[RequestId]) 
INNER JOIN [preLinkV2].[dbo].[Request_DoctorLocation] [LPA_L4]  ON  [LPA_L3].[RequestId] = [LPA_L4].[RequestId]) 
INNER JOIN [preLinkV2].[dbo].[SampleReceiveStatus] [LPA_L5]  ON  [LPA_L2].[SampleReceiveStatusId] = [LPA_L5].[SampleReceiveStatusId]) 
WHERE ( ( ( ( ( ( ( ( ( ( [LPA_L1].[TestId] IN (@TestId1)) 
AND ( [LPA_L1].[AnalyseStateId] = @AnalyseStateId2)) 
AND ( [LPA_L5].[CanTestingProceed] = @CanTestingProceed3)) 
AND  EXISTS 
     (SELECT [LPA_L7].[DoctorLocationId] FROM (SELECT [LPLA_13].[DoctorLocationId] 
     FROM [preLinkV2].[dbo].[DoctorLocation] [LPLA_13]  
     WHERE ( ( ( [LPLA_13].[DoctorId] = @DoctorId4)))) [LPA_L7] 
WHERE ( [LPA_L7].[DoctorLocationId] = [LPA_L4].[DoctorLocationId]))))))))) 
ORDER BY [LPA_L3].[RequestNumber] ASC,[LPA_L2].[Suffix] ASC

Is there something that I have done wrong or have missed. I had a look through all the documentation but couldn't seem to find anything.

Thanks.

1. Please update to the latest build and see if the problem persists.

2. Could you identify a pattern on your tests (when you use orderby, or some joins, or a cusomt mapping function)?

3) does the query indeed result in the results you wanted?

Otis wrote:

3) does the query indeed result in the results you wanted?

I knew as soon as you asked that question that I had done something wrong. I felt like I was back in junior school.

It does actually return the correct number of results.... DOH

I guess I should have debugged and checked the count before posting instead of just looking at the SQL and making an assumption. <Insert famous "Under Siege" quote here> It is part of background processing and not seen in the application that's why I just checked the SQL.

Sorry about that. Thanks for the great support.

I'm off to crawl under a rock.

deathwish wrote:

Otis wrote:

3) does the query indeed result in the results you wanted?

I knew as soon as you asked that question that I had done something wrong. I felt like I was back in junior school.

It does actually return the correct number of results.... DOH

I guess I should have debugged and checked the count before posting instead of just looking at the SQL and making an assumption. <Insert famous "Under Siege" quote here> It is part of background processing and not seen in the application that's why I just checked the SQL.

Sorry about that. Thanks for the great support.

I'm off to crawl under a rock.

haha Under Siege...

Well, it's not that dramatic, fortunately for you . Looking at the filter, you could think why isn't there a TOP clause in the query? It would make the query more efficient (it's now reading as much rows as specified in Take and then closing the datareader)

To be able to reliably emit TOP in a query, the resultset should contain unique values only, after all, you're requesting unique values. As you're sorting on a field not in the select list, DISTINCT can't be emitted. And because you're executing a function, the DQE can't decide if the resultset contains unique values, so it can't safely emit TOP. To be able to return unique values anyway, it does limitation filtering on the client by reading a row from the datareader and checking whether this is a unique row, and if not, skip it, otherwise use it. (using hashes).

Your conclusion from looking at the query was correct, though TOP would be impossible to emit, so it took a different route, to bring you the correct answers.