Another LINQ question

Matt_Hirst · 9 May 2012 at 13:14

Have a form that displays data in a datagridview, win forms app c#.

When I click a button I want to open up another form with the a datagridview showing same data at the top of this screen and below it I want a summary displaying.

Can I use LINQ to query a datagridview to summarise data based across a number of dffernt columns? If so how?

regards,

Matt

mp3duck · 9 May 2012 at 14:14

Can you not query the datasource that was used to initially bind the datagrid view?

AMMUT · 9 May 2012 at 14:15

This might help

http://stackoverflow.com/questions/308085/how-to-query-a-datagridview-using-linq

Matt_Hirst · 9 May 2012 at 15:41

Am struggling with LINQ so now been looking at doing it direct with SQL commands. Again am new to C# and also SQL so bear with me.

I have created a datatable from a sql server database, I then need to add approx 6 columns to this datatable and populate these based upon a couple of columns from the main database.

Now I have the datatable with everything in it, I can display this via a datagrid view, but can I summarise this datatable, effectively want about half of the columns with a cost field summing?

As you can see I cannot query the original source as it doesn't contain all of the information I require, this is added afterwards.

I have been learning LINQ but getting annoyed at how limited it is, or how limited my ability is with it, so if anyone can point me in a better direction it would be appreciated.

regards,

Matt

AMMUT · 9 May 2012 at 15:52

A sample of the data, and what you want the output to be from the sub query might help us to help you.

Matt_Hirst · 9 May 2012 at 16:06

Datatable 1 (6 columns)

D, F, G, A, B, 1
E, S, X, A, B, 2
E, S, X, B, B, 1
D, F, X, B, B, 3
E, S, A, B, C, 2

Datatable 2 (3 Columns)

A, B, 3
B, B, 4
B, C, 2

Sorry cant post physical data extract.

regards,

Matt

AMMUT · 9 May 2012 at 16:09

So basically you are taking unique combinations of the 4th and 5th columns and summing the value of their 6th column?

Will take a look at it when I get home.

Matt_Hirst · 9 May 2012 at 16:39

Yes that is exactly it, could be numerous other columns that I group by as well upto 6 columns but I think I will only want to sum the final column.

regards,

Matt

Matt_Hirst · 10 May 2012 at 09:31

Found some LINQ examples last night and modded my code to this:

Code:

            var queryAD1 = from m in dt.AsEnumerable()
                            where m.Field<string>("ProjectId") == frmSF.cmbProjID.Text
                            group m by m.Field<string>("ProjectId") into A123Group
                           select new
                           {
                            proj = A123Group.GroupBy (a1 => a1.Field<String>("ProjectId")),
                            Cost = A123Group.Sum (a1 => a1.Field<Decimal>("Cost"))
                            };

            DataTable dts = queryAD1.CopyToDataTable();

It sums the cost up correct but I need to return the Project Id in the first column, then a few other things. Unfortunately it doesn't return anything in the project id field, any help??

Matt

Matt_Hirst · 10 May 2012 at 10:48

Have progressed to this but it fails if I include the commented out lines, funnily enough the commented out lines include the values that change.

Code:

 var newSort = from row in dt.AsEnumerable()
                          group row by new {
                                          Proj = row.Field<string>("Proj"), 
                                          Floor = row.Field<string>("Floor"),
                                          SD = row.Field<string>("SD"),
                                          NoS = row.Field<string>("NoS"),
                                          SL = row.Field<string>("SL"),
                                          //RL = row.Field<string>("RL"),
                                          Std = row.Field<string>("Std"),
                                          //Sg = row.Field<string>("Sg"),
                                          //ED = row.Field<string>("ED"),
                                          Cost = row.Field<Decimal>("Cost") 
                                        } into grp
                          
                                        orderby grp.Key
                                        select new
                                        {
                                            Proj = grp.Key.Proj,
                                            Floor = grp.Key.Floor,
                                            SD = grp.Key.SD,
                                            NOS = grp.Key.NOS,
                                            SL = grp.Key.SL,
                                            //RL = grp.Key.RL,
                                            Std = grp.Key.Std,
                                            //Sg = grp.Key.Sg,
                                            //ED = grp.Key.ED,
                                            Cost = grp.Sum(r => Convert.ToDecimal(r.ItemArray[8]))
                                        };

