locked
Query datatable - lambda linq RRS feed

  • Question

  • Hi, I have a datatable dt representing a Task, it's duration and Queue

    The start and end duration are integers.

    DataTable dt = new DataTable();
    
    dt.Columns.Add(new DataColumn("TASK", typeof(System.String)));
    dt.Columns.Add(new DataColumn("START", typeof(System.Int16)));
    dt.Columns.Add(new DataColumn("END", typeof(System.Int16)));
    dt.Columns.Add(new DataColumn("Q", typeof(System.Int16)));
    
    dt.Rows.Add("A", 0, 10, 1);
    dt.Rows.Add("B", 18, 22, 1);
    dt.Rows.Add("C", 0, 15, 2);
    dt.Rows.Add("D", 5, 10, 3);
    dt.Rows.Add("E", 15, 20, 3);
    dt.Rows.Add("F", 20, 25, 3);
    dt.Rows.Add("G", 30, 45, 3);

    I attached chart for convenience.

    Q

    Now, assuming end of Time is 9999, I want to show free slots in each Q.
    The slot datatable should look like:
    Q    Start    End
    1    10    18
    1    22    9999
    2    15    9999
    3    0    5
    3    10    15
    3    25    30
    3    45    9999

    How to write lambda expression on input dt to get the slot dt?

    The following Oracle Query does the job in one shot (misses row '3,0,5' though),

    MS SQL Server 2012 also has lead/lag analytical function.

    Looking for an equivalent linq lambda expression:

    select 
      Q
     , Task -- task after which the free slot starts
     , End_Tm Slot_Start
     , coalesce(Next_Start_TM,9999) Slot_End
    from
    (
    with t as (
      select 'A' Task, 0 Start_TM, 10 End_TM, 1 Q from dual union
      select 'B', 18, 22, 1 from dual union
      select 'C', 0, 15, 2 from dual union
      select 'D', 5, 10, 3 from dual union
      select 'E', 15, 20, 3 from dual union
      select 'F', 20, 25, 3 from dual union
      select 'G', 30, 45, 3 from dual
      )
      select 
        Q
      , Task
      , Start_TM
      , End_Tm
      , (lead(Start_TM,1) over(partition by Q order by End_TM)) Next_Start_TM 
      from t
    )
    where End_Tm <> coalesce(Next_Start_TM,9999)
    ORDER BY 1,3,4;

    Thanks,

    -srinivas y.



    • Moved by Fred Bao Friday, January 10, 2014 3:01 AM It is a thread about C#
    • Edited by ysrini Friday, January 10, 2014 6:10 AM
    Friday, January 10, 2014 1:36 AM

