• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3169
  • Last Modified:

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!!!
0
WorknHardr
Asked:
WorknHardr
  • 5
  • 4
1 Solution
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now