LINQ Select Where, then Join Where-CodePudding

I am trying to determine a count of specified vehicles being used at a certain time from 2 different tables. Table postThr contains the timeframes, and table postFiv contains various information including vehicle types when column "v" is indicated.

All tables share an "FK" column.

We would be given a date, a start time, an end time, and a vehicle type.

I'm pretty sure I got the select statement right, below. Now I have to join the postFiv for the vehicle.

        DateTime givenDate = DateTime.Parse("11/15/22");
        TimeSpan givenTime = TimeSpan.Parse("9:00");
        TimeSpan givenEndT = TimeSpan.Parse("13:00");
        string vehicleType = "VAN";

        var qry = from x in _context.PostThrs
                  where
                    x.ThrDate == givenDate &&
                    x.ThrText == "SERVICE-" &&
                    ((
                    x.ThrTime < givenTime &&
                    x.ThrEndT > givenTime
                    ) || (
                    x.ThrTime < givenEndT &&
                    x.ThrEndT > givenEndT
                    ))
                    
                    select x
                    ;

My instinct is to continue on with something like:

select x new {
join ex in _context.PostFivs
on ex.FK equals x.FK
where ex.Code == "V" && ex.Text == "VAN"
}.Count()

but I know that's not right. I'm really struggling with the complex LINQ statements; makes me want to go back to SQL to study.

EDIT

Model classes were requested.

PostThr
-ThrID - int
-ThrFK - string
-ThrDate - datetime
-ThrTime - timespan
-ThrEndT - timespan
-ThrText - string

PostFiv
-FivID - int
-FivFK - string
-FivCode - string[1 char]
-FivText - string

EDIT I'm not sure what you are looking for exactly, but these are the full models. As requested. There are no navigation properties.

This is postThr

public class PostThr
{
    //would be nice if we could require at least 1 record for each Zero record

    [Key]
    public int ThrId { get; set; }

    [ForeignKey("PostZero")]
    [Display(Name = "0/")]
    public string ThrZero { get; set; }

    [Display(Name = "Date*")]
    [DataType(DataType.Date)]
    //[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    public DateTime ThrDate { get; set; }

    [Display(Name = "Time")]
    //[RegularExpression(@"^\d \.\d{0,2}$", ErrorMessage = "Must be between 0.00 and 23.99")]
    //[Range(0, 23, ErrorMessage = "Must be between 0.00 and 23.99")]
    public TimeSpan ThrTime { get; set; }

    [Display(Name = "End Time")]
    public TimeSpan ThrEndT { get; set; }

    [Required]
    [RegularExpression(@"[a-zA-Z0-9""'\s-|\.\=\ \*\/\\]*$")]
    [Display(Name = "Text")]
    [StringLength(160, MinimumLength = 2)]
    public string ThrText { get; set; }

    public string ThrTrackUser { get; set; }

    public string ThrTrackTime { get; set; }
}

and the postFiv

public class PostFiv
{
    [Key]
    public int FivId { get; set; }

    [ForeignKey("PostZero")]
    [Display(Name = "0/")]
    public string FivZero { get; set; }

    [Required]
    [Display(Name = "Priority Number")]
    [StringLength(1, MinimumLength = 1)]
    //[RegularExpression(?)] need to have only numbers 1-9
    public string FivPrio { get; set; }

    [Required]
    [Display(Name = "Code Letter")]
    [StringLength(1, MinimumLength = 1)]
    //[RegularExpression(?)] need to have only letters a-z any case
    public string FivCode { get; set; }

    [Required]
    [RegularExpression(@"[a-zA-Z0-9""'\s-|\.\=\ \*\/\\/¥():]*$")]
    [Display(Name = "Remark Text")]
    [StringLength(2000, MinimumLength = 1)]
    public string FivText { get; set; }

    public string FivTrackUser { get; set; }

    public string FivTrackTime { get; set; }
}

EDIT another bite at the apple... There are 2 jobs on 14NOV with a van, and meet the criteria in general. So answer must be '2', but I'm getting zero, so, no, this isn't working so far.

DateTime givenDate = DateTime.Parse("11/14/22");
        TimeSpan givenTime = TimeSpan.Parse("9:00");
        TimeSpan givenEndT = TimeSpan.Parse("18:00");

        var qry = from x in _context.PostThrs
                  where
                    x.ThrDate == givenDate &&
                    x.ThrText == "SERVICE-" &&
                    ((
                    x.ThrTime < givenTime &&
                    x.ThrEndT > givenTime
                    ) || (
                    x.ThrTime < givenEndT &&
                    x.ThrEndT > givenEndT
                    )) select new { zero = x.ThrZero };

        var ans = from y in qry join ex in _context.PostFivs on y.zero equals ex.FivZero select ex;

        var oth = (from z in ans where z.FivCode == "V" && z.FivText == "VAN-" select z).Count();

        var xyz = oth;

CodePudding user response：

You can use direct join in Single query like below:

    DateTime givenDate = DateTime.Parse("11/14/22");
    TimeSpan givenTime = TimeSpan.Parse("9:00");
    TimeSpan givenEndT = TimeSpan.Parse("18:00");

    var qry = from x in _context.PostThrs
              join y in _context.PostFivs on x.ThrZero equals y.zero
              where
                x.ThrDate == givenDate &&
                x.ThrText == "SERVICE-" &&
                ((
                x.ThrTime < givenTime &&
                x.ThrEndT > givenTime
                ) || (
                x.ThrTime < givenEndT &&
                x.ThrEndT > givenEndT
                ) && y.FivCode == "V" && y.FivText == "VAN-" ) select y.Count();

this query will return your entire PostFivs data. and you can get count from y.Count()