Linq self join

Question

All,

My code is as follows:

  List<Customer> VisCount =
      (from SelPerson in Global.People

       select SelPerson).OfType<Customer>().Where(

        a => (a.TravelRequirements.FindIndex(b => 

        b.CompleteTimePlan.FindIndex(c=> c.RequiredArrivalTime < EndTime)  //Line x
        - b.CompleteTimePlan.FindIndex(c=> c.RequiredArrivalTime>StartTime) ==1 //Line y

              )

              !=-1)
         ).ToList();

The result I'm looking for is:

The findindex from line y is 1 more than the findindex of line x or
Line x is at the end of the list

I think I need to create two instances of B and outer join them? Unfortunately I can't work out how if it's possible to do an outer join in this situation and how to implement it?

Edit to add some data as requested. I've also added some more description as I think that will help.

I have a location class which contains the following (I've only included the relevant parts):

public class Location
    {
 public string Title;
    public int MinTransitTime;
    public int MaxOccupancy;

    public virtual Boolean SpaceAvailable(DateTime StartTime, DateTime EndTime)
    {

      List<Customer> VisCount =
      (from SelPerson in Global.People

       select SelPerson).OfType<Customer>().Where(

        a =>   (a.TravelRequirements.FindIndex(b => 

        b.CompleteTimePlan !=null &&

        b.CompleteTimePlan.FindIndex(c=> c.RequiredArrivalTime < EndTime)
        - b.CompleteTimePlan.FindIndex(c=> c.RequiredArrivalTime>StartTime) ==1

              )

              !=-1)
         ).ToList();

        if (VisCount.Count()<MaxOccupancy)
        {
          return true;
        }
        else
        {
          return false;
        }
    }
}

This linq statement is called from a method in a Location Class.

CompleteTimePlan has the definition of:

    public List<LocationPlan> CompleteTimePlan;

The relevant part of the LocationPlan definition is:

  public class LocationPlan
  {
    public Location AllocatedArea;
    public DateTime RequiredArrivalTime;
}


An example list might be:

First item:

AllocatedArea= Instance of Location with title "A"
RequiredArrivalTime = "10/02/2021 09:00:00"

Second item:

LocType = Instance of Location with title "B"
RequiredArrivalTime = "10/02/2021 10:00:00"

Third item:

LocType = Instance of Location with title "C"
RequiredArrivalTime = "10/02/2021 11:00:00"

The result I want from the Linq is:

Select all the people who are at this location between the StartTime and the EndTime. So using the sample data:

Start time 09:15 and EndTime 09:30 would be Location A (Above 09:00 and below 10:00)

I was thinking that I would need a self join because I am comparing two items in a list but I could be wrong?

I thought about using a method to return the RequiredArrivalTime from the next element in the list but wasn't sure if that was just adding complication?

As there is no Location D the time they leave C will be calculated as RequiredArrivalTime.AddMinutes(MinTime). I was planning to add that later.

I also just realised that the Linq will need b.CompleteTimePlan.Location=this at some point.

If I need to use a completely different Linq statement that I'm happy to do that. I come from a SQL background and I'm currently learning Linq and C# so may have taken the wrong approach with the Logic.

I know there is quite a lot of detail and I'm not intending my question to be a "write this entire Linq statement for me". I'm happy to be given some guidance and then complete it myself. I'm just not sure how to deal with comparing two items in a list in one Linq statement (or even if I can).

Answer 1

Thanks for your reply. I solved the issue a different way. I made a method in the Person class.

I was going to post this as a comment but it is too many characters.

However to directly answer your question:

Each item in the list has a start time and a minimum time. So for example:

Item 0 in the list: Start time: 09:00 and minimum time 30 mins
Item 1 in the list: Start time: 10:00 and minimum time 30 mins
Item 2 in the list: Start time: 11:00 and minimum time 30 mins

I have a search time that I want to compare to the items in the list. The logic should be as follows:

The search time should be above the start time of the list item
If there is a consecutive item in the list then the search time should be below the start time of the consecutive item
If there is no consecutive item then the search time should be below start time + min time

Some examples:

Example 1:

Search time: 10:45
Result should be list item 1 because:

10:45 is above the start time of item 1 (10:45 > 10:00)
Item 2 exists and the search time is below the start time of item 2 (10:45<11:00)

Example 2:

Search time: 11:15

Result should be item 2 because:

11:15 is above the start time of item 2 (11:15 > 11:00)
There is no item 3 so the search time should be compared to the start time (11:00) + min time (30 minutes). 11:15 is less then 11:30 so this is the required list item.

Example 3:

Search time: 11:45

There should be no result (or alternatively a null result) because:

11:45 is above the start time of item 2 (11:45 > 11:00)
There is no item 3 so the search time should be compared to the start time (11:00) + min time (30 minutes). 11:45 is after 11:30 so no items in the list match the search time

The number of items in the list could vary.

As I said I have solved the problem using a different solution so my question is not important. I would be interested to know the answer because it might be useful in the future however I am happy to mark the question closed if that is the appropriate thing to do?

Thanks