Solved

MVC 4 LINQ IGrouping IEnumerable Cast Issue?

Posted on 2013-11-20
9
3,007 Views
Last Modified: 2016-02-10
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
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39665680
You are grouping by year/month, but you really also need to group by category, too.
0
 

Author Comment

by:WorknHardr
ID: 39665940
Yes, it's a little more complicated than a normal group.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 39666829
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:WorknHardr
ID: 39667072
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39667333
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
 

Author Comment

by:WorknHardr
ID: 39667496
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39667727
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
 

Author Comment

by:WorknHardr
ID: 39667825
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
 

Author Closing Comment

by:WorknHardr
ID: 39671896
I give points for a great attempt... thx
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question