none
How to group data by year, month and date? RRS feed

  • Question

  • I am developing an application in MVC using Entity Framework in C#. I have two tables as under:

    Table 1: tblCase (CaseId primary key, CaseNo)

    Table 2: tblNextHearing (HearingId primary key, CaseId Foreign key, Date)

    I want to display Case No grouped by Year, Month and Date as shown below:

    Example:

    2016

         May

            05/05/2016

    1. CaseNo 1
    2. CaseNo 2

    August

    03/08/2016

    • CaseNo 1
    • CaseNo 3
    • CaseNo 4

    2015

        June

            05/06/2015

    1. CaseNo 1
    2. CaseNo 2

    August

    03/08/2015

    • CaseNo 1
    • CaseNo 3
    • CaseNo 4

    05/08/2015

    • CaseNo 3

    Here is the ViewModel:

    using CCIS.Models;
    
    namespace CCIS.ViewModel
    {
        public class Archive
        {
            public string HearingDate { get; set; }
            public IEnumerable<tblCase> AllCases { get; set; }
            public int Year { get; set; }
            public int Month { get; set; }
            public string MonthName
            {
                get
                {
                    return CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(this.Month);
                }
            }
            public int Total { get; set; }
        }
    }


    Here is the Controller:

    public ActionResult Archive()
            {
                var getCase = db.tblCases.Select(p => p.fldCaseId);
    
                 db.tblNextHearings.Where(p => getCase.Contains(p.fldCaseId))
                    .GroupBy(o => new
                    {
                        Month = o.fldDate.Month,
                        Year = o.fldDate.Year,
                        Date = o.fldDate
                       
                    });
    
                var model = db.tblNextHearings
                    .GroupBy(t => t.fldDate)
                    .Select(g => new Archive
                    {
                        Month = g.Key.Month,
                        Year = g.Key.Year,
                        Total = g.Count(),
                        HearingDate = g.Key.ToString(),
                        AllCases = db.tblCases.Where(c => g.Select(h => h.fldCaseId).Contains(c.fldCaseId)).ToList()
                        
                    })
                    .OrderByDescending(a => a.Year)
                    .ThenByDescending(a => a.Month)
                    .ThenByDescending(a => a.HearingDate)
                    .ToList();
    
                return View(model);
    
            }


    Here is the View:

     @foreach (var group in Model.GroupBy(m => m.Year))
             
         {
            <h3 class="natrigger" data-toggle="collapse" data-target="#collapse-4">@group.Key</h3>
           <div>
             
               @foreach (var item in group.GroupBy(m => m.MonthName))
                   
                   {
            <h4 class="natrigger" data-toggle="collapse" data-target="#collapse-4"> @item.Key</h4>
                   
                   foreach (var vdate in item.GroupBy(m => m.HearingDate))
                       
                   {
                       <h4 class="natrigger" data-toggle="collapse" data-target="#collapse-4"> @vdate.Key</h4>
                 <table class="table">
              
                <tr>
                    <th width="300">
                         Row #
                    </th>
                    <th>
                        Case No
                    </th>
                    
            
        </tr>
    
                @{ int a = 0; } 
                @foreach (var caseno in vdate)
                {
                    <tr>
                        <td>
                            @(++a)
                        </td>
                        <td>
                            @caseno.AllCases.Select(m => m.fldCaseNo)
                            
    
                        </td>
                        <td>
                            @Html.ActionLink("Details", "SharedCaseDetails", new { id=caseno.AllCases.Select(m => m.fldCaseId)})
                        </td>
                        
                    </tr>
                
                }
        
    </table>
               }
               
               }
        </div>  
           <hr />          
         }
        

    Years, months and dates are getting grouped properly but the query or the view does not display Case No as required. under each date only a single row is retrieved which displays "System.Linq.Enumerable+WhereSelectListIterator`2[CCIS.Models.tblCase,System.String]" instead of CaseNo.

    Please help.


    Friday, August 19, 2016 12:53 PM

