Convert a ColdFusion query to C# query expression

Question

I am converting a legacy Cold Fusion application to ASP.NET Core. The application is very data intensive and the Cold Fusion code makes use of embedded SQL queries. I need to translate several of the Cold Fusion queries into .NET query expressions. For example, the following is a typical Cold Fusion query:

SELECT
  s2.CriteriaID, s2.Response, s2.NA, s2.Status, s2.resubStatus, s2.staffDetermination, s2.resubStaffDetermination,
  c.CriteriaDesc<cfif getAgency.agencyType NEQ "N">Short</cfif> criteriaDesc, ' ' long_desc
FROM tblASLStep2 s2 
  left outer join tblTableOfContentCriteria c on c.CriteriaNo = s2.criteriaID and c.meetingID = s2.meetingDate
where
  s2.AgencyID = #session.AgencyID#
  and s2.MeetingDate=#session.MeetingDate#
  and (
    (s2.response is not null and ltrim(cast(s2.response as nvarchar)) <> '')
    or exists (
      SELECT 1
      FROM tblASLStep2_narratives n
      WHERE
        n.AgencyID = '#session.AgencyID#'
        and n.CriteriaID = s2.criteriaID
        and n.MeetingDate = #session.MeetingDate#
        and n.narrativeType = 'R'
        and n.response is not null
    )
  )
<cfif session.agencyType neq "F">
  order by replace(replace(s2.CriteriaID, 'ix', 'viiii'), 'Agency Narrative', 1)
<cfelse>
  and s2.criteriaID not like 'SEC5%'
  union all
</cfif> 

I can get as far as the 'from' and the SELECT, but the LEFT OUTER JOIN and then the conditional statement parts is throwing me for a loop. Assuming that the table and all other variables exist, what would be the best approach for converting this to a .NET query expression?

Answer 1

if you can run the legacy system, fire up SQL Server profiler and run the application from the profiler you can capture the queries that are being send to the database.