none
Linq - extract blocks which starts by 1 RRS feed

  • Question

  • Hello!

    I would like to extract block of data from my database (see below).
    The problem is to find blocks which always starts with status=1.

    But first, data must be grouped by GroupID (3 groups)

    Here is what I have so far:
    (btw: It is not necessary to find groups first,...)

    var groups = (from evt in ctx.SysEvents where evt.SysTime >= start && evt.SysTime < stop orderby evt.SysTime ascending group evt by evt.GroupID into g select g); foreach (var grp in groups) { // need to find blocks inside each group }

    // my result item class    
    public class ResultItem
        {
            public int BlockNumber { get; set; }
            public DateTime SysTime { get; set; }
            public int GroupID { get; set; }
            public int Status { get; set; }
            public int Forced { get; set; }
        }
    

    I would appreciate if someone writes a LINQ query that return block started with 1.

    Here is the fraction of my database:

    Blank rows indicates blocks.

               SysTime    GroupID      Status      Forced
    19.6.2012 15:13:24 3901 1 0
    19.6.2012 15:13:24 3901 0 0
    19.6.2012 15:13:29 3901 11 0
    19.6.2012 15:14:48 3901 1 0
    19.6.2012 15:14:54 3901 11 0
    19.6.2012 15:14:55 3901 10 0
    19.6.2012 15:14:58 3901 4 0
    19.6.2012 15:15:08 3901 11 1
    19.6.2012 15:15:09 3901 32 1
    19.6.2012 15:16:25 3901 1 0
    19.6.2012 15:16:26 3901 0 0
    19.6.2012 15:16:30 3901 11 0
    19.6.2012 10:00:02 3889 1 0
    19.6.2012 10:00:03 3889 0 0
    19.6.2012 10:00:07 3889 11 0
    19.6.2012 10:02:24 3889 1 0
    19.6.2012 10:02:25 3889 5 0
    19.6.2012 10:02:29 3889 11 0
    19.6.2012 10:02:30 3889 10 0
    19.6.2012 10:03:28 3889 32 0
    19.6.2012 10:03:31 3889 11 0
    19.6.2012 10:04:24 3851 1 0
    19.6.2012 10:04:25 3851 5 0
    19.6.2012 10:04:29 3851 11 0
    19.6.2012 10:04:30 3851 10 0
    19.6.2012 10:04:28 3851 32 0
    19.6.2012 10:04:31 3851 11 1

    Thursday, June 21, 2012 6:14 AM

Answers

  • Logically its possible by extending my above LINQ e.g.

        var groups = (from evt in ge.Table_1
                        orderby evt.SysTime, evt.GroupId
                        select evt).AsEnumerable().Select(
                                                i=> new ResultItem {
                                                                BlockNumber = (i.Status == 1 && blk == 0)  ? ++blk : (i.Status == 1 && blk > 0 ? ++blk : blk),
                                                                SysTime = i.SysTime,
                                                                GroupID = i.GroupId,
                                                                Status = i.Status,
                                                                Forced = i.Forced }).GroupBy(ii => new { ii.BlockNumber, ii.GroupID }).GroupBy(ii => ii.Key.GroupID);

    But I confused how will you extract and consume those values back.

    If we have to loop the grouping-s and type casting etc... then why do we need to put them in group.

    Anyway, please share with us after you achieve your goal, fun to learn.

    Cheer!

    Friday, June 22, 2012 1:15 PM

All replies

  • I'm not 100% sure of what you want to acomplish

    • Do you want to find the groups that start or have a value of 1 in status column?
    • Do you want to find the rows that have a value of 1 in status column? and then group them?

    Regards

    Thursday, June 21, 2012 1:15 PM
  • Have you try this way, its fun :)

    int blk = 0;
    using (generalEntities1 ge = new generalEntities1()) {
        var groups = (from evt in ge.Table_1
                        orderby evt.SysTime, evt.GroupId
                        select evt).AsEnumerable().Select(
                                                i=> new ResultItem {
                                                                BlockNumber = (i.Status == 1 && blk == 0)  ? ++blk : (i.Status == 1 && blk > 0 ? ++blk : blk),
                                                                SysTime = i.SysTime,
                                                                GroupID = i.GroupId,
                                                                Status = i.Status,
                                                                Forced = i.Forced });
    
        GridView1.DataSource = groups;
        GridView1.DataBind();
    };

    Thursday, June 21, 2012 2:11 PM
  • @TinMgAye:

    This helps a lot. Block numbers are correct.
    I think you can simplify BlockNumber calculation

    i=> new ResultItem {
    	BlockNumber = i.Status == 1  ? ++blk  : blk,
    	//BlockNumber = (i.Status == 1 && blk == 0)  ? ++blk : (i.Status == 1 && blk > 0 ? ++blk : blk),
    	SysTime = i.SysTime,
    	GroupID = i.GroupID,
    	Status = i.Status,
    	Forced = i.Forced }
    

    How ever this is what I am trying to achieve (still not there, but I believe I am not far away from the solution)
    Help is still appreciated.

    Hierarchy

    GroupIDs
        Blocks
           Events

       
    [0] = 3901
     +-- [0] = block 0
     |    +-- [0] = 19.6.2012 15:13:24 1
     |    +-- [1] = 19.6.2012 15:13:24 0
     |    +-- [2] = 19.6.2012 15:13:29 11
     +-- [1] = block 1
     |    +-- [0] = 19.6.2012 15:14:48 1
     |    +-- [1] = 19.6.2012 15:14:54 11
     |    +-- [2] = 19.6.2012 15:14:55 10
     |    +-- [3] = 19.6.2012 15:14:58 4
     |    +-- [4] = 19.6.2012 15:15:08 11
     |    +-- [5] = 19.6.2012 15:15:09 32
     +-- [2] = block 2
     |    +-- [0] = 19.6.2012 15:16:25 1
     |    +-- [1] = 19.6.2012 15:16:26 0
     |    +-- [2] = 19.6.2012 15:16:30 11
     |
    [1] = 3889
     +-- [0] = block 0
     |    +-- [0] = 19.6.2012 10:00:02 1
     |    +-- [1] = 19.6.2012 10:00:03 0
     |    +-- [2] = 19.6.2012 10:00:07 11
     +-- [1] = block 1
     |    +-- [0] = 19.6.2012 10:02:24 1
     |    +-- [1] = 19.6.2012 10:02:25 11
     |    +-- [2] = 19.6.2012 10:02:29 10
     |    +-- [3] = 19.6.2012 10:02:30 4
     |    +-- [4] = 19.6.2012 10:03:28 11
     |    +-- [5] = 19.6.2012 10:03:31 32
     |
    [2] = 3851
     +-- [0] = block 0
          +-- [0] = 19.6.2012 10:04:24 1
          +-- [1] = 19.6.2012 10:04:25 5
          +-- [2] = 19.6.2012 10:04:29 11
          +-- [3] = 19.6.2012 10:04:30  10
          +-- [4] = 19.6.2012 10:04:28  32
          +-- [5] = 19.6.2012 10:04:31  11

    Friday, June 22, 2012 7:25 AM
  • Logically its possible by extending my above LINQ e.g.

        var groups = (from evt in ge.Table_1
                        orderby evt.SysTime, evt.GroupId
                        select evt).AsEnumerable().Select(
                                                i=> new ResultItem {
                                                                BlockNumber = (i.Status == 1 && blk == 0)  ? ++blk : (i.Status == 1 && blk > 0 ? ++blk : blk),
                                                                SysTime = i.SysTime,
                                                                GroupID = i.GroupId,
                                                                Status = i.Status,
                                                                Forced = i.Forced }).GroupBy(ii => new { ii.BlockNumber, ii.GroupID }).GroupBy(ii => ii.Key.GroupID);

    But I confused how will you extract and consume those values back.

    If we have to loop the grouping-s and type casting etc... then why do we need to put them in group.

    Anyway, please share with us after you achieve your goal, fun to learn.

    Cheer!

    Friday, June 22, 2012 1:15 PM
  • Hi forum -dv,

    I will mark @TinMgAye's solution as answer, if you feel it is not sensible, please feel free to unmark, then we will continue to help you. :)

    @TinMgAye, thanks for your sharing. I found there're so many issues in the forum are solved by you, with your help, the forum will be better and better. :)

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, June 28, 2012 2:23 AM
    Moderator
  • @Allen Li, thanks for your appreciation :) AND PEACE!

    Thursday, June 28, 2012 2:42 AM
  • I am sorry for late reply, I was away for few days.

    I thank you TinMgAye for your solution. I took you advice and work on flat block list as solution.
    You actually spared me a lot of time.

    Any way, Allen Li has already done my job, by marking your solution as answer.

    Thanks to him, too. :)

    Tuesday, July 3, 2012 12:18 PM