How does the Take() method work?

Question

I have a somewhat large table I'm querying in my web app, and I only want to return N number of rows from the table.

I've read through the MSDN documentation, but I can't see where it states if Take() first pulls all of the records from the DB, or if it behaves similar to SQL Server's TOP.

I'm worried if Take() will pull all records, and then get the top N number of records OR will it behave as expected and retrieve only the N number of records directly

Answer 1

See Return Or Skip Elements in a Sequence.

Take(N) will add TOP N to your SQL and only retrieve N records.

For example (using my own SQL Server 2014 with EF 6.1):

This LINQ:

var query = await dbContext.Lookup
                           .Where(w => w.LookupCd == '1')
                           .Take(10)
                           .ToListAsync();

Generates this SQL:

SELECT TOP (10) 
    [Extent1].[LookupId] AS [LookupId], 
    [Extent1].[LookupTypeId] AS [LookupTypeId], 
    [Extent1].[LookupCd] AS [LookupCd], 
    [Extent1].[LookupName] AS [LookupName], 
    [Extent1].[LookupDescription] AS [LookupDescription]
FROM [dbo].[Lookup] AS [Extent1]
WHERE '1' = [Extent1].[LookupCd]

Use the SQL Profiler (if you're using SQL Server) if you want to be sure what SQL your LINQ is generating. This is always a good practice with any LINQ you write.

Answer 2

It does it as best it can.

Since you seem to be using SQL Server and its engine knows about SQL Server's having a TOP it will use that. It would also use this with MS Access.

If you were using PostgreSQL, MySQL or SQL Lite it would use LIMIT.

It might also use "fetch first " + n + " rows only" (DB2 and SQL:2008 standard style) or "select first " + n + "from" (Informix style) or "select * from (" + restOfQuery + ") where rownum <= " + n on Oracle or whatever a given database needed.

And if someone wrote an engine for a database that absolutely couldn't support such limits then it could indeed close the result stream after it had taken what it needed.

Either way, it does the best it can in the particular case.

It also, incidentally, uses the same approach with a value of 1 for First() and its variants and with a value of 2 for Single() and its variants (since you need to try to take at least 2 rows to test that there is only 1 to take).