LINQ group-by and then display datetime for (dd mmm)

Question

I want Linq to group by date but display in text

here is my code

var groups = _uow.Orders.GetAll()
            .Where(x => x.Created > baselineDate)
            .GroupBy(x => x.Created.ToString("yyyy-MM-dd"));

var orders = new
        {
            Day = groups.Select(g => g.Key).ToArray(),
            Total = groups.Select(g => g.Sum(t => t.Total)).ToArray(),
        };

the result is (not good to put in label of graph)

 {"Day": [2, 3, 4, 5], "Total": [9999.00, 9999.00, 9999.00, 9999.00] }

But i want this (Monthly)

"Day": ['Jan', Feb', 'Mar', 'Apr'], "Total": [9999.00, 9999.00, 9999.00, 9999.00] }

Or Daily

"Day": ['Jan 1', 'Jan 2', 'Jan 3', 'Jan 4'], "Total": [9999.00, 9999.00, 9999.00, 9999.00] }

Please advice me for DateTime.ToString() that i can play with.

Thank you all.

score 4 · Answer 1 · answered Jul 05 '13 at 15:51

Thank you all for helping. I found the answer.

add

.AsEnumerable()

AND

 .GroupBy(x => x.Created.ToString("MMM d"));

here is full code

var groups = _uow.Orders.GetAll()
            .Where(x => x.Created > baselineDate)
            .AsEnumerable()
            .GroupBy(x => x.Created.ToString("MMM d"));

        var orders = new
        {
            Day = groups.Select(g => g.Key).ToArray(),
            Total = groups.Select(g => g.Sum(t => t.Total)).ToArray(),
        };

score 2 · Answer 2 · edited May 23 '17 at 12:16

2

This question describes how to convert a date time to the string name of the month.

EDIT: for EF, we have to pull everything into memory before doing any string logic:

var orders = _uow.Orders.GetAll()
    .Where(x => x.Created > baselineDate)
    // pull into memory here, since we won't be making the result set any smaller and
    // we want to use DateTime.ToString(), which EF won't compile to SQL
    .AsEnumerable()
    // this will group by the whole date. If you only want to group by part of the date,
    // (e. g. day or day, month), you could group by x => x.Date.Month or the like
    .GroupBy(x => x.Date)
    .Select(g => new {
        // or use just "MMM" for just the month
        Dates = g.Select(g => g.Key.ToString("MMM d")).ToArray(),
        Total = ...
    });

edited May 23 '17 at 12:16

Community

1
1

answered Jul 04 '13 at 18:46

ChaseMedallion

20,860
17
88
152

Yeah, I know how use ToString("MMMM") or any others stringFormat. but it doesn't work when i used LINQ to group-by. – riseres Jul 04 '13 at 18:52
@riseres are you using linq to objects? Linq to SQL? EF? NHibernate? – ChaseMedallion Jul 04 '13 at 18:58
Thank You very much. @ChaseMedallion but i got error in code. g.**Select**(g => g.**Key**.ToString("MMM d")).ToArray() how to fix it? – riseres Jul 04 '13 at 19:03
It shows this==> ExceptionMessage":"LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression." – riseres Jul 04 '13 at 19:14
@riseres: try my updated code. I've added the necessary AsEnumerable() line for EF – ChaseMedallion Jul 04 '13 at 21:58
@ChaseMedallion Thank you I got the solution, using AsEnumerable() – riseres Jul 05 '13 at 15:52

score 0 · Answer 3 · answered Jul 04 '13 at 19:42

You need to use functions that EF can understand and translate to SQL, such as EntityFunctions.TruncateTime. Here is your updated code:

var groups = _uow.Orders.GetAll()
            .Where(x => x.Created > baselineDate)
            .GroupBy(x => EntityFunctions.TruncateTime(x.Created));

Then use the string formatting just in the output, but make sure you're doing it after the value has been returned to .Net, using AsEnumerable to switch to Linq-to-Objects:

var orders = new
        {
            Day = groups.Select(g => g.Key)
                        .AsEnumerable()
                        .Select(x => x.ToString("MMM d"))
                        .ToArray(),
            Total = groups.Select(g => g.Sum(t => t.Total)).ToArray(),
        };

LINQ group-by and then display datetime for (dd mmm)

3 Answers3

Linked