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
Solved

LINQ: group by from datatable

Posted on 2012-03-30
7
4,175 Views
Last Modified: 2012-06-27
Hi,

I read a DBF file (with more than 1.000.000 records) into a datatable.
I can read that data, but now I want to group by on 2 columns (sampnumber,testcode)

Once I know the unique records by (sampnumber,testcode), I want the last 29 records for  every sampnumber,testcode in the data.

I've been looking for the solution, but I just can't find it.

The columns I have:
sampnumber, sampname, samplenr, date, testcode, testname, result, unit

Thanks.
0
Comment
Question by:VampValdo
  • 3
  • 3
7 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37785951
Here is sample code:-
var dt = new DataTable();
            dt.Columns.Add("Number", typeof (int));
            dt.Columns.Add("Code", typeof(int));
            dt.Columns.Add("Unit", typeof(int));

            var dr = dt.NewRow();
            dr.SetField<int>("Number", 1);
            dr.SetField<int>("Code", 12);
            dr.SetField<int>("Unit", 3);

            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr.SetField<int>("Number", 1);
            dr.SetField<int>("Code", 13);
            dr.SetField<int>("Unit", 34);

            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr.SetField<int>("Number", 1);
            dr.SetField<int>("Code", 12);
            dr.SetField<int>("Unit", 13);

            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr.SetField<int>("Number", 1);
            dr.SetField<int>("Code", 13);
            dr.SetField<int>("Unit", 7);

            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr.SetField<int>("Number", 1);
            dr.SetField<int>("Code", 14);
            dr.SetField<int>("Unit", 18);

            dt.Rows.Add(dr);

            var qry = from rw in dt.AsEnumerable()
                      group rw by new {Number = rw["Number"], Code = rw["Code"]}
                      into grouping
                      select
                          new
                              {
                                  grouping.Key.Number,
                                  grouping.Key.Code,
                                  Unit = grouping.Sum(rw => Convert.ToInt32(rw["Unit"]))
                              };

            foreach(var q in qry)
            {
                
            }

Open in new window

0
 

Author Comment

by:VampValdo
ID: 37785960
Hi, thank you.

I can't translate that into VB.NET code. :'(

Do I then have to loop in the foreach and do a new linq that gives me the top 29 records for every record where sampnumber and testcode are the same as the values from the first linq? With a where?
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37785961
Here is sample LINQ using Method Chains(My Preference):-

var qry =
                dt.AsEnumerable()
                    .GroupBy(rw => new {Number = rw["Number"], Code = rw["Code"]})
                    .Select(grouping => new
                                            {
                                                grouping.Key.Number,
                                                grouping.Key.Code,
                                                Unit = grouping.Sum(rw => Convert.ToInt32(rw["Unit"]))
                                            });
                                            

            foreach(var q in qry)
            {
                
            }

Open in new window


In VB :-
Dim qry = dt.AsEnumerable().GroupBy(Function(rw) New With { _
	.Number = rw("Number"), _
	.Code = rw("Code") _
}).[Select](Function(grouping) New With { _
	grouping.Key.Number, _
	grouping.Key.Code, _
	.Unit = grouping.Sum(Function(rw) Convert.ToInt32(rw("Unit"))) _
})



For Each q As var In qry
Next

Open in new window


Used This For Conversion:-
http://converter.telerik.com/
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:VampValdo
ID: 37786055
Hi,

This code is not working.

I get the following error:
error
I even tried using sampnumber for the sum (that are all whole numbers)

But I just need a list of the unique values of (sampnumber / testcode).
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37786269
this probably means that it cannot be converted to integer
Just see if its type is integer....

Otherwise use the same type as in your datatable...
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 37786328
See if this helps you:

Dim query = From row As DataRow In dt.Rows
            Group row By SampNumber = row.Field(Of Integer)("sampnumber"), TestCode = row.Field(Of Integer)("testcode") Into Group
            Select Group

For Each grp In query
  Dim last29 = grp.Skip(grp.Count() - 29)

  For Each item In last29
    ' do something with last 29 for each grp
  Next
Next

Open in new window

0
 

Author Closing Comment

by:VampValdo
ID: 37786392
Worked like a charm.

Thank you. :-)
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

792 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