Need help in converting SQL query to LINQ

Question

I am new to the world of LINQ and hence I am stuck at converting one sql query to LINQ. My SQL query is:

select COUNT(DISTINCT PAYER) as count, 
       PPD_COL FROM BL_REV 
where BL_NO_UID = 1084 
GROUP BY PPD_COL

The desired output is:

 Count   PPD_COL
  12       P
  20       C

I have written something like below in LINQ:

 var PayerCount = from a in LstBlRev where a.DelFlg == "N"
                  group a by new { a.PpdCol} into grouping
                  select new
                  {
                      Count = grouping.First().PayerCustCode.Distinct().Count(),
                      PPdCol = (grouping.Key.PpdCol == "P") ? "Prepaid" : "Collect"
                  };

But it is not giving me the desired output. The count is returned same for PPD_COL value P & C. What am I missing here?

Gilad Green · Accepted Answer · 2017-02-02 07:13:20Z

1

Change the groupby as following. in the group group only the property you need and then in thr by no need to create an anonymous object - just the one property you are grouping by.

var PayerCount = from a in LstBlRev 
                 where a.DelFlg == "N"
                 group a.PayerCustCode by a.PpdCol into grouping
                 select new
                 {
                     Count = grouping.Distinct().Count(),
                     PPdCol = grouping.Key == "P" ? "Prepaid" : "Collect"
                 };

answered Feb 2, 2017 at 7:13

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need help in converting SQL query to LINQ

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related