Answers

  • Does that SQL really give the results you have shown?  My cte knowledge is not great but I don't understand how you get the

    3    0    5

    row from that query.

    Translating the query to linq looks like

    class Task {
       public string Name{get; private set;}
       public int Start{get; private set;}
       public int End {get; private set;}
       public int Q {get; private set;}
       
       public Task(string name, int start, int end, int q) {
          Name = name;
    	  Start = start;
    	  End = end;
    	  Q = q;
       }
    }
    
    .
    .
    .
    
    var tasks = new List<Task>{
    	new Task("A", 0, 10, 1),
    	new Task("B", 18, 22, 1),
    	new Task("C", 0, 15, 2),
    	new Task("D", 5, 10, 3),
    	new Task("E", 15, 20, 3),
    	new Task("F", 20, 25, 3),
    	new Task("G", 30, 45, 3)
    };
    	
    var free = tasks
    	.GroupBy (t => t.Q)
    	.Select(g => g.OrderBy (t => t.End)
                               .Select ((t, n) => new{
                                               Q = t.Q, 
                                               Task = t.Name, 
                                               Start_TM = t.Start, 
                                               End_TM = t.End, 
                                               Next_Start_TM = n+1 < g.Count () ?
                                                                  g.ToArray()[n+1].Start : 
                                                                  9999 
                                              }
                                        )
                               .Where (t => t.End_TM!=t.Next_Start_TM)
                     )
              .Select(g => g.Select((t,n)=>new {
                                        Q = t.Q, 
                                        Task = t.Task, 
                                        Slot_Start = t.End_TM, 
                                        Slot_End = n+1 < g.Count () ? 
                                                       g.ToArray()[n+1].Start_TM :
                                                       9999
                                                  }
                                      )
                     );

    free is now an IEnumerable<IEnumerable>. Obviously you can read from a DataTable instead of a List.  This linq reproduces (what I think is) the bug in your original SQL statement.

    Paul Linton

    • Marked as answer by ysrini Friday, January 10, 2014 8:08 AM
    Friday, January 10, 2014 6:12 AM
  • For now I am traversing the datatable as below, works:

    private void Create_Slot_table(DataTable dt)
    {
        DataTable dtSlot = new DataTable();
    
        dtSlot.Columns.Add(new DataColumn("Q", typeof(System.Int16)));
        dtSlot.Columns.Add(new DataColumn("TASK", typeof(System.String)));
        dtSlot.Columns.Add(new DataColumn("START", typeof(System.Int16)));
        dtSlot.Columns.Add(new DataColumn("END", typeof(System.Int16)));
    
        List<Int16> Qs = dt.AsEnumerable().Select(r => r.Field<Int16>("Q")).Distinct().OrderBy(x => x).ToList<Int16>();
    
        foreach (int Q in Qs)
        {
            DataRow prevDr = null;
            DataTable dtQ = dt.AsEnumerable().Where(r => r.Field<Int16>("Q") == Q).OrderBy(x => x.Field<Int16>("START")).CopyToDataTable();
    
            foreach (DataRow currDr in dtQ.Rows)
            {
                // If the first task doesn't start at 0 time then there's free slot here !
                if (prevDr == null && currDr.Field<Int16>("START") > 0)
                    dtSlot.Rows.Add(Q, null, 0, currDr.Field<Int16>("START"));
    
                // If the current task starts more than 0 secs after the previous task finishes
                if (prevDr != null && currDr.Field<Int16>("START") > prevDr.Field<Int16>("END"))
                    dtSlot.Rows.Add(Q,prevDr.Field<string>("TASK"),prevDr.Field<Int16>("END"),currDr.Field<Int16>("START"));
    
                prevDr = currDr;
            }
    
            // prevDr now holds the last Task in this queue, add free slot after this end
            dtSlot.Rows.Add(Q, prevDr.Field<string>("TASK"), prevDr.Field<Int16>("END"), 9999);
        }
    
        // Output Slot table
        Console.WriteLine("Slot Table : ");
        Console.WriteLine("Q" + "\t" + "TASK" + "\t" + "START" + "\t" + "END");
        foreach (DataRow dr in dtSlot.Rows)
        {
            Console.WriteLine(dr.Field<Int16>("Q") + "\t" + dr.Field<string>("TASK") + "\t" + dr.Field<Int16>("START") + "\t" + dr.Field<Int16>("END"));
        }
                
    }
    output:
    Slot Table : 
    Q	TASK	START	END
    1	A	10	18
    1	B	22	9999
    2	C	15	9999
    3		0	5
    3	D	10	15
    3	F	25	30
    3	G	45	9999

    Thanks,
    • Edited by ysrini Friday, January 10, 2014 6:17 AM
    • Marked as answer by ysrini Friday, January 10, 2014 8:08 AM
    Friday, January 10, 2014 6:14 AM
  • Paul, I would like the correct answer and below is the additional linq to get the missed ones.

    I traversed the 'free' as:

    foreach (var obj in free)
    {
        foreach (var v in obj)
        {
            Console.WriteLine(v.Q + "\t" + v.Task + "\t" + v.Slot_Start.ToString() + "\t" + v.Slot_End.ToString());
        }
    }

    1	A	10	18
    1	B	22	9999
    2	C	15	9999
    3	D	10	20
    3	F	25	30
    3	G	45	9999

    The following lists Tasks that start first in a queue but don't start at Time 0:

    var free2 = tasks
        .GroupBy(t => t.Q)
        .Select(g => g.OrderBy(t => t.End).Select((t, n) => new
        {
            Q = t.Q,
            Task = t.Name,
            Start_TM = t.Start,
            End_TM = t.End,
                                   
        }).First()).Where(x=>x.Start_TM > 0)
        ;

    The following can be appended to your 2nd sample to get the complete result:

    var free2 = tasks
        .GroupBy(t => t.Q)
        .Select(g => g.OrderBy(t => t.End).Select((t, n) => new
        {
            Q = t.Q,
            Task = "[null]",
            slot_start = 0,
            slot_end = t.Start,

        }).First()).Where(x => x.slot_end > 0)
        ;

    Thanks again Paul for your time.


    • Edited by ysrini Friday, January 10, 2014 8:08 AM
    • Marked as answer by ysrini Friday, January 10, 2014 8:09 AM
    Friday, January 10, 2014 6:36 AM

