CODEWITHSUNDEEP
c#linqlinq-to-sql
SherCoder
SherCoder

Reputation: 688

Average numbers associated with same date and extract date from datetime using linq

I am trying to average data that is associated with same date and I am trying to pull just the date from datetime using linq. I can do this in mysql workbench and get the results I want:

select avg(pl.time), Date(pl.created_at) as date
from page_loads as pl
join sites as s
on pl.site_id = s.id
join test_runs as t
on pl.test_run_id = t.id
where s.3id = 17
group by date

how would I do this in linq. I have this so far:

var data = from pl in page_loads
           join s in sites 
           on pl.site_id equals s.id
           join t in test_runs
           on pl.test_run_id equals t.id
           where s.3id == 17
           select new { time = pl.time, created_at = pl.created_at };

This gives me the following results:

4034    2012-06-06 00:15:48
5649    2012-06-06 00:31:05
4275    2012-06-06 01:48:54
4352    2012-06-07 06:50:27
5672    2012-06-07 07:05:15
7510    2012-06-07 17:49:25
10366   2012-06-07 17:49:53
12193   2012-06-07 18:32:28
3681    2012-06-07 18:55:42
7951    2012-06-07 18:59:59
2273    2012-06-07 19:26:38
6816    2012-06-08 22:41:48
6918    2012-06-08 22:44:40
3702    2012-06-08 23:03:58
3065    2012-06-08 23:04:58
5981    2012-06-19 20:46:40
1695    2012-06-19 20:48:36
4323    2012-06-19 20:50:24

But I would like to average the numbers that associate with the same date and I would like to extract just the date from the datetime stamp.

Upvotes: 0

Views: 112

Answers (2)

user111013
user111013

Reputation:

The Date property on any DateTime will give you a DateTime for just that date.
That is: (new DateTime(2001,1,1,13,1,1)).Date should be equal to new DateTime(2001,1,1)

Something like this should work for in-memory objects: 

var data = from pl in page_loads
       join s in sites 
       on pl.site_id equals s.id
       join t in test_runs
       on pl.test_run_id equals t.id
       where s.3id == 17

       group pl by pl.created_at.Date into g 
       select new { time = g.Average(t=>t.time), created_at = g.Key };

Edit: The Stack Overflow question LINQ to Entities group-by failure using .date has an accepted answer with details on how to use a method appropriate for Entity Framework/LINQ to SQL.

You would need to modify the group line to be something like: 

       group pl by EntityFunctions.TruncateTime(pl.created_at) into g

But otherwise, the rest of the solution remains the same.

Upvotes: 0

Sharkz
Sharkz

Reputation: 458

What you are trying to do is get an average based on the grouping of date, but the LINQ that you have written does not include a group clause. A good place for you to start would be to look at LINQ examples. This LINQ Aggregate Operators will provide you with an example you can work with.

Upvotes: 1

Related Questions