Another LINQ question

Matt_Hirst

Matt_Hirst

Matt_Hirst

Associate
Joined
27 Jan 2005
Posts
1,397
Location
S. Yorks
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
 
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
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
Last edited:
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
 
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
 
You must log in or register to reply here.
Back
Top Bottom