[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 48
  • Last Modified:

Returning Distinct Records from a DataTable using LINQ

I am working in Visual Basic, and am trying to use LINQ to return records from a DataTable.  I am interested in returning two specific fields, but I want to weed out all duplicates.  So I tried something like this:
Dim results = (From part In SSNConversion.AsEnumerable _
               Where part.Field(Of String)("SSN") = ID _
               Select part.Field(Of String)("SubscriberID"), part.Field(Of String)("SSN")).Distinct()

Open in new window

This did not work.  So I tried to break it down (before trying to incorporate "Distinct()"), and discovered the following.
If I try to return all fields, like this:
Dim results = From part In SSNConversion.AsEnumerable _
               Where part.Field(Of String)("SSN") = ID _
               Select part

Open in new window

or just one field like this:
Dim results = From part In SSNConversion.AsEnumerable _
               Where part.Field(Of String)("SSN") = ID _
               Select part.Field(Of String)("SubscriberID")

Open in new window

it does not throw any errors at me.  
But as soon as I try to add the second field, like this:
Dim results = From part In SSNConversion.AsEnumerable _
               Where part.Field(Of String)("SSN") = ID _
               Select part.Field(Of String)("SubscriberID"), part.Field(Of String)("SSN")

Open in new window

It gives me an error saying "Range variable can only be inferred from a simple or qualified name with no arguments".

I am fairly new to programming using LINQ, and I don't really know what these means or how to fix it.  Can anyone help?  I need to figure out a way to return just those two fields, and weed out all the duplicate records when only considering those two fields.

Thanks
0
JoeMiskey
Asked:
JoeMiskey
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why use LINQ, if the datatable (and dataview) and already build-in method for that:
http://msdn.microsoft.com/en-us/library/wec2b2e6%28v=vs.110%29.aspx
'Declaration
Public Function ToTable ( _
      distinct As Boolean, _
      ParamArray columnNames As String() _
) As DataTable
0
 
Fernando SotoRetiredCommented:
Hi JoeMiskey;

The error you are getting is that the compiler can not inferred the fields names from the fields in the select statement. To remove the error give both fields in the select statement a name as shown below.

Dim results = From part In SSNConversion.AsEnumerable _
              Where part.Field(Of String)("SSN") = ID _
              Select _
                  SubscriberID = part.Field(Of String)("SubscriberID"), _
                  SSN = part.Field(Of String)("SSN")

Open in new window

0
 
JoeMiskeyAuthor Commented:
Thanks!  That seems to get rid of the errors.  I will give it a trial run tomorrow (I am on my way out the door now).

Out of curiosity, why does it require that?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Fernando SotoRetiredCommented:
Hi

To your question, "Out of curiosity, why does it require that?", the reason is that Linq is building an Anonymous object and needs to identify the two result fields back to the caller and it can not make a best guess so it is asking you to give names to those fields. It can be anything as long as it holds true to the rules of variables names.

Applying the Distinct method as shown below should give you what you need.

Dim results = (From part In SSNConversion.AsEnumerable _
               Where part.Field(Of String)("SSN") = ID _
               Select _
                   SubscriberID = part.Field(Of String)("SubscriberID"), _
                   SSN = part.Field(Of String)("SSN")). Distinct()

Open in new window

0
 
JoeMiskeyAuthor Commented:
I guess it makes sense when I think about it.  I thought that the field name should just transfer through, but I guess applying the ".Field(Of String)" to it treats it like a calculated field, requiring a name to be given to it.

I did test it out more thoroughly this morning, and everything works as it should now.
Thank you very much!
0
 
Fernando SotoRetiredCommented:
Not a problem Joe, glad I was able to help.
0
 
JoeMiskeyAuthor Commented:
Guy,

I apologize.  I did not see your reply originally.  I am not sure if it would be possible (or how) to use the method your suggested.

Here is the final code I used (I needed to add another level of criteria):
Dim results2 = (From part In SSNConversion.AsEnumerable _                                                                 
               Where part.Field(Of String)("GroupID") = GroupID _                                                         
               And part.Field(Of String)("SubscriberID") = PartID _                                                       
               Select SubscriberID = part.Field(Of String)("SubscriberID"), SSN = part.Field(Of String)("SSN")).Distinct()

Open in new window

So I got it working now, though I am never opposed to seeing other ways of doing things, as long as it doesn't overcomplicate it.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now