GTown-Coder GTown-Coder - 27 days ago 5
C# Question

GroupBy() getting duplicate records

Let's say I have database table called

TestRecords
, and the model looks like this:

public class TestRecords
{
public int Id {get;set;}
public Date DateEntered {get;set;}
public int SecId {get;set;}
public int PhoneCallsTaken {get;set;}
}


Now, I have a main viewmodel that looks like this:

public class MyMainViewModel
{
public string Location {get;set;}
public IEnumerable<MyChildViewModel> Children {get;set;}
}


And my Child View Model:

public class MyChildViewModel
{
public string Month { get; set; }
public string Year { get; set; }
public double TotalPhoneCalls { get; set; }
}


Let's say in the database there are records in the
TestRecords
table.

TestRecords

ID | DateEntered | SecId | PhoneCallsTaken
-------------------------------------------------------------
1 8/1/2017 2 35
2 8/2/2017 2 30
5 9/1/2017 2 30


What I want my outcome to be:

I would like to group those records by the
SecId
which I know is just
db.TestRecords.GroupBy(x => x.SecId)
.. that is not my confusion.

Here is what I would expect as an outcome where
SecId
equals 2

2

Month | Year | PhoneCallsTaken
------------------------------------------
August 2017 65
September 2017 30


Here is what I am receiving:

2

Month | Year | Total Phone Calls
------------------------------------------
August 2017 65
August 2017 65
September 2017 30


It is repeating the August Total because there are 2 records in the database where
SecId
equals 2 and the Month is August.

Here is my C# code:

var data = db.TestRecords.ToList();

IEnumerable<MyMainViewModel> model = data.GroupBy(x => x.Section.SectionName)
.Select(y => new MyMainViewModel()
{
Location = y.Key,
Children = y.Select(z => new MyChildViewModel()
{
Month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(z.DateEntered.Month),
Year = z.DateEntered.Year.ToString(),
TotalPhoneCalls = y.Where(t => t.Section.SectionName == z.Section.SectionName)
.Sum(t => t.PhoneCallsTaken)
}).Distinct()
});


How can I get records that are entered in the same month/year be distinct?

Any help is appreciated.

Answer Source

Here is the basic gist of what I mentioned in the comments.

var model = data.GroupBy(_ => _.Section.SectionName)
    .Select(section => new MyMainViewModel {
        Location = section.Key,
        Children = section.GroupBy(_ => new {
            Month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(_.DateEntered.Month),
            Year = _.DateEntered.Year.ToString()
        }).Select(period => new MyChildViewModel {
            Month = period.Key.Month,
            Year = period.Key.Year,
            TotalPhoneCalls = period.Sum(_ => _.PhoneCallsTaken)
        }).ToList()
    });

You initially grouped by section. Now you additionally want to group the sections by month and year and then sum the calls for those groups.