How to output Property not included in group by and its count

user_854328 0
Viewed 48

what I am trying to achieve here is that I want from that LINQ query to return the list with two poperties: billNo, and number of occurences of the importcode on the same fromDate.

So here we have a billNo 1 and 2 both have the same importcode which appears in two rows on the same date (01/01/2020) thus count is 2.

If it helps to clarify, think of it as import code should only cover one distinct fromDate. If it appears multiple time, I would like to see how many times (count) and which BillNo s are like that.

So expected result for dataset below would be:

BillNo Count
1 2
2 2
3 1
4 1
5 1

I struggle to figure out how to select BillNo if it is not used for grouping.

Thanks a lot for helping.

var rows = new List<ImportRow>()
            {
                new ImportRow {billNo= 1, importCode = "one", fromDate = new DateTime(2020, 1, 1)},
                new ImportRow {billNo= 2, importCode = "one", fromDate = new DateTime(2020, 1, 1)},
                new ImportRow {billNo= 3, importCode = "two", fromDate = new DateTime(2020, 1, 1)},
                new ImportRow {billNo= 4, importCode = "two", fromDate = new DateTime(2020, 2, 1)},
                new ImportRow {billNo= 5, importCode = "one", fromDate = new DateTime(2020, 3, 1)}
            };

public class ImportRow : IEnumerable
    {
        public int billNo { get; set; }
        public string importCode { get; set; }
        public DateTime fromDate { get; set; }

        public IEnumerator GetEnumerator()
        {
            throw new NotImplementedException();
        }
    }



   var billNosWithCounts = rows.GroupBy(info => new { info.importCode,
     info.fromDate }) 
                             .Select(group => new
                             {                             
                                 FromDate = group.Key.fromDate,
                                 Count = group.Count()                            
                             });
linq c#
2 Answers

You can join the source onto itself, I believe that will give you the output you are looking for:

var result = from r1 in rows
             join r2 in rows 
                on new { r1.importCode, r1.fromDate } equals 
                    new { r2.importCode, r2.fromDate }
             group r1.billNo by r1.billNo into g
             select new
             {
                 BillNo = g.Key,
                 Count = g.Count()
             };
user_16630010

You could potentially achieve what you are looking for by doing the following:

public static IDictionary<int, string> Map(this List<Sample> collection)
{
     var kvp = new Dictionary<int, string>();
     var records = collection.GroupBy(value => new { value.Code, value.Date });
     foreach(var record in records)
          foreach(var contents in record)
               kvp.Add(contents.Bill, contents.Count());
}

A sample of the object you outlined above:

    var collection = new List<Sample>() 
    {
        new Sample() { Bill = 1, Code = "ABC", Date = DateTime.Now.ToShortDateString() },
        new Sample() { Bill = 2, Code = "ABC", Date = DateTime.Now.ToShortDateString() },
        new Sample() { Bill = 3, Code = "ABCD", Date = DateTime.Now.ToShortDateString() },
        new Sample() { Bill = 4, Code = "ABCDE", Date = DateTime.Now.AddDays(-3).ToShortDateString() }
    };

In essence, I created a method that will let you pass an object. It will then group on the import code and the date. Creating the unique pair you desire. Then I simply add the newly transformed content into a Dictionary. You could use Linq to do the full transform, but it might become more difficult to read or understand, so I chose to simplify into basic loops for the transformation.

user_12885890
Related