c# find item in list returned by LINQ query and compare its value with another item in list

Question

I have a list of events returned from a LINQ query on a certain day (d).

var findJSE = from a in db.DailyGPSTables 
    where (a.EventType == "JE" || a.EventType == "JS") && 
    a.EventDateTime.Value.Day == d.Day select a;

I want to compare the times of each matching set of JS and JE to see if they are in the right order eg. JS (Job start) is before JE (Job end). I am able to accomplish this if there is only one JS and One JE by using this.

foreach (DailyGPSTable e in itCompareDay)
{
  if (e.EventType == "JE" && e.EventDateTime.Value.Day == d.Day)
   {
     var findJS = from a in db.DailyGPSTables where a.EventType == "JS" && a.EventDateTime.Value.Day == d.Day select a;
     var time = findJS.FirstOrDefault();
     js = time.EventDateTime.Value;
      if (js > e.EventDateTime.Value)
       {
         EOOmessage = " On " + e.EventDateTime.Value.ToShortDateString() + " JE before JS";
         errorList.Add(EOOmessage);
         errorListRow.Add(dc);
       }
   }
}

However I am having a bit of trouble if there are more than one JS in a day which is allowed. I was provided an excellent potential fix by @NetMage but ran out of time trying to correctly implement it. My latest attempt is here:

//Response.Write("
3rd Condition number of JE and JS on " + e.EventDateTime.Value.Day + " match now we see if there is more than one js");
if (findJS.Count() > 1)
{
    //Response.Write("
3.1 Condition there is more than one JS on " + e.EventDateTime.Value.Day + " get time of first js tag find the time of the first je tag");
    var jsTime = findJS.ToList();
    var jeTime = findJE.ToList();

    for (int j = 0; j <= jsTime.Count - 1; j++)
    {
        //Response.Write("

The " + j + " " + jsTime[j].EventType + " Starts on " + jsTime[j].EventDateTime + "
");
        var jsTimefor = jsTime[j].EventDateTime;
        for (int k = 0; k <= jeTime.Count - 1; k++)
        {
            //Response.Write("

The " + k + " " + jeTime[k].EventType + " Starts on " + jeTime[k].EventDateTime + "
");
            var jeTimefor = jeTime[k].EventDateTime;
            if (jeTime[k].EventDateTime < jsTime[j].EventDateTime)
            {
                //Response.Write(" The " + k + " " + jeTime[k].EventType + " Starts on " + jeTime[k].EventDateTime + " and < " + jsTime[j].EventDateTime + " which is " + jsTime[j].EventType + "
");
                EOOmessage = " On " + e.EventDateTime.Value.ToShortDateString() + " JE before JS";
                errorList.Add(EOOmessage);
                errorListRow.Add(dc);
            }
        }
    }
}

This attempt highlights all JE rows for the day and creates an error condition where there is none.

UPDATE:

I have been able to create a list containing the event types and times.

How would I iterate through that list and make sure the first event is an JS and it is the earliest and the next event is a JE and it is next in order of time. if not report the error.

I am trying to consolidate and loop through days. This reports only one error on the 21st but there are errors on the 23rd and 27th.

for (DateTime d = startDate.Date; d <= endDate.Date; d = d.AddDays(1))
{
    Response.Write("
" + d + "
");
    var itCompareDay = (from h in db.DailyGPSTables
                        where h.EmplID == EmpID
                                && (h.EventDateTime.Value.Day == d.Day)
                                && (h.EventType == "SS" || h.EventType == "JS" || h.EventType == "LS" || h.EventType == "LE" || h.EventType == "JE" || h.EventType == "SE")
                        orderby h.EventDateTime
                        select h).ToList();
    string EOOmessage="";
    var lastEventType = itCompareDay.Any(x => x.EventType == "JS")? "JE" : "JS";
    foreach (DailyGPSTable e in itCompareDay)
    {
        Response.Write("
Event Type " + e.EventType + " Count " + dc + " for the " + d.Day + "
");
        if (e.EventDateTime.Value.Day == d.Day)
        {
            if (e.EventType == lastEventType)
            {
                var error = e.EventType == "JS"
                ? "JS before JE"
                : "JE before JS";
                Response.Write("
OUT OF SEQUENCE JE" + " ON " + e.EventDateTime.Value.ToShortDateString());
            }
            lastEventType = e.EventType;
        }
        dc = dc + 1;
    }
    rowNumber = rowNumber + dc;
}

Errors incorrectly reported.

user_2052655 · Answer

I'm not sure I completely understand the design here, and it seems to me you'd be better off storing your events in an Event table, where each row has a Start and End column.

But one way you can validate a list of related items as you've described is to order the list by EventDateTime, and then keep track of the lastEventType. This way you can check if the current event type is equal to the lastEventType then it's an error.

For example:

public class Event
{
    public string EventType { get; set; }
    public DateTime? EventDateTime { get; set; }

    public override string ToString()
    {
        return $"{EventDateTime}: {EventType}";
    }
}

public class Program
{
    static void Main(string[] args)
    {            
        // Events for the day ordered by EventDateTime
        var events = new List
        {
            new Event {EventType = "JE",
                EventDateTime = DateTime.Parse("10/21/2018 12:23 PM")},
            new Event {EventType = "JE",
                EventDateTime = DateTime.Parse("10/21/2018 2:29 PM")},
            new Event {EventType = "JE",
                EventDateTime = DateTime.Parse("10/21/2018 2:40 PM")},
            new Event {EventType = "JS",
                EventDateTime = DateTime.Parse("10/21/2018 2:15 PM")},
            new Event {EventType = "JS",
                EventDateTime = DateTime.Parse("10/21/2018 3:12 AM")},
            new Event {EventType = "JS",
                EventDateTime = DateTime.Parse("10/21/2018 12:12 PM")},
        }.OrderBy(e => e.EventDateTime);

        // Initialize lastEventType to the opposite of the type of event we 
        // expect to see first (which is "JS" unless there are no "JS" events)
        var lastEventType = events.Any(e => e.EventType == "JS")
            ? "JE" : "JS";

        foreach (var e in events)
        {
            var errorMessage = string.Empty;

            // Check for error
            if (e.EventType == lastEventType)
            {
                Console.ForegroundColor = ConsoleColor.Red;

                // Create an error message
                var error = e.EventType == "JS"
                    ? "JS before JE"
                    : "JE before JS";

                var dateStr = e.EventDateTime?.ToShortDateString() ?? "[null date]";

                errorMessage = $" - On {dateStr} this error occurred: [{error}]";

                // Your code would include:
                // errorList.Add(errorMessage); 
                // errorListRow.Add(dc);
            }

            Console.WriteLine(e + errorMessage);
            Console.ResetColor();

            // Update the lastEventType with this event type
            lastEventType = e.EventType;
        }

        GetKeyFromUser("
Done! Press any key to exit...");
    }
}

Output

c# find item in list returned by LINQ query and compare its value with another item in list

1 Answers