SolvedPrivate

Returning Distinct Records from a DataTable using LINQ

Posted on 2014-01-20
7
40 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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