SolvedPrivate

Returning Distinct Records from a DataTable using LINQ

Posted on 2014-01-20
7
36 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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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
Comment Utility
Not a problem Joe, glad I was able to help.
0
 

Author Comment

by:JoeMiskey
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now