Home

convert SQL query to LINQ statement using Linqpad

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

Comments

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

Sign In or Register to comment.