Answers

  • Hi ArunKhatri,

    According to your description and related code snippet. I create a demo and reproduce your issue on my side. it seems

    that you need to iterate caseno.AllCases. like this:

    #View

    @model IEnumerable<MVCDemo.Models.Archive>
    
    @{
        ViewBag.Title = "Archive";
    }
    <h2>Archive</h2>
    @foreach (var group in Model.GroupBy(m => m.Year))
    {
        <h3 class="natrigger" data-toggle="collapse" data-target="#collapse-4">@group.Key</h3>
            <div>
                @foreach (var item in group.GroupBy(m => m.MonthName))
                {
                    <h4 class="natrigger" data-toggle="collapse" data-target="#collapse-4"> @item.Key</h4>
                    foreach (var vdate in item.GroupBy(m => m.HearingDate))
                    {
                        <h4 class="natrigger" data-toggle="collapse" data-target="#collapse-4"> @vdate.Key</h4>
                        <table class="table">
    
                            <tr>
                                <th width="300">
                                    Row #
                                </th>
                                <th>
                                    Case No
                                </th>
                            </tr>
                            @{ int a = 0; }
                            @foreach (var caseno in vdate)
                            {
                                foreach(var t in caseno.AllCases.ToList())
                                {
                                        <tr>
                                            <td>
                                                @(++a)
                                            </td>
                                            <td>
                                          
                                            @t.CaseNo
                                            </td>
                                            <td>
                                                @Html.ActionLink("Details", "SharedCaseDetails", new { id = t.CaseId})
                                            </td>
                                        </tr>
                                }
                            }
                        </table>
                                    }
    
                                }
            </div>
            <hr />
                                }
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ArunKhatri Wednesday, August 31, 2016 2:25 PM
    Wednesday, August 31, 2016 2:19 AM
    Moderator

All replies

  • Years, months and dates are getting grouped properly but the query or the view does not display Case No as required. under each date only a single row is retrieved which displays "System.Linq.Enumerable+WhereSelectListIterator`2[CCIS.Models.tblCase,System.String]" instead of CaseNo.

    You are displaying the object instead of displaying a property in the object. I suggest that put a debug breakpoint in the code and actually look at the content of the object to figure out how to address it.

    Saturday, August 20, 2016 3:41 PM
  • Hi ArunKhatri,

    According to your description and related code snippet. I create a demo and reproduce your issue on my side. it seems

    that you need to iterate caseno.AllCases. like this:

    #View

    @model IEnumerable<MVCDemo.Models.Archive>
    
    @{
        ViewBag.Title = "Archive";
    }
    <h2>Archive</h2>
    @foreach (var group in Model.GroupBy(m => m.Year))
    {
        <h3 class="natrigger" data-toggle="collapse" data-target="#collapse-4">@group.Key</h3>
            <div>
                @foreach (var item in group.GroupBy(m => m.MonthName))
                {
                    <h4 class="natrigger" data-toggle="collapse" data-target="#collapse-4"> @item.Key</h4>
                    foreach (var vdate in item.GroupBy(m => m.HearingDate))
                    {
                        <h4 class="natrigger" data-toggle="collapse" data-target="#collapse-4"> @vdate.Key</h4>
                        <table class="table">
    
                            <tr>
                                <th width="300">
                                    Row #
                                </th>
                                <th>
                                    Case No
                                </th>
                            </tr>
                            @{ int a = 0; }
                            @foreach (var caseno in vdate)
                            {
                                foreach(var t in caseno.AllCases.ToList())
                                {
                                        <tr>
                                            <td>
                                                @(++a)
                                            </td>
                                            <td>
                                          
                                            @t.CaseNo
                                            </td>
                                            <td>
                                                @Html.ActionLink("Details", "SharedCaseDetails", new { id = t.CaseId})
                                            </td>
                                        </tr>
                                }
                            }
                        </table>
                                    }
    
                                }
            </div>
            <hr />
                                }
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ArunKhatri Wednesday, August 31, 2016 2:25 PM
    Wednesday, August 31, 2016 2:19 AM
    Moderator