How this linq execute?

Question

Data = _db.ALLOCATION_D.OrderBy(a => a.ALLO_ID)
                       .Skip(10)
                       .Take(10)
                       .ToList();

Let say I have 100000 rows in ALLOCATION_D table. I want to select first 10 row. Now I want to know how the above statement executes. I don't know but I think it executes in the following way...

first it select the 100000 rows
then ordered by ALLO_ID
then Skip 10
finally select the 10 rows.

Is it right? I want to know more details.

Look at which SQL it generated. That's exactly how it executes. — Gert Arnold, Apr 17 '13 at 08:10
How to see which sql it generates? will be highly pleased if kindly tell me the way. — Atish Kumar Dipongkor, Apr 17 '13 at 08:15

score 2 · Accepted Answer · edited May 23 '17 at 11:43

This Linq produce a SQL query via Entity Framework. Then it depends on your DBMS, but for SQL Server 2008, here is the query produces:

SELECT TOP (10) [Extent1].[ALLO_ID] AS [ALLO_ID],   
FROM ( 
    SELECT [Extent1].[ALLO_ID] AS [ALLO_ID]
         , row_number() OVER (ORDER BY [Extent1].[ALLO_ID] ASC) AS [row_number]
    FROM [dbo].[ALLOCATION_D] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 10
ORDER BY [Extent1].[ALLO_ID] ASC

You can run this in your C# for retrieve the query:

var linqQuery = _db.ALLOCATION_D
                   .OrderBy(a => a.ALLO_ID)
                   .Skip(10)
                   .Take(10);
var sqlQuery = ((System.Data.Objects.ObjectQuery)linqQuery).ToTraceString();
Data = linqQuery.ToList();

Second option with Linq To SQL

var linqQuery = _db.ALLOCATION_D
                   .OrderBy(a => a.ALLO_ID)
                   .Skip(10)
                   .Take(10);
var sqlQuery = _db.GetCommand(linqQuery).CommandText;
Data = linqQuery.ToList();

References:

Run the C# code I give you, it is not dependant on your DBMS. — Cyril Gandon, Apr 17 '13 at 08:38
Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[InceptaProto.Entities.ALLOCATION_D]' to type 'System.Data.Objects.ObjectQuery'. — Atish Kumar Dipongkor, Apr 17 '13 at 08:53

score 0 · Answer 2 · answered Apr 17 '13 at 08:08

0

Your statement reads as follows:

Select all rows (overwritten by skip/take)
Order by Allo_ID
Order by Allo_ID again
Skip first 10 rows
Take next 10 rows

If you want it to select the first ten rows, you simply do this:

 Data = _db.ALLOCATION_D // You don't need to order twice
                    .OrderBy(a => a.ALLO_ID)
                    .Take(10)
                    .ToList()

answered Apr 17 '13 at 08:08

Chris Dixon

9,147
5
36
68

why Order by ALLO_ID two times? – Atish Kumar Dipongkor Apr 17 '13 at 08:12
Because you have two OrderBy's in your question. – Chris Dixon Apr 17 '13 at 08:14
no its not the answer. I explained it already in my question. thanks – Atish Kumar Dipongkor Apr 17 '13 at 08:22

score 0 · Answer 3 · answered Apr 17 '13 at 08:14

Up to the ToList call, the calls only generates expressions. That means that the OrderBy, Skip and Take calls are bundled up as an expression that is then sent to the entity framework to be executed in the database.

Entity framework will make an SQL query from that expression, which returns the ten rows from the table, which the ToList methods reads and places in a List<T> where T is the type of the items in the ALLOCATION_D collection.

How this linq execute?

3 Answers3