So, I’m really enjoying using LINQ and specifically LINQ2SQL. I’ve got a current problem where I want to get from a long list of loads (potentially hundreds per day), a short list of days in reverse order, that have a certain number or more of loads.
So, How to do this? Well, first, here is the answer:
1: /// <summary>
2: /// Returns a list of days that have a loadCount > loadCount
3: /// </summary>
4: /// <param name="days"></param>
5: /// <param name="loadCount"></param>
6: /// <returns></returns>
7: public List<DateTime> GetLastDaysWithLoadsGreaterThan(int days,int loadCount)
8: {
9: var dateGroups = (from load in _meta.Load
10: orderby load.DateCreated
11: group load by load.DateCreated
12: into g
13: where g.Count() > loadCount
14: select new
15: {
16: g.Key,
17: cnt = g.Count()
18: }).Take(days).OrderByDescending(f => f.Key);
19:
20: var listDates = dateGroups.Select(a => a.Key).ToList();
21:
22: return listDates;
23: }
And now, a short explanation.
First, the obvious I thought would have been if I did orderby load.DateCreated descending, that I would not have had to reverse the list at the end. It did not work and I’m not sure why. Luckily, I can sort the list at the very end which is a small penalty since my lists are not that large.
Notice the groupby and the where clause are doing the work. The resulting anonymous method solves the problem of getting the data out.
Line 20 is one of my favorite tricks to avoid 4 or 5 lines of code.
And of course, the SQL is a lot simpler, but not type safe.
1: SELECT datecreated,
2: count(*)
3: FROM dbo.[Load]
4: group by datecreated
5: having count(*) > 200
6: order by datecreated desc
7: