locked
LINQ lambda syntax for counting number of sequence RRS feed

  • Question

  • User-34860367 posted

    Hello all,

    I got table Student records in database as follows:

    ExamDate          Test            Result
    01/21/2016         Math           Pass
    06/02/2016         Art               Pass
    05/31/2017         Math           Fail
    06/28/2017         Art              Pass
    07/03/2017         Math          Pass
    07/19/2017         Art              Fail
    08/01/2017         Math          Fail
    09/13/2017         Art              Fail
    09/15/2017         Math          Fail
    10/01/2017         Art              Fail
    10/10/2017         Math          Pass
    10/11/2017         Art             Fail

    ....

    In above sample data, there are 3 consecutive fails (yellow highlight) for Art test and 1 consecutive fail (blue highlight) for Math test. Anyone can help me to write LINQ lambda to counting how many sequential consecutive fails each test (Math, Art) based on sorting exam date?

    Thanks in advance.

    Monday, January 14, 2019 8:07 PM

Answers

  • User-34860367 posted

    Hello,

    There are two different tests: Math and Art. There is a Pass for Math record before your indicated Art record but we cannot count for not consecutive fail because it is not the same test. 

    Let me clear. Per highlights that showed two consecutive failures that count one sequential fail. Therefore, 4 yellow highlights should be counted 3 sequential fails and 2 blue highlights for 1 sequential fail. 

    Anyway, I found the solution for it from another forum.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 16, 2019 2:08 PM

All replies

  • User1520731567 posted

    Hi avt2k7,

    In above sample data, there are 4 sequential fails in a row. Anyone can help me to write LINQ lambda to counting how many sequential fails in a row based on exam date?

    Do you mean you want to get the number of consecutive failure records?

    Single failure record is not counted?

    In your code,it should be 5?

    If so, I think could nest loops by foreach,it is easier to implement than linq,I make a demo,you could refer to it:

     List<sequential> model = new List<sequential>();
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "aa", Result = "pass" });
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "bb", Result = "fail" });
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "cc", Result = "pass" });
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "aa", Result = "fail" });
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "bb", Result = "fail" });
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "cc", Result = "fail" });//the count should be 3
                model.Add(new sequential { ExamDate = Convert.ToDateTime("2018-01-01"), Test = "aa", Result = "pass" });
    
                var count = 0;//get number of sequence
                var jcount = 1;
    
                for (var i = 0; i < model.Count; i++)
                {
                    for (var j = jcount; j < model.Count; j++)
                    {
                        if (model[i].Result == model[j].Result)
                        {
                            count++;
                            break;
                        }
                        else
                        {
                            jcount++;
                            break;
                          
                        }
                    }
    
                }

    Finally,check the value of variable count,look the picture:

    Best Regards.

    Yuki Tao

    Tuesday, January 15, 2019 8:55 AM
  • User-34860367 posted

    Hello,

    Thanks. In my problem,  I want to count the number of consecutive failure records for each test. That mean a single failure record is not counted. Please see the above modifications on my problem. I am looking for LINQ to solve my issue but not sure any solution for it?

    Tuesday, January 15, 2019 2:09 PM
  • User1520731567 posted

    Hi avt2k7,

    ExamDate          Test            Result
    01/21/2016         Math           Pass
    06/02/2016         Art               Pass
    05/31/2017         Math           Fail
    06/28/2017         Art              Pass
    07/03/2017         Math          Pass
    07/19/2017         Art              Fail
    08/01/2017         Math          Fail
    09/13/2017         Art              Fail
    09/15/2017         Math          Fail
    10/01/2017         Art              Fail
    10/10/2017         Math          Pass
    10/11/2017         Art             Fail

    Unclear,why 10/11/2017         Art             Fail is also in sequence.

    There is a pass in front of it.

    Best Regards.

    Yuki Tao

    Wednesday, January 16, 2019 2:07 AM
  • User-34860367 posted

    Hello,

    There are two different tests: Math and Art. There is a Pass for Math record before your indicated Art record but we cannot count for not consecutive fail because it is not the same test. 

    Let me clear. Per highlights that showed two consecutive failures that count one sequential fail. Therefore, 4 yellow highlights should be counted 3 sequential fails and 2 blue highlights for 1 sequential fail. 

    Anyway, I found the solution for it from another forum.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 16, 2019 2:08 PM