Solved

MVC 4 LINQ IGrouping IEnumerable Cast Issue?

Posted on 2013-11-20
9
2,899 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now