Getting a amount of rows with same month with LINQ from an MVC model using dateTime, is it possible?

Question

I have this need to know how many rows have the same month from a table and I have no idea of how to do it. I thought I'd try some LINQ but I've never used it before so I don't even know if it's possible. Please help me out!

        public ActionResult returTest()
        {
            ViewData["RowsWithSameMonth"] = // I'm guessing I can put some LINQ here?

            var returer = from s in db2.ReturerDB select s;
            return View(returer.ToList());
        }

The ideal would be to get, maybe a two dimensional array with the month in the first cell and the amount of rows from the db in the second?

I'd like the result to be sort of :

    string[,] statistics = new string[,]
{
    {"2013-11", "5"},
    {"2013-12", "10"},
    {"2014-01", "3"}
};

Is this doable? Or should I just query the database and do a whole lot of stuff? I'm thinking that I can solve this on my own, but it would mean a lot of ugly code. Background: self taught C# developer at IT-company with 1 years experience of ugly codesmanship and no official degree of any kind.

EDIT

var returer = from s in db2.ReturerDB select s;

            var dateRange = returer.ToList();

            var groupedData = dateRange.GroupBy(dateRow => dateRow.ToString())
            .OrderBy(monthGroup => monthGroup.Key)
            .Select(monthGroup => new
            {
                Month = monthGroup.Key,
                MountCount = monthGroup.Count()
            });

            string test01 = "";
            string test02 = "";

            foreach (var item in groupedData)
            {
                test01 = item.Month.ToString();
                test02 = item.MountCount.ToString();
            }

In debug, test01 is "Namespace.Models.ReturerDB" and test02 is "6" as was expected, or at least wanted. What am I doing wrong?

Please always tag the LINQ flavor you use. Entity framework, I suppose? — Gert Arnold
– Gert Arnold, Commented Jan 31, 2014 at 9:11
christopher - you'll need to do a few things to get the new addition to work: firstly, you need to explicitly tell the GroupBy() method the exact field you are grouping by. This might be something like dateRange.GroupBy(dateRow => dateRow.MyDateColumn.ToString("yyyy-mm"). As you can see, you also need to limit the group by the format string for 'yyyy-mm'. hope this helps. I'll add it to my answer in case someone else happens to find it and needs the complete answer — jim tollan
– jim tollan, Commented Jan 31, 2014 at 13:26
Your edit leaves unclear what dateRow is, but I think the answer below should do. — Gert Arnold
– Gert Arnold, Commented Jan 31, 2014 at 19:39

Gert Arnold · Accepted Answer · 2014-01-31 19:37:36Z

5

You can do this:

var groupedData = db2.ReturerDB.GroupBy(r => new { r.Date.Year, r.Date.Month })
             .Select(g => new { g.Key.Year, g.Key.Month, Count = g.Count() })
             .OrderBy(x => x.Year).ThenBy(x => x.Month);
             .ToList();
var result = groupedData
             .ToDictionary(g => string.Format("{0}-{1:00}", g.Year, g.Month),
                           g => g.Count);

Which will give you

Key       Value
---------------
2013-11    5
2013-12   10
2014-01    3

(Creating a dictionary is slightly easier than a two-dimensional array)

This will work against a SQL back-end like entity framework of linq-to-sql, because the expressions r.Date.Year and r.Date.Month can be translated into SQL.

edited Jan 31, 2014 at 19:37

answered Jan 31, 2014 at 9:23

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jim tollan Over a year ago

+1 as well for making the distinction regarding sql backend transparency!!

jim tollan · Accepted Answer · 2014-01-31 13:27:37Z

1

with a nod to mehrandvd, here is how you'd achieve this using linq method chain approach:

var dateRange = { // your base collection with the dates};

// make sure you change MyDateField to match your won datetime field
var groupedData = dateRange
        .GroupBy(dateRow => dateRow.MyDateField.ToString("yyyy-mm"))
        .OrderBy(monthGroup => monthGroup.Key)
        .Select(monthGroup => new
        {
            Month = monthGroup.Key, 
            MountCount = monthGroup.Count()
        });

This would give you the results you required, as per the OP.

[edit] - as requested, example of how to access the newly created anonymous type:

foreach (var item in groupedData)
{
    Console.WriteLine(item.Month);
    Console.WriteLine(item.MountCount);
}

OR, you could return the whole caboodle as a jsonresult to your client app and iterate inside that, i.e the final line of your view would be:

return Json(groupedData, JsonRequestBehavior.AllowGet);

hope this clarifies.

edited Jan 31, 2014 at 13:27

answered Jan 31, 2014 at 9:20

jim tollan

22.5k4 gold badges53 silver badges64 bronze badges

2 Comments

ChristopherSirén Over a year ago

This solution compiles and gives no errors, but how do I access the values within "groupedData"?

ChristopherSirén Over a year ago

I'm still having trouble, I'll edit the OP with some more code.

mehrandvd · Accepted Answer · 2014-01-31 08:47:40Z

1

What you need is grouping. Considering you have a list of dates a solution would be this:

var dateRows = // Get from database

var monthlyRows = from dateRow in dateRows
                  group dateRow by dateRow.ToString("yyyy/mm") into monthGroup
                  orderby monthGroup.Key
                  select new { Month=monthGroup.Key, MountCount=monthGroup.Count };

// Your results would be a list of objects which have `Month` and `MonthCount` properties.
// {Month="2014/01", MonthCount=24}
// {Month="2014/02", MonthCount=28}

answered Jan 31, 2014 at 8:47

mehrandvd

9,18414 gold badges68 silver badges121 bronze badges

4 Comments

ChristopherSirén Over a year ago

dateRows, what does that need to be? I tried var dateRows = returer.ToList(); as per my code above, but that didn't work. What does it need to be?

mehrandvd Over a year ago

I assumed dateRows type to be a List<DateTime>.

ChristopherSirén Over a year ago

Aah, my bad, I'll try that now!

jim tollan Over a year ago

+1 - this would have been my approach too, tho i'm a sucker for method chaining, rather than than query syntax, but the same end result. I've added my version below for completeness.

Collectives™ on Stack Overflow

Getting a amount of rows with same month with LINQ from an MVC model using dateTime, is it possible?

3 Answers 3

1 Comment

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related