How does the Take() method work?

.net c# entity-framework linq linq-to-entities

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

1
8
10/21/2015 10:08:21 PM

Accepted Answer

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.

SQL Profiler

11
2/22/2017 12:12:54 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow