WorknHardr
asked on
MVC 4 LINQ IGrouping IEnumerable Cast Issue?
I'm trying to accomplish this in MVC 4 View:
11/2013
November
Name Total
Auto 104.45
Charity 30.00
Credit 110.00
Food 370.34
Health 90.85
Household 438.57
Medical 142.99
Personal 107.95
Utility 455.32
Instead I get this. It doesn't really group the Category Names the way I want. Although the Month/Year grouping is fine.
11/2013
November
Category Sub
Auto $28.71
Auto $39.91
Auto $35.83
Charity $30.00
Credit $110.00
Food $18.37
Food $11.32
Food $9.08
Food $10.69
Food $3.98
Food $20.29
public ActionResult Summary()
{
var data = (from e in context.Expenses
join c in context.Categories on e.CategoryID equals c.CategoryID
let j = new ReportSummaryViewModel { Category = c, Expense = e }
let dt = j.Expense.Created
group j by new { y = dt.Year, m = dt.Month } into g
select new Group<object, ReportSummaryViewModel>
{
Key = g.Key,
Values = g.OrderBy(o => o.Category.Name),
Total = g.Sum(a => a.Expense.Amount),
}
).ToList();
return View(data);
}
public class Group<K, T>
{
public K Key;
public IEnumerable<T> Values;
public decimal Total;
}
Help!!!
11/2013
November
Name Total
Auto 104.45
Charity 30.00
Credit 110.00
Food 370.34
Health 90.85
Household 438.57
Medical 142.99
Personal 107.95
Utility 455.32
Instead I get this. It doesn't really group the Category Names the way I want. Although the Month/Year grouping is fine.
11/2013
November
Category Sub
Auto $28.71
Auto $39.91
Auto $35.83
Charity $30.00
Credit $110.00
Food $18.37
Food $11.32
Food $9.08
Food $10.69
Food $3.98
Food $20.29
public ActionResult Summary()
{
var data = (from e in context.Expenses
join c in context.Categories on e.CategoryID equals c.CategoryID
let j = new ReportSummaryViewModel { Category = c, Expense = e }
let dt = j.Expense.Created
group j by new { y = dt.Year, m = dt.Month } into g
select new Group<object, ReportSummaryViewModel>
{
Key = g.Key,
Values = g.OrderBy(o => o.Category.Name),
Total = g.Sum(a => a.Expense.Amount),
}
).ToList();
return View(data);
}
public class Group<K, T>
{
public K Key;
public IEnumerable<T> Values;
public decimal Total;
}
Help!!!
You are grouping by year/month, but you really also need to group by category, too.
ASKER
Yes, it's a little more complicated than a normal group.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, I found a solution doing the sub-group-by in the View like so, but I would like to do all grouping in the Action.
[View - Working Code]
@model List<Group<string, ReportSummaryViewModel>>
@foreach (var group in Model)
{
<table>
@foreach (var item in group.Values.GroupBy(p => p.Category.Name))
{
<tr>
<td>@item.FirstOrDefault() .Category. Name</td>
<td>@item.Sum(g => g.Expense.Amount).ToString ("c")</td>
</tr>
}
</table>
}
[Now concerning the new Action Code below]
1. I get this error using : Values = g.GroupBy(o => o.Category.Name)
" Cannot implicitly convert type 'System.Collections.Generi c.IEnumera ble<System .Linq.IGro uping<stri ng,ReportE xpenseDeta ilViewMode l>>' to 'System.Collections.Generi c.IEnumera ble<Report ExpenseDet ailViewMod el>'.
An explicit conversion exists (are you missing a cast?) "
2. I get this error using my new code below:
" Cannot implicitly convert type 'System.Collections.Generi c.IEnumera ble<Anonym ousType#1> ' to 'System.Collections.Generi c.IEnumera ble<Report ExpenseDet ailViewMod el>'.
An explicit conversion exists (are you missing a cast?) "
[Latest Sub-Grouping Attempt]
public ActionResult Summary()
{
var data = (from e in context.Expenses
join c in context.Categories on e.CategoryID equals c.CategoryID
let j = new ReportSummaryViewModel
{
Category = c,
Expense = e,
Name = c.Name
}
let dt = j.Expense.Created
group j by new { y = dt.Year, m = dt.Month } into g
select new Group<object, ReportSummaryViewModel>
{
Key = g.Key,
//Values = g.OrderBy(o => o.Category.Name),
//new code
Values = (from j in g
group j by g.Key.Name into g2
select new { Name = g2.OrderBy(k=> k.Category.Name) } ),
Total = g.Sum(a => a.Expense.Amount),
}
).ToList();
return View(data);
}
[View - Working Code]
@model List<Group<string, ReportSummaryViewModel>>
@foreach (var group in Model)
{
<table>
@foreach (var item in group.Values.GroupBy(p => p.Category.Name))
{
<tr>
<td>@item.FirstOrDefault()
<td>@item.Sum(g => g.Expense.Amount).ToString
</tr>
}
</table>
}
[Now concerning the new Action Code below]
1. I get this error using : Values = g.GroupBy(o => o.Category.Name)
" Cannot implicitly convert type 'System.Collections.Generi
An explicit conversion exists (are you missing a cast?) "
2. I get this error using my new code below:
" Cannot implicitly convert type 'System.Collections.Generi
An explicit conversion exists (are you missing a cast?) "
[Latest Sub-Grouping Attempt]
public ActionResult Summary()
{
var data = (from e in context.Expenses
join c in context.Categories on e.CategoryID equals c.CategoryID
let j = new ReportSummaryViewModel
{
Category = c,
Expense = e,
Name = c.Name
}
let dt = j.Expense.Created
group j by new { y = dt.Year, m = dt.Month } into g
select new Group<object, ReportSummaryViewModel>
{
Key = g.Key,
//Values = g.OrderBy(o => o.Category.Name),
//new code
Values = (from j in g
group j by g.Key.Name into g2
select new { Name = g2.OrderBy(k=> k.Category.Name) } ),
Total = g.Sum(a => a.Expense.Amount),
}
).ToList();
return View(data);
}
If I understand your problem correctly, my guess, is that you can't do this:
Name = g2.OrderBy(k=> k.Category.Name)
Here is my attempt:
Name = g2.OrderBy(k=> k.Category.Name)
Here is my attempt:
Values = (from j in g
group j by g.Key.Name into g2
select new { Name = g2.Category.Name) }
),
ASKER
Hmm.
1. I cannot use Name = c.Name in the first group because it messes-up the first group.
2. I cannot use Name in the second group...
3. Using 'Select g2.FirstOrDefault()' allows the program to run without error
- However, as expected, it only returns one record
[Latest Code]
Values = (from j in g
group j by g.FirstOrDefault().Categor y.Name into g2
select g2.FirstOrDefault()),
1. I cannot use Name = c.Name in the first group because it messes-up the first group.
2. I cannot use Name in the second group...
3. Using 'Select g2.FirstOrDefault()' allows the program to run without error
- However, as expected, it only returns one record
[Latest Code]
Values = (from j in g
group j by g.FirstOrDefault().Categor
select g2.FirstOrDefault()),
Let's review:
You need to group by year/month, and then for each year/month combination, you need to group by category, and sum amount.
Using my previous example (with replacements), here my attempt:
You need to group by year/month, and then for each year/month combination, you need to group by category, and sum amount.
Using my previous example (with replacements), here my attempt:
var query = from e in context.Expenses
join c in context.Categories
on e.CategoryID equals c.CategoryID
let j = new ReportSummaryViewModel
{
Category = c,
Expense = e,
Name = c.Name
}
group by new { Year = j.Expense.Year, Month = j.Expense.Month } into g1
select new ReportSummaryViewModel(
Category = g1.Category,
Expense = g1.Expense,
Name = g1.Name,
Date = g1.Year + "/" + g1.Month,
Sums = (from grouped in g1
group by grouped.Category into g2
select new { g2.Date, g2.Category, Sum = g2.Sum(x => x.Amount) }
)
)
};
ASKER
That one's a mess. I'll just use my hack-solution in the View because I'm out-of-time to complete this job. Thanks for you help...
ASKER
I give points for a great attempt... thx