LINQ Grouped Query

[Paolo]

In a grouped query, I want to pick up a descriptive field from lookup tables
related to my main transactions table.
The Transaction table rows have a category column containing a single
character Id code, as does the SubCategory table. I want to pick up the
appropriate description for the Category and SubCategory Ids from the
Category and Subcategory tables.

I've got this far with my query but am only displaying the ids
(Key.T_Category and Key.T_SubCategory). Not sure how to amend the code to
substitute the descriptions for the Ids.

Any help appreciated.

private void button2_Click(object sender, EventArgs e)
{
var groupQuery =
from trans in dataSet.Transaction
from c in dataSet.Category where trans.T_Category == c.C_Id
from s in dataSet.SubCategory where trans.T_SubCategory ==
s.S_Id

group trans by new
{
trans.T_Category,
trans.T_SubCategory
}
into firstGroup
select new
{
Category = firstGroup.Key.T_Category,
SubCategory = firstGroup.Key.T_SubCategory,
Count = firstGroup.Count(),
Sum = firstGroup.Sum(s => s.T_Amount),
Mean = (firstGroup.Sum(s =>
s.T_Amount))/(firstGroup.Count())
}
into secondGroup
orderby secondGroup.Category ascending,
secondGroup.SubCategory ascending
select secondGroup;

foreach (var item in groupQuery)
{
richtxbxAnalysis.AppendText(item.ToString() + "\n");
}
}

 

[Paolo]

OK, I've got the results I wanted. How can I improve the efficiency of this
query? Maybe I can't but I feel as though I've got too many groups.

private void button2_Click(object sender, EventArgs e)
{
var groupQuery =
from trans in dataSet.Transaction
from c in dataSet.Category where trans.T_Category == c.C_Id
from s in dataSet.SubCategory where trans.T_SubCategory ==
s.S_Id

select new
{
c.C_Description,
s.S_Description,
trans.T_Amount
}
into startGroup

group startGroup by new
{
startGroup.C_Description,
startGroup.S_Description
}
into secondGroup

select new
{
Category = secondGroup.Key.C_Description,
SubCategory = secondGroup.Key.S_Description,
Count = secondGroup.Count(),
Sum = secondGroup.Sum(s => s.T_Amount),
Mean = ( secondGroup.Sum(s =>
s.T_Amount))/(secondGroup.Count())
}
into thirdGroup

orderby thirdGroup.Category,
thirdGroup.SubCategory

select thirdGroup;

foreach (var item in groupQuery)
{
richtxbxAnalysis.AppendText(item.ToString() + "\n");
}
}