SolvedPrivate

Create an Aggregate Query in VB Using LINQ

Posted on 2014-04-02
18
35 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 79

Expert Comment

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

Author Comment

by:JoeMiskey
ID: 39974844
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
ID: 39975207
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 39975208
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
ID: 39975215
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
ID: 39975220
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
ID: 39975237
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
ID: 39975264
What is wrong with your LINQ query?
0
 

Author Comment

by:JoeMiskey
ID: 39975355
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
ID: 39976558
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
ID: 39976612
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
ID: 39976655
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
ID: 39978369
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
ID: 39978827
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
ID: 39978884
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
ID: 39978975
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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