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() }

RameshT

SELECT
data.RfcMasterDataId,
data.Oem,
data.Region,
data.Segment,
rf.RfcMonthIdentifier,
sum (rv.RfcValue*rf.Price) Volume,
rs.RfcSourceTypeId,
rs.SourceTypeName
FROM
rfc.RfcMasterData data
INNER JOIN
rfc.RfcDetails rf on data.RfcMasterDataId = rf.RfcMasterDataId
INNER JOIN
rfc.RfcVolumeData rv on rf.RfcDetailsId = rv.RfcDetailsId
INNER JOIN
rfc.RfcSourceType rs on data.RfcSourceTypeId = rs.RfcSourceTypeId
INNER JOIN
rfc.RfcReportVersion e on data.RfcReportVersionId = e.RfcReportVersionId
WHERE
e.RfcVersionNumber = 'RFC0323' and e.RfcReportStatusId =1
GROUP BY
data.RfcMasterDataId,
data.Oem,
data.Region,
data.Segment,
rf.RfcMonthIdentifier,
rs.RfcSourceTypeId,
rs.SourceTypeName
ORDER BY
data.RfcMasterDataId,
rf.RfcMonthIdentifier