Create an Aggregate Query in VB Using LINQ

if you get the results in t-sql then create a stored procedure and then execute the procedure.

if you get the results in t-sql then create a stored procedure and then execute the procedure.

That is not what I am saying.  The table is not in SQL, it is an ADO table in VB.Net.  I am saying IF the table where in SQL, this is what the SQL code would look like.  I am trying to come up with the LINQ equivalent of that code, as I need to do this in LINQ.

Here is my best guess with a Lambda expression:

Since I think in C#:

 var results = PreImportData.AsEnumerable()
                .Select(x => new RecordEntry(x))
                .Where(x => !x.ClaimAdjustSequenceNumber.EndsWith("00"))
                .GroupBy(x => x)
                .Select(x => x.Key)
                .ToList();

Here is the translated equivalent:

Dim results = PreImportData.AsEnumerable()
     .[Select](Function(x) New RecordEntry(x))
     .Where(Function(x) Not x.ClaimAdjustSequenceNumber.EndsWith("00"))
     .GroupBy(Function(x) x)
     .[Select](Function(x) x.Key)
     .ToList()

Intermediate RecordEntry class:

  private class RecordEntry
        {
            public string SocialSecurityNumber { get; set; }
            public string ClaimAdjustSequenceNumber { get; set; }
            public int GroupId { get; set; }
            public int SubGroupId { get; set; }

            public RecordEntry(DataRow row)
            {
                SocialSecurityNumber = row["SSN"].ToString();
                ClaimAdjustSequenceNumber = row["ClaimAdjustSeqNum"].ToString();
                GroupId = int.Parse(row["GroupId"].ToString());
                SubGroupId = int.Parse(row["SubGroupId"].ToString());
            }
        }

Select allOpen in new window

VB.NET equivalant:

Private Class RecordEntry
	Public Property SocialSecurityNumber() As String
	Public Property ClaimAdjustSequenceNumber() As String
	Public Property GroupId() As Integer
	Public Property SubGroupId() As Integer

	Public Sub New(row As DataRow)
		SocialSecurityNumber = row("SSN").ToString()
		ClaimAdjustSequenceNumber = row("ClaimAdjustSeqNum").ToString()
		GroupId = Integer.Parse(row("GroupId").ToString())
		SubGroupId = Integer.Parse(row("SubGroupId").ToString())
	End Sub
End Class

Select allOpen in new window

Maybe it would be better to come at this from a different angle.
Here is an example of what my internal datatable may look like (actually has more fields, but they are not pertinent to what I am trying to do):

SSN      GroupID      SubGroupID      ClaimAdjustSeqNum
111111111      123456      0001      123456700
111111111      123456      0001      123456701
111111111      123456      0001      123456801
222222222      654321      0002      222333400
333333333      222333      0002      444222101
333333333      444777      0002      246812300
444444444      333666      0001      777999900
444444444      333666      0001      777900000

And this is what I should end up with:

SSN      GroupID      SubGroupID
111111111      123456      0001
333333333      222333      0002

Basically, it should return a single set of SSN, GroupID, SubGroupID if that combination contains any records where the ClaimAdjustSeqNum does not end in "00".

I have been scouring the web to see what I could adapt, and here is my latest attempt, but it does not seem to like my syntax:

        Dim results = From p In PreImportData.AsEnumerable
                        Where (Right(p.Field(Of String)("ClaimAdjustSeqNum"), 2) <> "00")
                        Group p By New{p.Field(Of String)("SSN"), p.Field(Of String)("GroupID"), p.Field(Of String)("SubGroupID")}
                        Into myGroup
                        Select New { myGroup.Key.SSN, myGroup.Key.GroupID, myGroup.Key.SubGroupID }

Select allOpen in new window

TheLearnedOne,
Just saw your reply.  I have never worked with Lambda expressions before.  In the VB.Net training class I took last year, they just kind of glossed over them, indicating they were a more complex, advanced programming tool.  So I am not sure how I would work my fields into that statement.

Not sure what I am supposed to do with the intermediate class, or how I incorporate it into my program to do what I need?

What is wrong with your LINQ query?

Something with the syntax.  In Visual Studio, when it is all written correctly, all the FROM, WHERE, GROUP, and OF STRING words are highlighted in blue, and the field names surrounded by double-quotes are highlighted in red.  The line containing the GROUP statement does not look correct:

Group p By New{p.Field(Of String)("SSN"), p.Field(Of String)("GroupID"), p.Field(Of String)("SubGroupID")}

Select allOpen in new window

In the first field referenced, p.Field(Of String)("SSN"), everything is all black.  The Of String is not blue, and the "SSN" is not red, like it should be (and is for the following two fields).

If I debug it, it goes to the left curly bracket and says "Type of 'With' expected.

