Get result depending on between dates using linq-CodePudding

I have an Entity Framework query to return a list of salaries and its clients as:

public async Task<List<Salary>> GetSalaryByClientIdAndProfileId(int profileId, int clientId)
{
    var profileClientSalary = this._db.Salaries.Where(pc => pc.ProfileId == profileId && pc.ClientId == clientId).OrderByDescending(x => x.StartDate);
    return profileClientSalary.Include(pc => pc.Client).Where(p => p.ProfileId == profileId).ToList();
}

As you can see, I added Client Entity to the object, so my parent object has the property startDate and the Client entity has ClientName, ContractStartingDate, and ContractEndDate, I want to get on each parent the client name/s where parent startDate is between Client ContractStartingDate and ContractEndDate

So I have a foreach of the parent object as:

foreach(var salary in profileClientSalary)
{
    profile.Salaries.Add(new ProfileSalariesViewModel
    {
        StartDate = salary.StartDate,
        Customers = salary.Client.Name.Where(c => c.) 
        // Here I want to get client names where salary.startDate between Client.ContractStartingDate and Client.ContractEndDate
    });
}

Note: if the Client.ContractEndDate is null the client should be added to the list too

How can I achieve that? Regards

CodePudding user response：

I think what you're looking for is something like this

    profile.Salaries.Add(new ProfileSalariesViewModel
    {
        StartDate = salary.StartDate,
        Customers = salary.Client.Where(c => c.ContractStartingDate > StartDate && c.ContractEndDate < StartDate).Select(c => c.Name); 
    });

The issue with your original code is that you're iterating over the name when you really need to be iterating over the clients and building a list of those first based on your filtering criteria.

Once you have that list of clients, you can select the customer names from that list with another linq query Select(c => c.Name);

CodePudding user response：

Another way could be using EF.Functions for the date comparison, but it might be a bit overkill.

If you want to make up a date from multiple properties:

var filteredSalaries = profileClientSalary.Where(salary => EF.Functions.DateFromParts(salary.year, salary.month, salary.day) >= contractStartDate &&
                                                           EF.Functions.DateFromParts(salary.year, salary.month, salary.day) <= contractendDate);

Or Just:

var filteredSalaries = profileClientSalary.Where(salary => salary.startDate >= contractstartDate &&
                                    salary.startDate <= contractendDate);

Just change <= to < if you don't want to include the exact start and end date.

And finally, if you just want the names.

var clientNames = filteredSalaries.select(salaries => salaries.ClientName);