LINQ - Return rows with all columns of a table based on multiple distinct columns

Question

I have a table,

**Matter No      Client No    Client Name  Invoice No  Invoice Date    Invoice Amt**

1111-0001          1111          ABC            101       01/01/2013       100.00 
1111-0001          1111          ABC            102       02/01/2013       200.00
1111-0001          1111          ABC            103       03/01/2013       300.00
1111-0001          1111          ABC            104       04/01/2013       400.00
1111-0001          1112          DEF            105       05/01/2013       500.00
1111-0001          1113          GHI            106       06/01/2013       600.00

Based on above scenario, I wish to return all the columns but DISTINCT ROWS based on Columns - Matter No and Client No. i.e. I need to see 3 rows as output:

**Matter No      Client No    Client Name  Invoice No  Invoice Date    Invoice Amt**

1111-0001          1111          ABC            101       01/01/2013       100.00 
1111-0001          1112          DEF            105       05/01/2013       500.00
1111-0001          1113          GHI            106       06/01/2013       600.00

How I can achieve this in LINQ?

Thanks in advance

Dó you have some c# code to og with your question? :-) what have you tried? — Jens Kloster, May 09 '13 at 22:49

Jean-Bernard Pellerin · Answer 1 · 2013-05-09T23:07:01.040

5

from x in table
group x by new {x.MatterNo, x.ClientNo}
into mygroup
select mygroup.First();

Or if you prefer this syntax

list.DistinctBy(x => new {x.MatterNo, x.ClientNo});

If you're not dealing with a list you can try

from DataRow drow in dtable.Rows
group drow by new {MatterNo = drow["Matter No"], ClientNo = drow["Client No"]}
into myGroup
select myGroup.First();

edited May 09 '13 at 23:07

answered May 09 '13 at 22:48

Jean-Bernard Pellerin

12,556
10
57
79

thanks but column name is [Matter No] and [Client No]. Above query is not recognizing x.matterno and x.clientNo. It says System.Data.DataRow doesn't contain definition for x.MatterNo – prafulla shimpi May 09 '13 at 22:56
@prafullashimpi I'm changed it, does the last example bring you any closer? – Jean-Bernard Pellerin May 09 '13 at 22:59
1

goup -> group? Typo, I'm assuming – Kyle C May 09 '13 at 23:01
thanks again. Now I am receiving an error "Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access." for the statement **{drow["Matter No"], drow["Client No"]}**. I added missing 'r' to the group. – prafulla shimpi May 09 '13 at 23:03
1

Because it's not valid syntax for creating an anonymous type. Try: new { MatterNo = drow["Matter No"], ClientNo = drow["Client No"]} – rossisdead May 09 '13 at 23:06
I am trying to return this result as data table, if possible, but getting error as ** "Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Data.DataTable'. An explicit conversion exists (are you missing a cast?)" ** The query I am writing is: DataTable dtRows = from DataRow drow in dt.Rows group drow by new { MatterNo = drow["Matter No"], ClientNo = drow["Client No"] } into myGroup select myGroup.First ; – prafulla shimpi May 09 '13 at 23:15
@prafullashimpi That's a separate issue, but here is a solution http://stackoverflow.com/a/2263691/103959 – Jean-Bernard Pellerin May 09 '13 at 23:17
@Jean-Bernard Pellerin, thanks for your help. I tried a lot to cast, still I am unable to compile this, will it possible to help me with a LINQ query returning result set as datatable – prafulla shimpi May 10 '13 at 08:40

score 0 · Answer 2 · edited Jul 05 '17 at 01:08

0

Try this:

DataTable result = dt.AsEnumerable()
                     .GroupBy(x => x.Field<int>("COLUMN NAME"))
                     .Select(x => x.First()).CopyToDataTable();

edited Jul 05 '17 at 01:08

marc_s

732,580
175
1,330
1,459

answered Jun 26 '17 at 17:10

Ata Hoseini

117
1
4

LINQ - Return rows with all columns of a table based on multiple distinct columns

2 Answers2