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?

You cannot do it this way around. You have to know what you are doing, and than you can form a query from it. It's not possible to convert SQL into EF. Ef will form SQL - but not in the way you want, just in the way that works. So you have to know what you want to do. Start with defining your classes and Tables (DBSets) and then we talk again. — Holger, Dec 10 '19 at 18:26
Like I said, all tables and variable are already defined and exist.For instance, the table, tblASLStep2, is a DbSet already. As is the table, tblTableOfContentCriteria, and the table tblASLStep2_narratives. — JNickVA1, Dec 10 '19 at 18:35
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you start. There are also commercial translation products available. If you still have issues with specific queries, you can post a specific question. As mentioned, using EF instead of LINQ to SQL will involve more transformation and understanding. PS Your query post appears incomplete (can't end with `union all`). — NetMage, Dec 10 '19 at 19:12
The conditional part at the end looks buggy. If session.agencyType is "F", the command will end with `union all`. As you translate this to .net, you might want to look at that. — Dan Bracuk, Dec 10 '19 at 19:19
From James Njoroge, "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." — Dan Bracuk, Dec 11 '19 at 14:46

score 0 · Answer 1 · answered Dec 11 '19 at 08:11

0

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.

answered Dec 11 '19 at 08:11

James Njoroge

11
1
1

This is more of a comment than an answer. Since you don't have a high enough reputation to post comments, I did it for you. You can spare yourself some grief by deleting this answer. – Dan Bracuk Dec 11 '19 at 14:48

Convert a ColdFusion query to C# query expression

1 Answers1