LINQ - Minimize Records Returned - Correct Way to Write These Expressions

Question

Employee is a sample entity type.

var r1 = (from c in _ctx select c).Skip(5).Take(5);  

// my intent is to pull the first record from the query  
var r2 = (from c in _ctx select c).FirstOrDefault<Employee>();   

// my intent is to pull the last record from the query.
// any good way to ask for the result back in the reverse   
// order of the natural sort without specifing a field/property name?  
var r3 = (from c in _ctx select c).LastOrDefault<Employee>();  

Do these pull back the entire records (objects) and then filter? What is the best way to write these so that the whole line is a LINQ expression?

Answer 1

I believe that (assuming _ctx is a DataContext) the SQL generated will be mildly efficient.

Can I suggest that you run SQL Server Profiler while you run these bits of code?

I think the query for r2 will be a simple SELECT TOP (1) statement.

r1 has a chance of being efficient with:

SELECT TOP(5) [fields]
FROM (
       SELECT TOP (10) [fields]
       FROM [table]
       ORDER BY [identitycol] DESC
     )

r3 looks like it may just select all and take the last record.

Try Profiler and see :)

Answer 2

If you use IQueryable that means that you create a expression tree that will be run in the remote source

See http://stackoverflow.com/questions/252785/what-is-the-difference-between-iqueryablet-and-ienumerablet

Good books tools c# 3.0 in a NutShell,LinqPad, LINQ in Action

Answer 3

1. It filters records within database since IQueryable is used. However, @Codesleuth can be right concerning r3.
2. The most pretty-looking way is _ctx.Employees.Skip(5).Take(5), because there is no query alternative to method calls of Skip, Take, First, etc.

Answer 4

How do you know what the last record is? What determines the order? Can't you make a call to OrderByDescending and then take the FirstOrDefault? Perhaps that would yield a quicker query.