Hi,

i Have a typed list defined that is made up of three entities. Entity 1 is a left outer join to entity 2 which has a left outer join to entity 3, not the best but what i have to do. I have some filter predicates and a sort predicate. Running it straight will retrun 67,000+ records. I only want to get the top 20 records (regardless of duplicates). I thought the max records would handle this but i was wrong. How can I accomplish this?

Thanks,

Nate

Specifying the maxNumberOfItemsToReturn in the FetchTypedList method should only return the 20 records. Can you post your code that is not working as expected?

i am recieving the number of records i specify in the maxrecordstoreturn, but when I watch SQL Profiler the SQL Command to execute is not passing in TOP 'N' in the statement. It is simply passing in SELECT <field list> FROM <table and joins>. Am I to assume that using the maxrecords to return will retrieve all of the records from the database but then only return to the client the amoutn specified?

Yes, because it couldn't emit DISTINCT I think, and then TOP doesn't work in all cases. DISTINCT can't be used with fields of type image/text/blob etc.

is there any way I can enforce it to perform in this manner? The only field types that are being queried are DateTime, varchar, char, int and Guid.

I found this post from august discussing how TOP is ignored if any relations are present, http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4021. Is there any I can force the TOP to be executed? I am not concerned about duplicate records et al, I am concerned that I only want 20 records, but it has to pull 67,000 plus records in 8 seconds and then shelve me 20, where if I just run the query retrieved from profiler and put a TOP 20 in it it executes in half a second...

ah ha! I got it. After reading through some of the other posts I saw some people talking abotu the allowduplicates flag. I had this set to true. When it was set to true it sent a non top 20 query to SQL server (as seen through profiler). When I set the flag to false, the query in profiler was a SELECT TOP 20. Everything works fine now, btu I guess my question is shouldn't it be a mroe expensive query if you specify it to not allow duplicates?

I'm not quite sure what you mean by: "my question is shouldn't it be a mroe expensive query if you specify it to not allow duplicates?" DISTINCT filters out duplicates, so by not allowing duplicates (allowDuplicates==false), you get DISTINCT emitted (if possible).