convert SQL query to LINQ statement using Linqpad

rajeshpatelkp

Does Linqpad support convert any SQL query to LINQ statement. I have below query and I like to convert in to Linq lambda query. I have sample query below which needs to be convert to Lambda quer.
SELECT incidents.*,
sub.incidents AS incidents_that_day
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN ( SELECT date,
COUNT(incidnt_num) AS incidents
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY 1
) sub
ON incidents.date = sub.date
ORDER BY sub.incidents DESC, time

Thanks

nescafe

I'm not sure if the subselect is intentional, but given the current selection and (no) filtering, you could easily use a window function no a OVER clause:

SELECT COUNT(*) OVER (PARTITION BY date) AS incidents_that_day, *
FROM tutorial.sf_crime_incidents_2014_01
ORDER BY 1 DESC, time

There is no SQL to LINQ converter, but you could do the LINQPad challenge ( http://www.linqpad.net/challenge.aspx ) and might end up with something like this.

from incident in sf_crime_incidents_2014_01
join sub in sf_crime_incidents_2014_01.GroupBy(x => x.date) on incident.date equals sub.Key
select new { incident, incidents_that_day = sub.Count() }