SolvedPrivate

Returning Distinct Records from a DataTable using LINQ

Posted on 2014-01-20
7
38 Views
Last Modified: 2016-02-10
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
Comment
Question by:JoeMiskey
  • 3
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39795450
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
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39795459
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
 

Author Comment

by:JoeMiskey
ID: 39795474
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39795540
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
 

Author Comment

by:JoeMiskey
ID: 39797185
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39797212
Not a problem Joe, glad I was able to help.
0
 

Author Comment

by:JoeMiskey
ID: 39797213
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 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