R Sharp R Sharp - 5 months ago 30
Vb.net Question

Remove Duplicate Objects from list in VB.Net

I am trying to remove duplicate object from a list based on the companyID.

How do I integrate through a list and remove the object based on a companyID.

While reader.Read()
companys.Add(New CompanySearch)
companys(companys.Count - 1).StartDate = reader("StartDate").ToString & " (" & count & ")"
companys(companys.Count - 1).CompanyID = reader("company").ToString
companys(companys.Count - 1).Origin = reader("Origin").ToString
companys(companys.Count - 1).OriginName = reader("OriginName").ToString
companys(companys.Count - 1).Status = reader("status").ToString
companys(companys.Count - 1).StatusName = reader("statusname").ToString
companys(companys.Count - 1).Status = reader("status").ToString
companys(companys.Count - 1).FullLegalBusinessName = reader("fullLegalBusinessName")
companys(companys.Count - 1).AmountRequestedText = reader("amountRequestedText")
companys(companys.Count - 1).HowSoonNeededText = reader("howSoonNeededText")
companys(companys.Count - 1).QueueID = reader("QueueID")
companys(companys.Count - 1).Company = reader("Company")
End While

For counter As Integer = 0 To companys.Count
counter += 1
If i <> CInt(companys(companys.Count - 1).CompanyID) Then
i = CInt(companys(companys.Count - 1).CompanyID)
Else
companys.Remove()
End If
Next

Answer

Don't add them in the first place. Use either aDictionary (if you will look them up by ID later) or a HashSet (if you won't) to check before adding to the results. Here's the HashSet example:

Dim companyIDs As New HashSet(Of String)()  
While reader.Read()
    If Not companyIDs.Contains(reader("company").ToString()) Then
        companys.Add(New CompanySearch() With {
            .StartDate = reader("StartDate").ToString() & " (" & count & ")",
            .CompanyID = reader("company").ToString(),
            .Origin = reader("Origin").ToString(),
            .OriginName = reader("OriginName").ToString(),
            .Status = reader("status").ToString(),
            .StatusName = reader("statusname").ToString(),
            .Status = reader("status").ToString(),
            .FullLegalBusinessName = reader("fullLegalBusinessName"),
            .AmountRequestedText = reader("amountRequestedText"),
            .HowSoonNeededText = reader("howSoonNeededText"),
            .QueueID = reader("QueueID"),
            .Company = reader("Company"),
        })
    End If
    companyIDs.Add(reader("company").ToString())
End While

I also noticed that both the .Company and .CompanyID properties in this object are populated from the company column in the reader. Is this intentional, or do you mean to look at a different column for .CompanyID?

Additionally, while I understand your existing search SQL already considers these company rows as distinct, you should probably go back to the drawing board there and rethink the SQL, so that you truly do get distinct records. Perhaps use a nested query or CTE to first find a projection of CompanyID values that match your query, and then join back to your company table to get the details for each company with an ID included in those initial results. If that's not possible, you should consider what it is that makes the rows different, because I promise you that some column IS different, and if you just cull one record or the other you're potentially showing bad data from the wrong row to your user.

Comments