SolvedPrivate

Create an Aggregate Query in VB Using LINQ

Posted on 2014-04-02
18
24 Views
Last Modified: 2016-06-20
I am trying to create an Aggregate Query in VB.Net using LINQ.  Basically, I have a list of Claims, with many adjustments mixed in.  I am trying to return all the unique members who have had adjustments (what makes a member unique is GroupID, SubgroupID, SSN).  Even though these members may have a number of adjustments, I only want each GroupID, SubgroupID, SSN combination listed once.

I am quite versed in SQL, but fairly new to LINQ.  In SQL, the query would look something like this:
SELECT
	SSN,GroupID,SubGroupID
FROM
	PreImportData
WHERE
	RIGHT(ClaimAdjustSeqNum,2)<>'00'
GROUP BY
	SSN,GroupID,SubGroupID

Open in new window

I am struggling a bit in finding the correct syntax for LINQ, specifically the "Grouping" part.  Here is an attempt I had made (based on some code I found on the net), but it obviously only is considering the SSN field, and not the other two fields I need.
        Dim results = From p In PreImportData.AsEnumerable
                        Where Right(p.Field(Of String)("ClaimAdjustSeqNum"), 2) <> "00"
                        Select p.Field(Of String)("SSN").Distinct()

Open in new window

Can anyone help me get the correct LINQ syntax?

Thanks
0
Comment
Question by:JoeMiskey
18 Comments
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
if you get the results in t-sql then create a stored procedure and then execute the procedure.
0
 

Author Comment

by:JoeMiskey
Comment Utility
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.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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()
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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());
            }
        }

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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

Open in new window

0
 

Author Comment

by:JoeMiskey
Comment Utility
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 }

Open in new window

0
 

Author Comment

by:JoeMiskey
Comment Utility
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?
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
What is wrong with your LINQ query?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:JoeMiskey
Comment Utility
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")}

Open 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

Open 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.
0
 

Author Comment

by:JoeMiskey
Comment Utility
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

Open 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)?
0
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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:
Filter for not a 00 suffix
Group on SSN, GroupID, SubGroupID
Select just the keys from the generated groups, which are an anonymous type with the GroupBy properties

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);

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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}

Open in new window

0
 

Author Comment

by:JoeMiskey
Comment Utility
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 }

Open in new window

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

This is so frustrating!!!
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
I am not a VB.NET guy any more, so my guess is it would be:

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

Author Comment

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

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

Open in new window

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

Accepted Solution

by:
JoeMiskey earned 0 total points
Comment Utility
I figured it out!!!

This is the syntax I need to create a multi-field primary key:
        AdjustmentMembers.PrimaryKey = New DataColumn() {GroupID, SubGroupID, SSN}

Open in new window


So, then I used this code to select all my records:
        '   Identify all members with adjustment claims
        Dim results = From p In PreImportData.AsEnumerable
                        Where (Right(p.Field(Of String)("ClaimAdjustSeqNum"), 2) <> "00")
                        Select mem = p.Field(Of String)("SSN"), co = p.Field(Of String)("GroupID"), div = p.Field(Of String)("SubGroupID")

Open in new window


And then use this code to write the records to a datatable:
            '   Loop through list and add records to table
            For Each row In results
                Dim foo = AdjustmentMembers.NewRow
                foo("SSN") = row.mem
                foo("GroupID") = row.co
                foo("SubGroupID") = row.div
                '   Add a try catch so if it tries to add the same record twice, it ignores the subsequents ones
                Try
                    AdjustmentMembers.Rows.Add(foo)

                Catch ex As System.Data.ConstraintException
                    ' Do nothing - we're intentionally skipping the addition of duplicate values

                Catch ex As Exception
                    MessageBox.Show(ex.Message + vbCrLf + ex.ToString, _
                                    My.Application.Info.Title, _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Error)

                End Try
            Next

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now