How to skip last 2 records and get all other records with linq?

Question

I have a table called Test:

Test: Id, CreatedBy, CreatedDate

Now I want to get list of test but skip last 2 test. So if I have say for e.g. 10 test then I want to get 1 - 8 test and skip test 9 and 10.

This is how I am trying to do that:

var query = context.Test.OrderByDescending(t=>t.Id).Skip(2) // How to take other records?

Answer 1

In this case: Take(8)

With Take and Skip you can get any range you want.

E.G:

var query = context.Test.OrderByDescending(t=>t.Id);
var allButTheLastTwoElements = query.Take(query.Count() - 2);

Safest way:

var query = context.Test.OrderByDescending(t=>t.Id).ToList();
var allButTheLastTwoElements = query.Take(Math.Max(0,query.Count() - 2));

Or you could just do it the other way around (depending on your requirements)

var query = context.Test.OrderByAscending(t=>t.Id).Skip(2);

Answer 2

If records size is not fixed, you would use:

test.Take(test.Count-2);
//If records are already sorted in the order you like,

or

test.Where(t=>t.ID <= test.Max(m=>m.ID)-2);
//Where ID is a unique key and the list may not be sorted by id
//This will return the lowest 8 ID even if the list is sorted by address or whatever.

Answer 3

What you need is very simple, you don't even need to use Take or query the database twice.

If you OrderByDescending and Skip the first N elements, then you're taking all the remaining elements by default. So you can just do this:

var query = context.Test.OrderByDescending(t=>t.Id).Skip(2);

Docs:

Bypasses a specified number of elements in a sequence and then returns the remaining elements.

If you don't really intend to deffer the execution or append additional querying logic, then calling .ToList() at the end (which actually executes the query against the database) is logical.