Linq to SQL vs Access SQL - Why aren't Take() and TOP the same?

Question

Sorry I couldn't come up with a better title, but it's kind of a convoluted question to begin with.

I'm in the process of converting an access database over to the web, and I'm using linq to sql.

In the access database, there is a SQL function that returns the top 3 records, ordered descending by # of individuals collected. For example, in a data set that has the following # of individuals collected as 37,10,9,9,9,5,4,1,1, the query (using TOP 3) returns 37, 10, 9, 9, 9

This is currently the desired effect, as no DISTINCT is wanted for this particular result.

However, in linq-to-sql using Take(3), the returned result is 37,10,9 --- taking 3 records. no surprises.

My question is- how would I get linq to return the same result as the original access SQL? I know I can do this by looping through the code and checking each record til i get 3 distinct, but that seems a little silly to do.

Answer 1

var topThree = collection.OrderByDescending(c => c).Distinct().Take(3);
var top = collection.Where(topThree.Contains).OrderByDescending(c => c);

That should do what you want. You could easily wrap it into an extension method.