LINQ query for scheduling syntax

I have a db to organize jobs. PostOne is the primary table with the foreign key linking the supporting tables, and the PostThr table is for recording scheduling information for the given job.

Table PostThr records the scheduling of when the jobs begin and end. There are 5 fields: ID, FK, ThrDate(DateTime), ThrTime(TimeSpan), and ThrText(string). "FK" is the jobID, and ThrText is either "CONFIRM START" or "CONFIRM END". There is one entry for a job start and another for the job end. The jobs do not last for more than one date, ie overnight.

The goal here is to show how many jobs are active at the same time, rather within the same hour block on a given date. So, given,

DateTime givenDate = DateTime.Parse("7/15/22");
DateTime givenTime = TimeSpan.Parse("9:00");

I would want to return a count of all unique jobID’s where the start is <= 9:00 AND the end is >= 9:00 on 7/15/22.

I would like to write this in LINQ.

I struggle a bit with sql in general, and thus LINQ. I feel like there is a join to be made here, but I am not sure of the structure or syntax in LINQ or sql. I have some feeble LINQ attempts, but I’m not sure that they are helpful to show.

>Solution :

This is untested, but something like this should work.

from begin in PostThr 
join end in PostThr on begin.FK equals end.FK
where begin.ThrDate == givenDate && end.ThrDate == givenDate
      && begin.ThrText == "CONFIRM START"
      && end.ThrText == "CONFIRM END"
      && begin.ThrTime <= givenTime
      && end.ThrTime >= givenTime
select new {Task=begin.FK, Date=begin.ThrDate, Start=begin.ThrTime, End=end.ThrTime}