Problem translating LINQ from C# to VB.NET

Question

I have a LINQ query that works as expected in C#. I need to translate it to VB.NET.

Background here

None of the online translators give a version that would work

C# (working)

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

VB.NET(translated from above) not working: it returns empty:

Dim result = From r1 In rows Join r2 In rows On
                             New With {r1.MeterIdentifier, r1.Timestamp} Equals
                             New With {r2.MeterIdentifier, r2.Timestamp}
                                              Group r1.BillNo By __groupByKey1__ = r1.BillNo Into g = Group
                                              Select New With
                                                    {
                                                        .BillNo = __groupByKey1__,
                                                        .Count = g.Count()
                                                    }

            Dim test = result.ToList() ' list empty

Telerik's translator gives a very slightly different code but that is not even compiling.

I am pretty limited with LINQ, can anyone spot if that translations is wrong/how to fix it?

Thanks a lot

Answer 1

From the background

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

I think I'd just group by ImportCode and get those where Min fromDate not equals Max fromDate.. it'd give the bill numbers too

data _
  .GroupBy(Function(x) x.ImportCode) _
  .Where(Function(g) g.Select(Function(gg) gg.FromDate).Min() <> g.Select(Function(gg) gg.FromDate).Max()) 

Or with a multiline lambda

data _
  .GroupBy(Function(x) x.ImportCode) _
  .Where(Function(g) _
    Dim h = g.Select(Function(gg) gg.FromDate) _
    Return h.Min() <> h.Max() _
  End Function) 

What you're getting out of this is a bunch of groupings, which is essentially a list A of list B of the original objects.

Make sense? No.. OK

Imagine this is a single list of MeterReading(?) objects like (i've omitted the BillNo...):

ImportCode  Date
A           1-Jan
A           1-Jan
A           2-Jan
B           1-Jan
B           2-Jan
B           3-Jan
C           1-Jan

After grouping by ImportCode, it becomes a list of list of meterreadings like (JSON flavored rendering):

Key            (items list - a bunch of MeterReading)
A              [{A,1-Jan},{A,1-Jan},{A,2-Jan}]
B              [{B,1-Jan},{B,2-Jan},{B,3-Jan}]
C              [{C,1-Jan}]

If you were to say Where(Function(items) ...) then the code in the ... in the Where is run once per grouped row above - there are 3 rows so the code is called 3 times. A single row is an items - it has a .Key property (that I don't use) that is the itemcode that was grouped on, and the whole items thing itself is a list (ienumerable, to be exact) of the input MeterReading objects that went into the grouping operation, so in other words, items is a list of MeterReading that all have the same ImportCode, and there are 3 such lists

If you Select the FromDate out of each MeterReading in the items and call Max then you get the max date, same for min. If they're inequal then the items list contains some meter readings with different dates - you say they should be the same. For lists headed by item codes A and B, the min date is different to the max date. For the C they're all the same so C disappears from the results and we're down to 2 rows

Key            (items list - a bunch of MeterReading)
A              [{A,1-Jan},{A,1-Jan},{A,2-Jan}]
B              [{B,1-Jan},{B,2-Jan},{B,3-Jan}]

The output from Where is "two rows", each with a full list of all the meter readings and their associated data. You can pull that data in various ways, for example:

.Select(Function(meterReadingsList) _
  New With { _
    .DistinctDayCount = meterReadingsList.Select(Function(mr) mr.FromDate).Distinct().Count() _ 'the number of different days this happened on
    .BillNos = meterReadingsList.Select(Function(mr) mr.BillNo).ToArray() _
 } _
End Function)

There's also a SelectMany operation in linq that can be used to flatten a List Of List Of Something into a List Of Something(else); kinda like the reverse of the GroupBy done earlier. If you were to SelectMany the meterreadings in those two rows, they'd come back out to a single list like what we started with..

ImportCode  Date
A           1-Jan
A           1-Jan
A           2-Jan
B           1-Jan
B           2-Jan
B           3-Jan

But from what you say about wanting counts per item code etc it seems like theyre better off staying with their 2D representation