SolvedPrivate

Returning Distinct Records from a DataTable using LINQ

Posted on 2014-01-20
7
39 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 143

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 63

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

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 63

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

680 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