Linq Complex Query Search between Dates

Question

I have this Model that hold list months Jan - Dec. I want user to be set start day of the logs example 26 Dec to Jan 25 or Jan 1 - Jan 31

 public class SalaryMonth
    {
        public int Id { get; set; }

        public int StartMonth { get; set; }
        public int StartDay { get; set; }

        public int EndMonth { get; set; }
        public int EndDay { get; set; }

        public string Name { get; set; }

    }

How do I get the SalaryMonth based on the date supplied and but be one month this my business logic which always return null

namespace Contracts.Attendances
{


    public interface IAttendanceService
    {       
        Task<SalaryMonth> GetASalaryMonthByDate(DateTime date);

    }

     public class AttendanceService : IAttendanceService
    {

        /.../

        public async Task<SalaryMonth> GetASalaryMonthByDate(DateTime date) {
        var day = date.Day;
            var month = date.Month;
            var result = await _context.SalaryMonths.
                .FirstOrDefaultAsync(p => (p.StartMonth == month && p.StartDay >= day && p.StartDay <= day) 
                                          || ( p.EndMonth == month && p.EndDay >= day && p.EndDay <= day));

            return result;
        }

    }
}

Json from Database

{
    "SalaryMonth": [
        {
            "Id": "3",
            "StartMonth": "2",
            "StartDay": "26",
            "EndMonth": "3",
            "EndDay": "25",
            "Name": "March"
        },
        {
            "Id": "4",
            "StartMonth": "3",
            "StartDay": "26",
            "EndMonth": "4",
            "EndDay": "25",
            "Name": "April"
        },
        {
            "Id": "5",
            "StartMonth": "4",
            "StartDay": "26",
            "EndMonth": "5",
            "EndDay": "25",
            "Name": "May"
        },
        {
            "Id": "6",
            "StartMonth": "5",
            "StartDay": "26",
            "EndMonth": "6",
            "EndDay": "25",
            "Name": "June"
        },
        {
            "Id": "7",
            "StartMonth": "6",
            "StartDay": "26",
            "EndMonth": "7",
            "EndDay": "25",
            "Name": "July"
        },
        {
            "Id": "8",
            "StartMonth": "7",
            "StartDay": "26",
            "EndMonth": "8",
            "EndDay": "25",
            "Name": "August"
        },
        {
            "Id": "9",
            "StartMonth": "8",
            "StartDay": "26",
            "EndMonth": "9",
            "EndDay": "25",
            "Name": "September"
        },
        {
            "Id": "10",
            "StartMonth": "9",
            "StartDay": "26",
            "EndMonth": "10",
            "EndDay": "25",
            "Name": "October"
        },
        {
            "Id": "11",
            "StartMonth": "10",
            "StartDay": "26",
            "EndMonth": "11",
            "EndDay": "25",
            "Name": "November"
        },
        {
            "Id": "12",
            "StartMonth": "11",
            "StartDay": "26",
            "EndMonth": "12",
            "EndDay": "25",
            "Name": "December"
        },
        {
            "Id": "2",
            "StartMonth": "2",
            "StartDay": "26",
            "EndMonth": "2",
            "EndDay": "25",
            "Name": "February"
        },
        {
            "Id": "1",
            "StartMonth": "12",
            "StartDay": "26",
            "EndMonth": "1",
            "EndDay": "25",
            "Name": "January"
        }
    ]
}

How to I query the record to allow for users to get Salary month from supplied date. Example user provides any date between 26/12/2019 to 26/01/2020 the result must January since that date is in Jan Month because Jan StartMonth is 12 and StartDate 26 and EndMonth is 1 and EndDate 25

Answer 1

So I think I know what you’re trying to do. And if I understand correctly then your logic is a little mixed up in your linq call. Given any day, that day cannot be less than or equal to a date and greater than or equal to that same date unless it is the same date.

So you need to do:

C# Code:

.FirstOrDefaultAsync(p => (p.StartMonth == month && p.StartDay <= day) || ( p.EndMonth == month && p.EndDay >= day ));

Hope this helps.