Select all columns after filtering Distinct rows based on few columns from a Datatable in c#

Question

I gone through similar questions posted here but didn't find the solution of my problem. I have a datatable in C# which contains duplicate rows like below:

Now, I have to apply a filter which finds all distinct rows based on Last 2 highlighted columns but in final result set I have to return all columns.

Also, I'll get an ADDRESS_ID whose corresponding row should be returned and duplicates should be removed.

DataView view = new DataView(ds.Tables[0]);
            DataTable distinctValues = view.ToTable(true, "ADDR_LINE_1", "ADDR_LINE_2", "ADDR_LINE_3", "CITY", "STATE", "ZIP", "BOX_NUMBER");

This code is returning 2 rows but not all columns.

Also used this code:

DataTable dtUniqRecords = new DataTable();
            dtUniqRecords = ds.Tables[0].DefaultView.ToTable(true, "RELATE_CODE", "ADDRESS_TYPE", "ADDRESS_CODE", "ADDRESS_ID", "ADDR_LINE_1", "ADDR_LINE_2", "ADDR_LINE_3", "CITY", "STATE", "ZIP", "BOX_NUMBER");

But this is returning all rows with duplicates.

Please add the source of what you mentioned (referring to: "I gone through similar questions posted here but didn't find the solution of my problem"). I am not familiar with the problem described above because I have never tried working with a DataTable or a DataView class before, but can't you just use an SQL command using the "Distinct" clause (assuming you are retrieving this data from a database)? — Barrosy, Oct 17 '19 at 13:46
I gone through https://stackoverflow.com/questions/25998500/get-all-column-valus-of-a-datatable-based-on-only-one-distinct-column and https://stackoverflow.com/questions/1199176/how-to-select-distinct-rows-in-a-datatable-and-store-into-an-array and many more. — LogicalDesk, Oct 17 '19 at 13:48
Please consider re-wording your question. Its unclear what you're trying to do. You either want rows without duplicate values, or you want the duplicate values. Which? You want to pass in an ID but return rows which do not have that ID? — haldo, Oct 17 '19 at 15:46

score 0 · Answer 1 · answered Oct 17 '19 at 14:01

0

You can use GroupBy to filter out duplicate values:

var dt = ds.Tables[0];
var distinct = dt.AsEnumerable()
                 .GroupBy(g => new 
                 { 
                     Address1 = g.Field<string>("ADDR_LINE_1"), 
                     Address2 = g.Field<string>("ADDR_LINE_2")
                     // any other fields you need to group by
                 })
                 .Select(g => g.First())   // select first group including all columns
                 .CopyToDataTable();

answered Oct 17 '19 at 14:01

haldo

14,512
5
46
52

This is working but I also have to put a where condition to return an ADDRESS_ID row whose duplicates should be removed and returned with additional distinct rows. could you please modify the code? – LogicalDesk Oct 17 '19 at 14:12
Could you clarify what you want returned? Do you want a list of duplicate IDs returned (ie rows to remove)? Or do you want a list of distinct values returned (the code currently does this)? – haldo Oct 17 '19 at 14:17
Referring to the screenshot I've attached- I get an ADDRESS_ID (20063) along with the datatable after that I need to return that ADDRESS_ID(20063) row with no duplicates(considering those 2 filter by columns) + Other distinct rows(anyone from 209365,209429, 209455). – LogicalDesk Oct 17 '19 at 14:22
The code you have provided works fine but I also have to place a check like if I pass an ADDRESS_ID then it should return that particular row with all other distinct rows rather than returning just random distinct row. Please let me know if I'm still confusing you. – LogicalDesk Oct 17 '19 at 14:32

Select all columns after filtering Distinct rows based on few columns from a Datatable in c#

1 Answers1