Most performant way to filter a huge List in C#?

You can adjust your storage to this specific query type. First, create dictionaries from your in-memory archive:

ArchiveCacheByDatapoint = ArchiveCache.GroupBy(c => c.DataPointId)
            .ToDictionary(c => c.Key, c => c.ToList());
ArchiveCacheByStation = ArchiveCache.GroupBy(c => c.StationId)
            .ToDictionary(c => c.Key, c => c.ToList());

Then use those dictionaries in your query:

bool hasStations = request.StationIds.Length > 0;
bool hasDatapoints = request.DatapointIds.Length > 0;            
int numFound = 0;
List<ArchiveCacheValue> result;
if (hasDatapoints && hasStations) {
    // special case - filter by both
    result = new List<ArchiveCacheValue>();
    // store station filter in hash set
    var stationsFilter = new HashSet<int>(request.StationIds);
    // first filter by datapoints, because you have more different datapoints than stations
    foreach (var datapointId in request.DatapointIds.OrderBy(c => c)) {                    
        foreach (var item in ArchiveCacheByDatapoint[datapointId]) {                        
            if (stationsFilter.Contains(item.StationId)) {
                // both datapoint and station matches filter - found item
                numFound++;
                if (numFound >= request.Start && result.Count < request.Length) {
                    // add to result list if matches paging criteria
                    result.Add(item);
                }
            }
        }
    }
}
else if (hasDatapoints) {                
    var query = Enumerable.Empty<ArchiveCacheValue>();                
    foreach (var datapoint in request.DatapointIds.OrderBy(c => c))
    {
        var list = ArchiveCacheByDatapoint[datapoint];
        numFound += list.Count;
        query = query.Concat(list);
    }
    // execute query just once
    result = query.Skip(request.Start).Take(request.Length).ToList();
}
else if (hasStations) {                
    var query = Enumerable.Empty<ArchiveCacheValue>();
    foreach (var station in request.StationIds.OrderBy(c => c))
    {
        var list = ArchiveCacheByStation[station];
        numFound += list.Count;
        query = query.Concat(list);
    }
    // execute query just once
    result = query.Skip(request.Start).Take(request.Length).ToList();
}
else {
    // no need to do Count()
    numFound = ArchiveCache.Count;
    // no need to Skip\Take here really, ArchiveCache is list\array
    // so you can use indexes which will be faster
    result = ArchiveCache.Skip(request.Start).Take(request.Length).ToList();
}

// Number of entries on the current page that will be shown
int numShown = result.Count;

I've measured it and on my machine it runs in 1ms (sometimes up to 10ms) for all types of queries I tried (by sections only, by datapoints only, by both sections and datapoints), for 100 million items.

I would at least store those values in an array or vector of struct ArchiveRow to make sure all data is in contiguous memory. This way you will greatly benefit from locality of reference (i.e. use the L1 cache efficiently). And you also avoid the overhead of a list (pointers/references). (Update: I did a quick lookup of C# List. Looks like List is the same as C++ vector (i.e. an array), and C# LinkedList is the same as C++ list.. a bit confusing - iow, no pointer overhead here for the C# List<>)

Try to make the struct as small as possible. Use uint32 or even uint16 where possible, maybe 32 bit for the datetime and maybe even float instead of double (depending on your values). Also put the widest values first in your struct (for better alignment).

Even the brute force approach (scanning the whole array, a few 100MB of contiguous memory) should finish in well under 1 second.

To optimize further you can sort the data (or better, retrieve it sorted from the database) which will allow you to do a binary search, and also keep the values for the resultset close together. For example: sort on StationId, DataPointId.

If the data gets bigger, you could store the same structure on disk (in a binary file) and access it via memory mapping.

Also, you only have to scan for the first 25 items. You could then store the last checked position (for that session / query), and when the next page is asked, start from there for the next 25 items. This will also save on memory which would be needed to store the full resultset.

If the number of stations is small, and the data is sorted on StationId, you could also keep a small list or jump table (while importing) with the starting position of each StationId and directly jump to there to scan for datapoints of that station.

it takes about 9 seconds to load the whole archive table (with currently about 10 million entries) into the List.

Just in case you didn't do this already, make sure to set an initial Capacity on the list to avoid multiple reallocations.

The answer of Evk has some good optimizations, I'd just wanted to point out some very unoptimized things in your original code, maybe removing them would speed it up already:

You do your query in-memory three times!

// execute query first time
int numFound = query.Count();

var result = query.Skip(request.Start == 0 ? 0 : request.Start - 1).Take(request.Length);

// executing query second time!
int numShown = result.Count();

// executing query third time!    
// result.Select [...] .ToList()

You should really do it once and then do the counting and pagination on that result.

Another optimization. Using linq join in memory is not that efficient.

So instead of joining the request.StationIds and request.DatapointIds i would create hashsets from them and use simple contains over the hashset. Something like this.

if (request.StationIds.Count > 0)
{
    var stationIdSet = new HashSet<int>(request.StationIds);
    query =  (from a in ArchiveCache
             stationIdSet.Contains(a.StationId)
             select a);
               // .AsParallel()
               // .MaxDegreeOfParallelism(...);
}

Running it in sequental manner for 9m records and 30 station ids, out performed the join with roughly 150 - 250 ms

For the parallel version with MaxDegreeOfParallelism = 2, both (the join and hashset) were performing worse.

NOTE: AsParallel is putting overhead which for simple operations like those it may not be the best option.