SQL Conversion - LINQ - Problem Using Like Min / Max

Question

Is there an online system that converts SQL-LINQ, or can someone else help convert SQL-LINQ below?

SELECT MIN(startTime) As startTime, MAX(endTime) As endTime
FROM tblRA
LEFT JOIN tblA ON tblRA.asID = tblA.asID
WHERE 'xxxxxx' BETWEEN tblRA.startDate AND tblRA.endDate
AND tblA.availabilityDayOfWeek = 7

The main problem I encountered is .MAX / .MIN.

That's what i still have

    public List<string> GetResourceAvailabilitiesByDate(DateTime searchDate)
    {
        DayOfWeek dayOfWeek = searchDate.DayOfWeek;

        var minVal = from a in dc.tblResourceAvailabilities
                join b in dc.tblAvailabilities on a.asID equals b.asID 
                where searchDate.Date >= a.startDate.Date && searchDate.Date <= a.endDate.Value.Date
                && b.availabilityDayOfWeek == (int)dayOfWeek
                select b.startTime.ToShortTimeString();;

        var maxVal = from a in dc.tblResourceAvailabilities
                     join b in dc.tblAvailabilities on a.asID equals b.asID
                     where searchDate.Date >= a.startDate.Date && searchDate.Date <= a.endDate.Value.Date
                     && b.availabilityDayOfWeek == (int)dayOfWeek
                     select b.endTime.ToShortTimeString();

        var min = minVal.Min(minVal.Min);
        var max = maxVal.Max();

        return min,max;

Thank you in advance for your help.

Clare

Answer 1

I think your code is a little flawed, and its first symptom is that you use duplicate code to determine minval and maxval. I tried to simulate something similar to what you want, and came to the next code, tailoring it to your needs.

    public List<string> GetResourceAvailabilitiesByDate(DateTime searchDate)
    {
        DayOfWeek dayOfWeek = searchDate.DayOfWeek;

        var vals  = from a in dc.tblResourceAvailabilities
            join b in dc.tblAvailabilities on a.asID equals b.asID 
            where searchDate.Date >= a.startDate.Date && searchDate.Date <= a.endDate.Value.Date
            && b.availabilityDayOfWeek == (int)dayOfWeek
            select b;

        var min = vals.Min(v => v.startTime).ToShortTimeString();
        var max = vals.Max(v => v.startTime).ToShortTimeString();

        return new List<string>() { min, max };
     }

Some comments on your code, considering it C #.

• , .
• where . startdate.Date endDate.Value.Date. .
• select b, . .