Thanks for the responses.

In my case the example problem here was a simplification of my actual problem but the responses helped me get on the right track.

The most entity framework way of approaching this problem was to have the ranges in another table and use a multi select. Note a Join would not work here as you can't do that with a LINQ joins - LINQ only supports equijoins.

Here's what the code would look like:

private class SimpleDbContext : DbContext
{
    // Stores the values 0, 1, 2, ..., 100000
    public virtual DbSet<SequencePoint> SequencePoints { get; set; }
    
    // [0, 0], [10, 11], [20, 21, 22], etc...
    public virtual DbSet<SelectionRange> SelectionRanges { get; set; }
}

private class SequencePoint
{
    public int SequenceNumber { get; set; }
}

private class SelectionRange
{
    public int LowerRange { get; set; }
    public int UpperRange { get; set; }
}

private void ConcatReworked()
{
    SimpleDbContext simpleDbContext = new();
    
    IQueryable<SequencePoint> queryable = from sequencePoint in simpleDbContext.SequencePoints
        from selectionRange in simpleDbContext.SelectionRanges
        where sequencePoint.SequenceNumber >= selectionRange.LowerRange
        where sequencePoint.SequenceNumber <= selectionRange.UpperRange
        orderby sequencePoint.SequenceNumber
        select sequencePoint;

    List<int> result = queryable.Select(sequenceNumber => sequenceNumber.SequenceNumber).ToList();
    
    _logger.LogInformation("result = {result}", result);
}

The other approach I tried was to write the Query manually using a StringBuilder which looked something along this lines of:

private async Task LoadSelection(List<SelectionRange> selectionRange)
{
    using NpgsqlConnection connection = await npgsqlDataSource.OpenConnectionAsync();
    StringBuilder commandSqlBuilder = new();
    commandSqlBuilder.Append("SELECT ");
    // ... More SQL here
    for (int i = 0; i < selectionRange.Count; i++)
    {
        SelectionRange range = selectionRange[i];
        commandSqlBuilder.Append("OR (");
        commandSqlBuilder.Append($"value >= (@range_lower_param_{i})");
        commandSqlBuilder.Append($"value <= (@range_upper_param_{i})");
        // ... More SQL here
    }
    // ... More SQL here
    NpgsqlCommand command = new NpgsqlCommand(commandSqlBuilder.ToString(), connection);
    command.Parameters.AddWithValue(...);
    NpgsqlDataReader reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
}

While this does work, and runs about twice as fast as the join, it does have new limitations such as A statement cannot have more than 65535 parameters. So I needed to split it into smaller statements and join the results in code later. Also when the database names change in a migration I will need to come back an update this String Builder to reflect the changes.