Linq to SQL Converstion RRS feed

  • Question

  • Select c1.CheckIssueDate, sum(c1.EOBCount), SUM(c1.Payment)
    SELECT RemitBundles.CheckIssueDate, count(ClaimAdjudications.ClaimAdjudicationsKey) AS EOBCount, RemitBundles.TotalActualProviderPaymentAmount as Payment
    FROM ClaimAdjudications INNER JOIN
    RemitBundles ON ClaimAdjudications.RemitBundlesKey = RemitBundles.RemitBundlesKey
    WHERE (RemitBundles.CIN = '00779')
    GROUP BY RemitBundles.CheckIssueDate, RemitBundles.TotalActualProviderPaymentAmount) as c1
    group by c1.CheckIssueDate
    I have this statement above that I need to convert to Linq, this is what I have the sum is way off because et sums up for each child table row:

                    var clms = (from p in contextBatch.RemitBundles
    join o in contextBatch.ClaimAdjudications on p.RemitBundlesKey equals o.RemitBundlesKey
    where p.Cin == "00779"
    //orderby p.CheckIssueDate
    group p by p.CheckIssueDate
    into g
    select new
    Date = g.Select(v => v.CheckIssueDate).FirstOrDefault(),
    EOBCount = g.Select(v => v.RemitBundlesKey).Count(),
    AmountPaid = g.Sum(v => v.TotalActualProviderPaymentAmount)
    the date and the count are fine, but the amount paid is not fine. Please help, thanks
    Monday, September 21, 2009 8:16 PM


  • Hi Fahd,


    Considering there's no data table structure provided, I wrote the following Linq statement for you based on your SQL statement. It works fine and returns the same result as the SQL statement does. Check to see if it meets your needs.

    Suppose that 'dc' is the DataContext, the same as your 'contextBatch'.

                var clms = from p4 in
                               (from p in
                                    (from r in dc.RemitBundles
                                     join c in dc.ClaimAdjudications
                                     on r.RemitBundlesKey equals c.RemitBundlesKey
                                     where r.CIN == "00779"
                                     select new { CheckIssueDate = r.CheckIssueDate, ClaimAdjudicationsKey = c.claimAdjudicationsKey, TotalActualProviderPaymentAmount = r.TotalActualProviderPaymentAmount })
                                group p by new { p.CheckIssueDate, p.TotalActualProviderPaymentAmount }
                                    into p1
                                    from p2 in p1
                                    select new { CheckIssueDate = p2.CheckIssueDate, EOBCountr = (from p3 in p1 where p3.ClaimAdjudicationsKey == p2.ClaimAdjudicationsKey select p3).Count(), TotalActualProviderPaymentAmount = p2.TotalActualProviderPaymentAmount })
                           group p4 by p4.CheckIssueDate
                               into p5
                               from p6 in p5
                               select new { CheckIssueDate = p6.CheckIssueDate, sum1 = (from p7 in p5 where p7.CheckIssueDate == p7.CheckIssueDate select p7.EOBCountr).Sum(), sum2 = (from p7 in p5 where p7.CheckIssueDate == p7.CheckIssueDate select p7.TotalActualProviderPaymentAmount).Sum() };
                foreach (var c in clms)
                    Console.WriteLine ("{0} -- {1} -- {2}",c.CheckIssueDate,c.sum1,c.sum2);

    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Zhipeng Lee Monday, September 28, 2009 1:22 AM
    Wednesday, September 23, 2009 7:41 AM