Any ideas?

Matt

GravyMonster · 10 May 2012 at 11:06

What's the error when it fails?

Matt_Hirst · 10 May 2012 at 11:12

The Linq statement works but when I copy back to a datatable I get the following:

At least one object must implement IComparable.

regards,

Matt

Matt_Hirst · 10 May 2012 at 11:20

Its the way I copy to datatable that fails, what is the best way to either copy the results to a datatable?

Using:

DGV2.DataSource = from row in dt.AsEnumerable()
group row by new {
Proj = row.Field<string>("Proj"),
Floor = row.Field<string>("Floor"),
SD = row.Field<string>("SD"),
NoS = row.Field<string>("NoS"),
SL = row.Field<string>("SL"),
RL = row.Field<string>("RL"),
Std = row.Field<string>("Std"),
Sg = row.Field<string>("Sg"),
ED = row.Field<string>("ED"),
Cost = row.Field<Decimal>("Cost")
} into grp

orderby grp.Key
select new
{
Proj = grp.Key.Proj,
Floor = grp.Key.Floor,
SD = grp.Key.SD,
NOS = grp.Key.NOS,
SL = grp.Key.SL,
RL = grp.Key.RL,
Std = grp.Key.Std,
Sg = grp.Key.Sg,
ED = grp.Key.ED,
Cost = grp.Sum(r => Convert.ToDecimal(r.ItemArray[8]))
};

returns nothign to the datagridview.

Matt

Matt_Hirst · 10 May 2012 at 11:31

disregard

Matt

Matt_Hirst · 10 May 2012 at 14:03

Got it sort of working with the following:

Code:

            var query = from row in dt.AsEnumerable()
                        group row by new
                        {
                            Proj = row.Field<string>("Proj"),
                            Floor = row.Field<string>("Floor"),
                            SD = row.Field<string>("SD"),
                            NOS = row.Field<string>("NOS"),
                            SL = row.Field<string>("SL"),
                            RL = row.Field<string>("RL"),
                            Std = row.Field<string>("Std"),
                            Sp = row.Field<string>("S"),
                            ED = row.Field<string>("ED")

                        } into grp
                        orderby grp.Key.ProjectId
                        select new
                        {
                            SD = grp.Key.SD,
                            NOS = grp.Key.NOS,
                            SL = grp.Key.SL,
                            RL = grp.Key.RL,
                            Std = grp.Key.Std,
                            Sp = grp.Key.Sp,
                            ED = grp.Key.ED,
                            TotalCost = grp.Sum(r => r.Field<decimal>("Cost")),
                            TotalRev = grp.Sum(r => r.Field<decimal>("Cost"))
                        };

Final thing I need is TotalRev tuning into a count across the group.

Matt

Matt_Hirst · 10 May 2012 at 14:44

Sorted by counting across the grp - fairly logical once you get your head around it.

Matt

Matt_Hirst · 10 May 2012 at 16:50

Knew I would end up speaking too soon - lol.

Code:

var queryss = from row in dt.AsEnumerable()
                         group row by new
                         {
                             SD = row.Field<double>("SD"),
                             NOS = row.Field<double>("NOS"),
                             SL = row.Field<double>("SL")

                         } into grp
                          orderby grp.Key.StudDiameter
                         select new
                         {
                             SD = grp.Key.SD,
                             TotalQty = grp.Sum(r => r.Field<double>("NOS"))
                         };

I want to times the TotalQty by SL, can this be done?

Matt