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!!!
WorknHardrAsked:
Who is Participating?
 
Bob LearnedCommented:
Here is an example of a nested group:

var query = from bug in RawListData
            group bug by new bug.Category into grouped
            select new { 
                Category = grouped.Category,
                Counts = from bug in grouped
                         group bug by grouped.Priority into g2
                         select new { Priority = g2.Key, Count = g2.Count() }
            };

Open in new window

0
 
Bob LearnedCommented:
You are grouping by year/month, but you really also need to group by category, too.
0
 
WorknHardrAuthor Commented:
Yes, it's a little more complicated than a normal group.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
WorknHardrAuthor Commented:
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.Generic.IEnumerable<System.Linq.IGrouping<string,ReportExpenseDetailViewModel>>' to 'System.Collections.Generic.IEnumerable<ReportExpenseDetailViewModel>'.
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.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<ReportExpenseDetailViewModel>'.
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);
        }
0
 
Bob LearnedCommented:
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:

   Values = (from j in g
                      group j by g.Key.Name into g2
                      select new { Name = g2.Category.Name) } 
                   ),

Open in new window

0
 
WorknHardrAuthor Commented:
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().Category.Name into g2
                  select g2.FirstOrDefault()),
0
 
Bob LearnedCommented:
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:

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) }
                                           )
                        )
            };

Open in new window

0
 
WorknHardrAuthor Commented:
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...
0
 
WorknHardrAuthor Commented:
I give points for a great attempt... thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.