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 will retrieve only the N number of records directly.

Linq:

dbContext.table.Where(w => w.id== 1).Take(10);

will generate the following SQL

select top 10 * from table

You can see the generated query using SQL Server Profile or when running under debugger, IntelliTrace shows SQL queries made.

Answer 3

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).

Answer 4

Here's what I use everytime for my gridviews paging with cool performance:

    resultList = context.MYTABLE.Where(WhereClause).OrderBy(orderCondition).Skip(firstItemIndex).Take(lastItemIndex - firstItemIndex + 1).ToList();

WhereClause is the WHERE part of the query (i.e.'myObj.field > 1') OrderBy is clear enough I guess. firstItemIndex is the value I pass to tell from which record I want to start (if my paging is 15 elements long and I call the function from page 2, the value would be 15*2 = 30 ) so we want to skip that amount of records. The Take part tells how many records I want to retrieve from DB. There are different ways to do that, in my case I used a custom pager which gave me a 'maxItemIndex' computed value. So I had to substract firstItemIndex from that value to know how many records to fetch. That way, only the specified amount of records are retrieved from the DB, which is pretty good performance. Hope it helps