Convert SQL - LINQ - Problem with using both Min/Max

Question

Hello all,

Is there a online system which converts SQL - LINQ or can anyone else help convert the 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 area I am having trouble is the .MAX/.MIN.

Heres what I have so far

    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;

Thanks in advance for any help

Clare

Answer 1

Try this: http://www.sqltolinq.com/

Answer 2

I didn't find an example in the end, so decided to do it within a stored procedure.

Clare

Answer 3

You may find the Linq in Action book helpful.

Answer 4

I think your code is a little bit incorrect, and the first symptom of it is that you are using repeated code to define minval and maxval. I tried to simulate something similar to what you want and came to the following code, please adapt 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, assuming it's C#.

1. You are trying to return an array of strings when you should be returning an array of dates.
2. Your where clause is pretty confuse. You're comparing the search date with startdate.Date and endDate.Value.Date. It does not make much sense.
3. Your select clause could select only b, or a, or whatever. You don't really need to select the date in it.