CODEWITHSUNDEEP
c#linqdatetimegroup-bylinq-to-objects
LukaVazic
LukaVazic

Reputation: 25

Grouping and setting conditions for list query

I have a list of Status objects carrying int value and datetime in format of

Tue Sep 30 21:22:02 +0000 2014

Code:

public class Status
{
    public string created_at { get; set; }

    public int value { get; set; }
}

Now, I would like to display this information on chart, but for the last 7 days only.

How can I group objects by date in C# and manage to get this information? I would know how to do it in SQL, but I am not familiar with LINQ.

Upvotes: 0

Views: 116

Answers (3)

The One
The One

Reputation: 4784

I guess you need to group Status to sum or count some other field on your class, so you can get a total of something by day.

If so, given the following example

        List<Status> statusList = new List<Status>();
        statusList.Add(new Status() { Value = 12, CreatedAt = "Tue Sep 30 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 11, CreatedAt = "Tue Sep 30 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 10, CreatedAt = "Tue Sep 30 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 9, CreatedAt = "Sat Sep 27 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 8, CreatedAt = "Fri Sep 26 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 7, CreatedAt = "Thu Sep 25 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 6, CreatedAt = "Wed Sep 24 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 5, CreatedAt = "Tue Sep 23 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 4, CreatedAt = "Mon Sep 22 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 3, CreatedAt = "Sun Sep 21 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 2, CreatedAt = "Sat Sep 20 21:22:02 +0000 2014" });
        statusList.Add(new Status() { Value = 1, CreatedAt = "Fri Sep 19 21:22:02 +0000 2014" });



        var groups = from status in statusList
                  let date = DateTime.ParseExact(status.CreatedAt, "ddd MMM dd HH:mm:ss zzz yyyy", CultureInfo.InvariantCulture)
                  group status by date into g
                  where g.Key.Date >= DateTime.Now - new TimeSpan(7, 0, 0, 0)
                  select g;

        foreach (IGrouping<DateTime, Status> group in groups)
        {
            DateTime day = group.Key;
            int countOfValues = group.Select(g => g.Value).Count();
            Console.WriteLine("Day: {0}", day.ToString());
            Console.WriteLine("countOfValues: {0}", countOfValues.ToString());
        }

It prints:

Day: 9/30/2014 4:22:02 PM
countOfValues: 3 //Three entries on Sep 30...
Day: 9/27/2014 4:22:02 PM
countOfValues: 1
Day: 9/26/2014 4:22:02 PM
countOfValues: 1
Day: 9/25/2014 4:22:02 PM
countOfValues: 1
Day: 9/24/2014 4:22:02 PM
countOfValues: 1

Upvotes: 1

Marek Woźniak
Marek Woźniak

Reputation: 1776

if you are using int (it's pretty bad, but ok..) then first of all you must convert int to datetime: search on google int to datetime.

The second step's to implement TimeSpan and use overriden operators for Datetime and TimeSpan.

public bool LaterThan7Days(int valueFromClass)
{
    DateTime myTime = //here convert valueFromClass to Datetime, search in google please
    TimeSpan t = new TimeSpan(7,0 , 0, 0); //days, hours ,mins, seconds
    DateTime timeToCompare = myTime + t;
    return timeToCompare > DateTime.Now;
}

and the query should looks like:

var query = from Status in yourContext
            where LaterThan7Days(Status.value)
            select Status;

query.ToList().Foreach(p=> Console.WriteLine(p.created_at));

Upvotes: 0

Bhasyakarulu Kottakota
Bhasyakarulu Kottakota

Reputation: 833

Try add following to yours where condition then yours SP/SQL Query will return only records within 7 days.

datediff(day, created_at, getdate()) < 7

Upvotes: 0

Related Questions