Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MVC 4 LINQ IGrouping IEnumerable Cast Issue?

Posted on 2013-11-20
9
Medium Priority
?
3,053 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

722 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