Splitting a list of numbers into multiple lists I need split the list I get from the select into multiple list so I can use Parallel.For() to generate the next query in different threads.


I create the main list
Create the sub list
Keep filling sub list until reach max capacity.
Add sublist to main list


Create new sub list

When finish the loop save the last sublist


This works but I bet there is an easy / clean way to do this, I can change the db if needed.

List&lt;List&lt;long&gt;&gt; result = new List&lt;List&lt;long&gt;&gt;();
using (var conn = new NpgsqlConnection(strConnection))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT route_source_id " +                                      
                          "FROM route_sources " +
                          "WHERE has_route IS NULL " +          
                          "LIMIT 1000; ";

        using (var reader = cmd.ExecuteReader())
        {
            const long list_size = 100;                

            long row_id = 0;
            List&lt;long&gt; route_list = new List&lt;long&gt;();

            while (reader.Read())
            {                                
               if (row_id++ &gt; list_size) // Add the sublist to the result
               {
                   result.Add(route_list); 
                   row_id = 0;
               }

               if (row_id == 0)  // Create new sublist
               {
                  route_list = new List&lt;long&gt;();
               }

               if (!reader.IsDBNull(0)) // fill the sublist
               {
                    route_list.Add(reader.GetInt64(0));                                
               }
            }

            if (route_list.Count &gt; 0 ) // add the last sublist
            {
               result.Add(route_list);
            }
        }
   }
 }
 return result;


EDIT: aditional part of the process. 

I have ticker to check the db for pending id to be process

// read 1000 rows split into 10 list, using above function
List&lt;List&lt;long&gt;&gt; route_pending = getRouteRange(); 
long total_rows = 0;

while (route_pending.Count &gt; 0) 
{
     foreach (List&lt;long&gt; range in route_pending)
     {
         total_rows += range.Count;                    
     }
     routeParallelGeneration(route_pending);
     route_pending = getRouteRange(); // keep reading next 1000 rows
}


And the parallel function, here I call my db storeproc with an array parameter. So max 10 connections with array of size 100. Here was where got issues with the excesive number of connections and decide split the data into 10 lists

public void routeParallelGeneration(List&lt;List&lt;long&gt;&gt; route_pending)
{
    Action&lt;List&lt;long&gt;&gt; forEachLoop = route_source_id =&gt; //Begin definition of forLoop
    {
        using (var conn = new NpgsqlConnection(strConnection))
        {
            conn.Open();
            using (var cmd = new NpgsqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = "SELECT avl_db.process_route(@route_source)";
                cmd.CommandType = CommandType.Text;

                NpgsqlParameter p = new NpgsqlParameter("@route_source", NpgsqlDbType.Array | NpgsqlDbType.Bigint);
                p.Value = route_source_id;
                cmd.Parameters.Add(p);
                cmd.ExecuteNonQuery();
            }
        }
    };
    Parallel.ForEach(route_pending, forEachLoop);
}
Pretty minor but you don't need the  if (row_id == 0)

if (row_id++ &gt; list_size) // Add the sublist to the result
{
    result.Add(route_list); 
    row_id = 0;
    route_list = new List&lt;long&gt;();
}


Also it seem like you should also test for 

if (route_list.Count &gt; 0 ) 


Why not just put where route_source_id is not null in the query?

Connection, Command, and Reader behave the same with many DB so this is pretty portable.

I think you can use 

conn.CreateCommand()


Long is not required for row_id or list_size

Why bump row_id++ if there is no value?

In summary  

    using (var reader = cmd.ExecuteReader())
    {
        const int list_size = 100;                
        List&lt;long&gt; route_list = new List&lt;long&gt;(list_size);

        while (reader.Read())
        {                                
           if (route_list.Count &gt;= list_size) // Add the sublist to the result
           {
               result.Add(route_list); 
               route_list = new List&lt;long&gt;(list_size);
           }
           route_list.Add(reader.GetInt64(0));
        }

        if (route_list.Count &gt; 0 ) // add the last sublist
        {
           result.Add(route_list);
        }
    }