0

I created a class which represents an aggregated record from database query: 

class list2d
{
    private string ser;
    private DateTime dat;
    private int howmuch;


    public list2d(string ser, DateTime dat, int howmuch)
    {
        this.ser = ser;
        this.dat = dat;
        this.howmuch = howmuch;
    }

    public string Ser { get; set; }
    public DateTime Dat { get; set; }
    public int HowMuch { get; set; }
}

So every object returns info about quantity (howmuch) one of distribution channels (ser) per day (dat) Then I created list<> of my objects and fill it with data from my database

List<list2d> listz = new List<list2d>();
foreach (DataRow row in dt.Rows)
{
    listz.Add(new list2d(
        row["SOURCE"].ToString(), (DateTime)row["DATE"], (int)row["HOWMUCH"]));
}

So right now I want to group these data by week (return_week(DATE) )and distribution channel and count it. As far as I searched stackoverflow it seems linq is correct "tool". Unfortunately I am not familiar with this feature but most of examples base on simply list of int or strings. In my case it is list of objects.

ekad
  • 14,436
  • 26
  • 44
  • 46
user3863616
  • 185
  • 1
  • 9
  • 24
  • 2
    How do you define "week" in this case? (In some cases it can be Monday to Sunday, in others Sunday to Saturday, for example.) Do you already have a method which extracts some sort of week value (e.g. the `DateTime` at the start of the week)? – Jon Skeet Sep 22 '14 at 11:00
  • Not to mention that week numbers aren't coherent either. Some cultures week 1 starts at the first Monday of the new year, in others week 1 is the first week that has a Thursday in the new year, etc. etc. Week numbers really don't like software developers. – Martijn Sep 22 '14 at 11:02
  • good point :) what about this piece of code? private int WeekNumber(DateTime dat) { DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo; Calendar cal = dfi.Calendar; DateTime dow = dat; return cal.GetWeekOfYear(dow, dfi.CalendarWeekRule, dfi.FirstDayOfWeek); } – user3863616 Sep 22 '14 at 11:03
  • This link will be helpful. http://stackoverflow.com/questions/5231845/c-sharp-linq-group-by-on-multiple-columns – Priyank Sep 22 '14 at 11:03
  • What's up with your naming conventions? `"So every object returns info about quantity (howmuch) one of distribution channels (ser) per day (dat)"` - why are you naming something `howmuch` instead of `quantity` as you've described? Or `Dat` instead of `Day`? How is the poor soul whos job it is to maintain this one day supposed to know that `Ser` = Distribution channel? – DGibbs Sep 22 '14 at 11:03
  • @user3863616 that code *still* doesn't clarify how you define a week - you are using the system default culture, how are we to know what that is? – James Sep 22 '14 at 11:05
  • ok, let's forget about week(Date) for now. If I group it by day it will be good enough. Priyank's link seems to be useful :) @DGibbs - you are right, shame on me. I am DBA and code only if I really must to (especially in c#). – user3863616 Sep 22 '14 at 11:18
  • @user3863616 to group by day then you can do `list.GroupBy(x => x.Dat.Date)` – James Sep 22 '14 at 15:42

1 Answers1

0

It can be done with the following Linq query

lst
   .GroupBy(x => GetWeek(x.Dat))
   .Select(x => new KeyValuePair<DayOfWeek, int>(x.Key, x.Count()))
   .ToList();

int GetWeek() { ... }

This will give you a list of keyvaluepairs where the key is the week and the value is the count for the given day.

Edit:

Just realized the question was to group on week, not the day of the week.

Cine
  • 4,255
  • 26
  • 46
  • I think OP wants `x.Sum(item => item.howmuch)` rather than `x.Count()`, but I will admit the question is not clear in that regard. – Martijn Sep 22 '14 at 11:25
  • well, your code returns pair of values but my expectation is to get trio if I forget for a while about "weeks" the code I expect should be something like: select date, source, count(howmuch) from group by date, source; – user3863616 Sep 22 '14 at 13:34
  • you mean sum of howmuch? It is just a matter of replacing the .Count with .Sum(x=>x.howmuch) – Cine Sep 23 '14 at 07:57