SQL to LINQ Conversion: Retrieving Container Data

This article showcases the process of converting a SQL query into its corresponding LINQ expression for retrieving container data. The original SQL query involves multiple joins, filtering, and conditional logic for selecting specific data points related to containers. Here's the SQL query:

SELECT
	CTNPRES.CTNREF,
	CTNPRES.CTNNO,
	DCTNREC.RSUCD,
	CTNPRES.INYARD AS YARD,
	DCTNREC.ESTNO,
	CTNPRES.CTNLOC,
	CTNPRES.CTNOPE,
	DCTNREC.WWLB,
	DCTNREC.OPECFCD,
	DCTNREC.REPEDDT,
	CASE CTNPRES.REMARK WHEN 'AV' THEN (SELECT TOP 1 JBPLSDT FROM MVJBPL WHERE CTNREF = CTNPRES.CTNREF AND REMARK = 'AV' ORDER BY JBPLSDT DESC) ELSE DCTNREC.REPEDDT END DT
FROM
	CTNPRES 
	INNER JOIN DCTNREC ON CTNPRES.CTNREF = DCTNREC.CTNREF 
WHERE
	DCTNREC.SURPCACD = '4'
	AND DCTNREC.OWNCFCD IN ( '1', '2', '3' )
	AND DCTNREC.REPEDDT IS NOT NULL
	AND DCTNREC.REPEDDT >= '2023-04-21 00:00' 
	AND DCTNREC.REPEDDT <= '2023-04-25 11:01' 
	AND CTNPRES.CTNNO= 'OOLU6897794'
ORDER BY
	DCTNREC.REPEDDT DESC

The equivalent LINQ query in C# is as follows:

var query = from ctnpres in context.CTNPRES
            join dctnrec in context.DCTNREC on ctnpres.CTNREF equals dctnrec.CTNREF
            where dctnrec.SURPCACD == '4'
                  && (dctnrec.OWNCFCD == '1' || dctnrec.OWNCFCD == '2' || dctnrec.OWNCFCD == '3')
                  && dctnrec.REPEDDT != null
                  && dctnrec.REPEDDT >= new DateTime(2023, 4, 21)
                  && dctnrec.REPEDDT <= new DateTime(2023, 4, 25, 11, 1, 0)
                  && ctnpres.CTNNO == 'OOLU6897794'
            orderby dctnrec.REPEDDT descending
            select new 
            {
                ctnpres.CTNREF,
                ctnpres.CTNNO,
                dctnrec.RSUCD,
                YARD = ctnpres.INYARD,
                dctnrec.ESTNO,
                ctnpres.CTNLOC,
                ctnpres.CTNOPE,
                dctnrec.WWLB,
                dctnrec.OPECFCD,
                DT = ctnpres.REMARK == 'AV' ? (from mvjbpl in context.MVJBPL 
                                                where mvjbpl.CTNREF == ctnpres.CTNREF && mvjbpl.REMARK == 'AV' 
                                                orderby mvjbpl.JBPLSDT descending 
                                                select mvjbpl.JBPLSDT).FirstOrDefault() 
                                             : dctnrec.REPEDDT
            };

This LINQ query efficiently replicates the functionality of the SQL query, offering a more readable and maintainable approach for data access within the application. The query uses LINQ's expressive syntax to join tables, filter data based on multiple criteria, and even employ conditional logic for selecting the appropriate date field. The use of anonymous types within the select clause allows for a concise representation of the data structure being retrieved.