All replies

  • Hello,

    Since this issue is more regarding C#, I will move it to the suitable forum. There are C# forum who will help you better.

    Thanks for your understanding.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 10, 2014 3:01 AM
  • I think you should group your table based on Q first like this,

    from Qs in dt
    group Qs by Qs.Q into groupedTasks
    select new { Q=groupedTasks.Key.Name,
    Tasks=groupedTasks };
    

    then for each group, you can sort the group result by start.



    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Friday, January 10, 2014 3:57 AM
  • A Task and a FreeSlot are very similar - they have a start, an end and optionally a name.  I will create a class called Slot to represent either a Task or a FreeSlot.  I make the name mandatory and use "Free" for the name of a FreeSlot.  The class looks like this.

    class Slot {
       public int Start{get; private set;}
       public int End{get; private set;}
       public string Name{get; private set;}
       
       public static Slot FreeSlot(int start, int end) {
          return new Slot("Free", start, end);
       }
       
       public Slot(string name, int start, int end) {
       	Name = name;
    	Start = start;
    	End = end;
       }
    }
    

    The FreeSlot method is a helper to make it a little easier to create Free Slots.

    A Queue has tasks and free slots which are both just collections of Slots.  The Queue class exposes Tasks and FreeSlots as ReadOnlyCollections so that clients are forced to use the Queue class to manipulate the collections.  The Queue class looks like this

    class Queue {
       public ReadOnlyCollection<Slot> Tasks {get; private set;}
       public ReadOnlyCollection<Slot> FreeSlots {get; private set;}
       
       public void AddTask(Slot task) {
          tasks.Add(task);
    	  var split = freeSlots.Single(fs=>fs.Start<= task.Start && fs.End >= task.Start);
    	  freeSlots.Remove(split);
    	  if (split.Start < task.Start)
    	     freeSlots.Add(Slot.FreeSlot(split.Start, task.Start));
    	  if (split.End > task.End)
    	     freeSlots.Add(Slot.FreeSlot(task.End, split.End));
       }
       
       List<Slot> tasks;
       List<Slot> freeSlots;
       
       public Queue() {
    	  tasks = new List<Slot>();
    	  Tasks = new ReadOnlyCollection<Slot> (tasks);
    
    	  freeSlots = new List<Slot>{Slot.FreeSlot(0, 9999)};
    	  FreeSlots = new ReadOnlyCollection<Slot>(freeSlots);
       }
    }

    Public properties expose both Tasks and FreeSlots. The constructor creates the backing lists for Tasks and FreeSlots.  tasks is created empty but freeSlots is created with a single Slot from 0 to 9999.  When created a Queue is a single free Slot with no tasks.

    The AddTask method allows clients to add a task to this Queue.  The method maintains the 'tasks' list (easy) and also the 'freeSlots' list.  The Slot is just added to the tasks list.  A task that is added will always overlap exactly one free slot.  The free Slot that is overlapped is found and remembered as 'split'.  'split' is removed from the freeSlots list as it is now at least partially used.  If the start of 'split' is prior to the start of the new task then we add a free Slot.  If the end of 'split' is after the end of the new task then we add a free Slot.  Adding a task always removes one FreeSlot and adds either zero, one or two new FreeSlots.

    There are a couple of ways you could use these classes.  Here is one

    // The queues could be 'discovered' from the DataTable or
    // just created in advance as I do here
    var queues = new Dictionary<int, Queue>{
    	{1, new Queue()},
    	{2, new Queue()},
    	{3, new Queue()}
    };
    // You would initialize the queues from the items in you table
    // I have just used literals
    queues[1].AddTask(new Slot("A", 0, 10));
    queues[1].AddTask(new Slot("B", 18, 22));
    queues[2].AddTask(new Slot("C", 0, 15));
    queues[3].AddTask(new Slot("D", 5, 10));
    queues[3].AddTask(new Slot("E", 15, 20));
    queues[3].AddTask(new Slot("F", 20, 25));
    queues[3].AddTask(new Slot("G", 30, 45));
    
    // queues[x].FreeSlots is a collection of the unused slots for queue 'x'.


    Paul Linton

    Friday, January 10, 2014 4:08 AM
  • Thanks Prakash.

    Within a Queue, if Tasks are ordered by start time, how to get list of all slots between a task end time and the next task start time


    sri

    Friday, January 10, 2014 4:10 AM
  • Thanks Paul, in Oracle sql, the 'lead' or 'lag' function does the job (I updated the original post now).

    Doesn't .net linq provide a way to query the datatable in a similar way?


    sri

    Friday, January 10, 2014 4:41 AM
  • While traversing your group results, the time between current end time & next start time is your slot.

    Not sure how are you planning to implement your UI. As per Paul's reply, you can also consider creating temporary "Free" slots in your result.


    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Friday, January 10, 2014 5:08 AM
  • Understand prakash, it's similar to listing the tasks by queue and time and navigating the list to find the free slots, and I am trying to avoid this (exactly!) by using a linq that can mimic the database lag or lead analytic function. Sounds like there isn't an easier way yet !


    sri

    Friday, January 10, 2014 5:14 AM
  • Does that SQL really give the results you have shown?  My cte knowledge is not great but I don't understand how you get the

    3    0    5

    row from that query.

    Translating the query to linq looks like

    class Task {
       public string Name{get; private set;}
       public int Start{get; private set;}
       public int End {get; private set;}
       public int Q {get; private set;}
       
       public Task(string name, int start, int end, int q) {
          Name = name;
    	  Start = start;
    	  End = end;
    	  Q = q;
       }
    }
    
    .
    .
    .
    
    var tasks = new List<Task>{
    	new Task("A", 0, 10, 1),
    	new Task("B", 18, 22, 1),
    	new Task("C", 0, 15, 2),
    	new Task("D", 5, 10, 3),
    	new Task("E", 15, 20, 3),
    	new Task("F", 20, 25, 3),
    	new Task("G", 30, 45, 3)
    };
    	
    var free = tasks
    	.GroupBy (t => t.Q)
    	.Select(g => g.OrderBy (t => t.End)
                               .Select ((t, n) => new{
                                               Q = t.Q, 
                                               Task = t.Name, 
                                               Start_TM = t.Start, 
                                               End_TM = t.End, 
                                               Next_Start_TM = n+1 < g.Count () ?
                                                                  g.ToArray()[n+1].Start : 
                                                                  9999 
                                              }
                                        )
                               .Where (t => t.End_TM!=t.Next_Start_TM)
                     )
              .Select(g => g.Select((t,n)=>new {
                                        Q = t.Q, 
                                        Task = t.Task, 
                                        Slot_Start = t.End_TM, 
                                        Slot_End = n+1 < g.Count () ? 
                                                       g.ToArray()[n+1].Start_TM :
                                                       9999
                                                  }
                                      )
                     );

    free is now an IEnumerable<IEnumerable>. Obviously you can read from a DataTable instead of a List.  This linq reproduces (what I think is) the bug in your original SQL statement.

    Paul Linton

    • Marked as answer by ysrini Friday, January 10, 2014 8:08 AM
    Friday, January 10, 2014 6:12 AM
  • For now I am traversing the datatable as below, works:

    private void Create_Slot_table(DataTable dt)
    {
        DataTable dtSlot = new DataTable();
    
        dtSlot.Columns.Add(new DataColumn("Q", typeof(System.Int16)));
        dtSlot.Columns.Add(new DataColumn("TASK", typeof(System.String)));
        dtSlot.Columns.Add(new DataColumn("START", typeof(System.Int16)));
        dtSlot.Columns.Add(new DataColumn("END", typeof(System.Int16)));
    
        List<Int16> Qs = dt.AsEnumerable().Select(r => r.Field<Int16>("Q")).Distinct().OrderBy(x => x).ToList<Int16>();
    
        foreach (int Q in Qs)
        {
            DataRow prevDr = null;
            DataTable dtQ = dt.AsEnumerable().Where(r => r.Field<Int16>("Q") == Q).OrderBy(x => x.Field<Int16>("START")).CopyToDataTable();
    
            foreach (DataRow currDr in dtQ.Rows)
            {
                // If the first task doesn't start at 0 time then there's free slot here !
                if (prevDr == null && currDr.Field<Int16>("START") > 0)
                    dtSlot.Rows.Add(Q, null, 0, currDr.Field<Int16>("START"));
    
                // If the current task starts more than 0 secs after the previous task finishes
                if (prevDr != null && currDr.Field<Int16>("START") > prevDr.Field<Int16>("END"))
                    dtSlot.Rows.Add(Q,prevDr.Field<string>("TASK"),prevDr.Field<Int16>("END"),currDr.Field<Int16>("START"));
    
                prevDr = currDr;
            }
    
            // prevDr now holds the last Task in this queue, add free slot after this end
            dtSlot.Rows.Add(Q, prevDr.Field<string>("TASK"), prevDr.Field<Int16>("END"), 9999);
        }
    
        // Output Slot table
        Console.WriteLine("Slot Table : ");
        Console.WriteLine("Q" + "\t" + "TASK" + "\t" + "START" + "\t" + "END");
        foreach (DataRow dr in dtSlot.Rows)
        {
            Console.WriteLine(dr.Field<Int16>("Q") + "\t" + dr.Field<string>("TASK") + "\t" + dr.Field<Int16>("START") + "\t" + dr.Field<Int16>("END"));
        }
                
    }
    output:
    Slot Table : 
    Q	TASK	START	END
    1	A	10	18
    1	B	22	9999
    2	C	15	9999
    3		0	5
    3	D	10	15
    3	F	25	30
    3	G	45	9999

    Thanks,
    • Edited by ysrini Friday, January 10, 2014 6:17 AM
    • Marked as answer by ysrini Friday, January 10, 2014 8:08 AM
    Friday, January 10, 2014 6:14 AM
  • Thanks Paul, I corrected the OP a while ago about the row '3,0,5' missing from Oracle sql (didn't take into consideration if the first task starts after 0 time)

    I will try your code and let you know, very much appreciate your time on this and the working solution


    sri

    Friday, January 10, 2014 6:22 AM
  • Is your goal to reproduce buggy sql as my second sample does?

    Or do you want the correct answer as my first sample provides?

    (Please don't make substantial edits to preious posts, it just makes the threads difficult to follow)


    Paul Linton

    Friday, January 10, 2014 6:30 AM
  • Paul, I would like the correct answer and below is the additional linq to get the missed ones.

    I traversed the 'free' as:

    foreach (var obj in free)
    {
        foreach (var v in obj)
        {
            Console.WriteLine(v.Q + "\t" + v.Task + "\t" + v.Slot_Start.ToString() + "\t" + v.Slot_End.ToString());
        }
    }

    1	A	10	18
    1	B	22	9999
    2	C	15	9999
    3	D	10	20
    3	F	25	30
    3	G	45	9999

    The following lists Tasks that start first in a queue but don't start at Time 0:

    var free2 = tasks
        .GroupBy(t => t.Q)
        .Select(g => g.OrderBy(t => t.End).Select((t, n) => new
        {
            Q = t.Q,
            Task = t.Name,
            Start_TM = t.Start,
            End_TM = t.End,
                                   
        }).First()).Where(x=>x.Start_TM > 0)
        ;

    The following can be appended to your 2nd sample to get the complete result:

    var free2 = tasks
        .GroupBy(t => t.Q)
        .Select(g => g.OrderBy(t => t.End).Select((t, n) => new
        {
            Q = t.Q,
            Task = "[null]",
            slot_start = 0,
            slot_end = t.Start,

        }).First()).Where(x => x.slot_end > 0)
        ;

    Thanks again Paul for your time.


    • Edited by ysrini Friday, January 10, 2014 8:08 AM
    • Marked as answer by ysrini Friday, January 10, 2014 8:09 AM
    Friday, January 10, 2014 6:36 AM