1

I am trying to fetch the results from the DB using the Entity Framework using the below SQL query:

    SELECT
    Header_Id, 
    Header_Number, Details_Id, 
    Details_Header_Date, 

    (SELECT TOP 1 c.[Comment_Description] FROM [Comment] c 
        WHERE c.[Comment_CarrierId] = i.[Header_CarrierId] AND c.[Comment_ParentEntityId] = i.[Header_Id] 
            ORDER BY c.Comment_AddedOn DESC)                                                                    AS 'HeaderComments',

    CONCAT(ht.[HeaderTracker_Reason], ' ', ht.[HeaderTracker_Notes])                                            AS 'RejectedComments',

    at.[InternalComments]                                                                                       AS 'InternalComments',

    CASE i.[Header_Status] 
            WHEN 'Pending' THEN  ar.[HeaderTracker_ApprovedLvl] END                                             AS 'ApprovedLevel',
    CASE i.[Header_Status] 
            WHEN 'Pending' THEN  ar.[HeaderTracker_DueBy]   END                                                 AS 'ApprovedDueDate'

FROM [Header] i
    LEFT OUTER JOIN [Details] ii
        ON i.[Header_Id] = ii.[Details_HeaderId]
    INNER JOIN [Carrier] t
        ON t.[Carrier_Id] = i.[Header_CarrierId]
    LEFT OUTER JOIN [HeaderTracker] ht 
        ON ht.[HeaderTracker_HeaderId] = i.[Header_Id] AND ht.[HeaderTracker_Type] = 'Rejected'
    LEFT OUTER JOIN [AdjustmentTracker] at
        ON at.[DetailsId] = ii.[Details_Id]
    LEFT OUTER JOIN [HeaderTracker] ar 
        ON ar.[HeaderTracker_HeaderId] = i.[Header_Id] 
            AND i.[Header_Status] IN ('Paid', 'Pending', 'Approved', 'PaidWithAdj')
            AND ar.[HeaderTracker_IsPending] = 1    

The result output should be using LINQ query / LAMBDA expression

The following code is what I tried: (not sure how TOP 1 and CASE would come here + if & is also valid here)

    var statuses = new string[] { "Paid", "Pending", "Approved", "PaidWithAdj" };
var hdrdet = 
    (
        from hdr in dbContext.Headers
            join det in dbContext.Details on hdr.Header_Id equals det.Header_Id into hdrdet
            from h in hdrdet.DefaultIfEmpty()
            join carr in dbContext.Carriers on hdr.Carrier_Id equals carr.Header_Carrier_Id into carrs
            from c in carrs.DefaultIfEmpty()
        join hdrtk in dbContext.HeaderTracker on hdr.Header_Id equals hdrtk.HeaderTracker_HeaderId && hdrtk.HeaderTracker_Type equals "Rejected" into hdrtks
            from k in hdrtks.DefaultIfEmpty()
        join adjt in dbContext.AdjustmentTracker on adjt.DetailsId equals det.Details_Id into adjts
            from a in adjts.DefaultIfEmpty()
        join pnd in dbContext.HeaderTracker on pnd.HeaderTracker_HeaderId equals hdr.Header_Id && hdr.Header_Status contains(statuses) && pnd.[HeaderTracker_IsPending] equals 1 into pnds
            from p in pnds.DefaultIfEmpty()
            select new 
        { 
            hdrid = Header_Id, 
                hdrnumber = Header_Number, Details_Id, 
                det_date = Details_Header_Date, 
            reason = HeaderTracker_Reason + ' ' + HeaderTracker_Notes,
            intcomments = InternalComments
        }
    )
user1234
  • 11
  • 2

0 Answers0