Grouping question
-
I am trying to group my datatable data on column SourceID. But currently it just returns all rows. Here's my LINQ query:
Dim transactionDetailsQuery = (From t In dc.SupplierTransactions _
Join p In dc.Portfolios On t.Portfolio Equals p _
Join b In dc.BankAccountPortfolios On p Equals b.Portfolio _
Group By SourceID = t.SourceID, BankAccountCode = b.BankAccountCode, TranDate = t.TranDate, _
Period = t.Period, Year = t.Year, TranType = t.TranType, Reference = t.Reference, _
ID = t.ID, PortfolioCode = t.PortfolioCode, Description = t.Description, SupplierCode = t.SupplierCode Into g = Group, _
NetAmount = Sum(t.NetAmount), TaxAmount = Sum(t.TaxAmount), PaidAmount = Sum(t.PaidAmount) _
Select Transaction = ID, SourceID, BankAccountCode, TranDate, Period, Year, TranType, Reference, PortfolioCode, Description, NetAmount, TaxAmount, PaidAmount, SupplierCode)What should the correct syntax be please?
-
I am trying to group my datatable data on column SourceID. But currently it just returns all rows. Here's my LINQ query:
Dim transactionDetailsQuery = (From t In dc.SupplierTransactions _
Join p In dc.Portfolios On t.Portfolio Equals p _
Join b In dc.BankAccountPortfolios On p Equals b.Portfolio _
Group By SourceID = t.SourceID, BankAccountCode = b.BankAccountCode, TranDate = t.TranDate, _
Period = t.Period, Year = t.Year, TranType = t.TranType, Reference = t.Reference, _
ID = t.ID, PortfolioCode = t.PortfolioCode, Description = t.Description, SupplierCode = t.SupplierCode Into g = Group, _
NetAmount = Sum(t.NetAmount), TaxAmount = Sum(t.TaxAmount), PaidAmount = Sum(t.PaidAmount) _
Select Transaction = ID, SourceID, BankAccountCode, TranDate, Period, Year, TranType, Reference, PortfolioCode, Description, NetAmount, TaxAmount, PaidAmount, SupplierCode)What should the correct syntax be please?
Well, if you want to filter and not "return all rows" I would suggest the Where clause. If you want more than one row per group, I would suggest grouping by fewer fields (you are currently grouping by 11 of which 1 looks like a primary key id and another looks like a free text field that will rarely be the same from one record to the next).
-
Well, if you want to filter and not "return all rows" I would suggest the Where clause. If you want more than one row per group, I would suggest grouping by fewer fields (you are currently grouping by 11 of which 1 looks like a primary key id and another looks like a free text field that will rarely be the same from one record to the next).
My problem has been that if I only group on SourceId, I am then unable (as yet) to reference items in the Select portion of the query. If I have
Select Transaction = t.ID, t.SourceID .. etcThe t. reference throws a scope/undefined error. How can I reference ID and SourceID from within the select portion please? -
My problem has been that if I only group on SourceId, I am then unable (as yet) to reference items in the Select portion of the query. If I have
Select Transaction = t.ID, t.SourceID .. etcThe t. reference throws a scope/undefined error. How can I reference ID and SourceID from within the select portion please?As I explained when you asked the first part of this question, doing a Group By will get you a group object. If you just want to get all the rows with all records that have the same SourceID together, do a sort with Order By. Then you will be able to reference the original objects in the select part of the query. Grouping is most useful when you are trying to get some sort of aggregate result such as the sum of all order amounts from a particular customer.
-
I am trying to group my datatable data on column SourceID. But currently it just returns all rows. Here's my LINQ query:
Dim transactionDetailsQuery = (From t In dc.SupplierTransactions _
Join p In dc.Portfolios On t.Portfolio Equals p _
Join b In dc.BankAccountPortfolios On p Equals b.Portfolio _
Group By SourceID = t.SourceID, BankAccountCode = b.BankAccountCode, TranDate = t.TranDate, _
Period = t.Period, Year = t.Year, TranType = t.TranType, Reference = t.Reference, _
ID = t.ID, PortfolioCode = t.PortfolioCode, Description = t.Description, SupplierCode = t.SupplierCode Into g = Group, _
NetAmount = Sum(t.NetAmount), TaxAmount = Sum(t.TaxAmount), PaidAmount = Sum(t.PaidAmount) _
Select Transaction = ID, SourceID, BankAccountCode, TranDate, Period, Year, TranType, Reference, PortfolioCode, Description, NetAmount, TaxAmount, PaidAmount, SupplierCode)What should the correct syntax be please?
Your group is grouping by a LOT of fields, so I am guessing that the broad set of data for grouping is making each row unique. In LINQ to SQL, like SQL itself, when you group, to access non-grouped data you need to aggregate it. Also like SQL, if you group on a primary key (which I assume SourceID is), then each row is guaranteed to be unique, and there isn't any point in grouping. Assuming you have a simple set like this: TABLE PersonAges ID, FirstName, LastName, Age ---------------------------- 1, Jon, Doe, 24 2, Jane, Doe, 22 3, Roger, Doe, 48 4, Judy, Doe, 51 5, Jack, Smith, 47 6, Audrey, Smith, 44 And you want to find the average age of members of each last name:
var agesByLastName = from pa in db.PersonAges
group pa by pa.LastName into g
select new { LastName = g.Key, AverageAge = g.Average(pa => pa.Age) };