This is my actual full block of code:

        Dim AdjustmentClaims As DataTable = ConversionData.Clone

        '   Identify all members with adjustment claims
        Dim results = From p In PreImportData.AsEnumerable
                        Where (Right(p.Field(Of String)("ClaimAdjustSeqNum"), 2) <> "00")
                        Group p By New{p.Field(Of String)("SSN"), p.Field(Of String)("GroupID"), p.Field(Of String)("SubGroupID")}
                        Into myGroup
                        Select New { myGroup.Key.SSN, myGroup.Key.GroupID, myGroup.Key.SubGroupID }


        '   Write results to Temp Table
        Dim amt As New CreateEnhancedMemberListStructure()
        AdjustmentMembers = amt.CreateTableStructure()

        For Each row In results
            AdjustmentMembers.ImportRow(row)
        Next

Select allOpen in new window

Once I get this list, I am trying to write the results to a table that just has these three fields (SSN, GroupID, SubGroupID).  It gives me another error, highlighting the word "results" in my For statement, saying "'results is not declared.  It may be inaccessible due to its protection level.".  Its odd, because we have been using this exact same technique to populate other tables with the results of our LINQ queries.

OK, since LINQ does not seem to be able to handle Grouping on multiple fields very well, maybe we can approach this a different way.  Instead, maybe we can allow it to return all the datarows, but maybe we can place a Primary Key or Constraint on the table that would not allow duplicate records in it (and we could add a "Try Catch" block to ignore those errors).

Here is what the class that creates the tables I will be writing the results to looks like:

Public Class CreateEnhancedMemberListStructure

    Public Function CreateTableStructure() As DataTable

        '   Create Table
        Dim AdjustmentMembers As DataTable
        AdjustmentMembers = New DataTable("AdjustmentMembers")

        ''   GroupID
        Dim GroupID As DataColumn = New DataColumn("GroupID")
        GroupID.DataType = System.Type.GetType("System.String")
        AdjustmentMembers.Columns.Add(GroupID)

        ''   SubGroupID
        Dim SubGroupID As DataColumn = New DataColumn("SubGroupID")
        SubGroupID.DataType = System.Type.GetType("System.String")
        AdjustmentMembers.Columns.Add(SubGroupID)

        '   SSN
        Dim SSN As DataColumn = New DataColumn("SSN")
        SSN.DataType = System.Type.GetType("System.String")
        AdjustmentMembers.Columns.Add(SSN)

        Return AdjustmentMembers

    End Function

End Class

Select allOpen in new window

So, how would I amend this table structure so that it does not allow duplicate records to be added when all three fields are taken into consideration (whether that be a mult-field Primary Key or some sort of Constraint)?

OK, this is in C#, but the concept is the same.
I've used the Fluent syntax because personally I prefer it.
For clarity I've broken the query down into 3 stages, but you could combine these if you wish:

The list that is selected from is just an IEnumerable of a class with the properties you describe.

	var filtered = list.Where(i => !i.ClaimAdjustSeqNum.EndsWith("00"));
	var grouped = filtered.GroupBy(i => new {  i.SSN, i.GroupID, i.SubGroupID });
	var result = grouped.Select( g => g.Key);

Select allOpen in new window

Try this:

        Dim results = From p In PreImportData.AsEnumerable()
                        Where Not p.Field(Of String)("ClaimAdjustSeqNum").EndsWith("00")
                        Group p By g = New With {.SSN = p.Field(Of String)("SSN"),
                                                 .GroupId = p.Field(Of String)("GroupID"),
                                                 .SubGroupId = p.Field(Of String)("SubGroupID")
                                                }
                        Into Group
                        Select New With {.SSN = g.SSN,
                                          .GroupId = g.GroupId,
                                          .SubGroupId = g.SubGroupId}

Select allOpen in new window

Mike Toole,
That has been one of my biggest causes of frustration.  I have found lots of stuff on-line that seems to have the correct concept, but the syntax doesn't work.  It seems to be written for C# or different versions of vb.  I am using vb.net in Visual Studio 2010.  So I have been unable to come up with anything that works.

The LearnedOne,
I tried the code you posted.  The good news is that it doesn't return any errors.  The bad news is, it doesn't seem to be doing what it is supposed to.  Namely, it is still returning duplicate records, based on those three fields (looking at the results in "results").

Another example of the syntax stuff that has been driving me crazy.  In my attempt to try to prevent duplicates using the table structure instead, I found some code that creates a multi-field Primary Key.  Great!  Only, it doesn't work!

Here is my code (which is supposedly for vb.net):

AdjustmentMembers.PrimaryKey = new { GroupID, SubGroupID, SSN }

Select allOpen in new window

I get an error on the left squiggly bracket saying "Type or 'With' Expected".

This is so frustrating!!!

I am not a VB.NET guy any more, so my guess is it would be:

AdjustmentMembers.PrimaryKey = New With { .GroupID, .SubGroupID, .SSN }

I am not a VB.NET guy any more, so my guess is it would be:

AdjustmentMembers.PrimaryKey = New With { .GroupID, .SubGroupID, .SSN }

Select allOpen in new window

Thanks for the effort, but that still returns the same error.