How can I write a Linq query that returns rows that are in a specific period of dates?

Question

I am using EF Core 7 and C#. In the model, one Employee has many Periods and many Tickets.

I have the following classes:

public class Employee
{    
    public string EmployeeId { get; set; }    
    public string FullName { get; set; }

    public List<Period> Periods { get; set; }
    public List<Ticket> Tickets { get; set; }
}

public class Period
{
    public int PeriodId { get; set; }  
    
    public DateTime From { get; set; }
    public DateTime To { get; set; }
    
    public Employee Employee { get; set; }
    public string EmployeeId { get; set; } //FK pointing to Employee
}

public class Ticket
{ 
    public string TicketId { get; set; }        
    
    public DateTime CreationDate { get; set; }
   
    public string EmployeeId { get; set; } //FK pointing to Employee
}

How can I write a linq query that returns the tickets that are in the range of period of the employees? I need the result as a IQueryable<Ticket>.

Here is the query I was trying:

// I create a list of employees Id's
var employeeIds = new List<string> { "9854", "4587", "2587" };

IQueryable<Ticket> result =
     from ticket in DbContext.Tickets
     where employeeIds.Contains(ticket.EmployeeId) &&
     
           // Here I need to set the condition for tickets
           // that are inside the Period of each employee
           // [Period.From , Period.To] 
     
     select ticket;
}

Answer 1

This should be what you want:

IQueryable<Ticket> result = 
     from ticket in DbContext.Tickets
      where employeeIds.Contains(ticket.EmployeeId)  
        && DbContext.Periods.Any(p => p.EmployeeId == ticket.EmployeeId 
            && ticket.CreationDate >= p.From && ticket.CreationDate <= p.To)
    select ticket;

So i check if there is at least one period for each ticket-employee which from/to are in the ticket's CreationDate range.