Converting a SQL to Linq Query

Question

Currently I have SQL query like

 select tt.userId, count(tt.userId) from (SELECT userId,COUNT(userId) as cou
  FROM [dbo].[users]

  where createdTime> DATEADD(wk,-1,GETDATE())

   group by userId,DATEPART(minute,createdTime)/5) tt group by tt.userId

Now I have the Data in the Data Table, I need to convert the above the query to LINQ and execute against the data table. I am unable to do so , can anybody help me out.

This is what query does, It groups the users into 5 minutes time slots and then counts the number of timeslots per user.

Note : I am not able to use Linqer to create the Linq queries because this table does not exist in the database, it's a virtual one created dynamically.

@mohan111 can you please share the useful one. I installed Linqer and Linqpad but does not seems to get it working — Midhun Murali, Mar 01 '16 at 05:09
Try this one: http://weblogs.asp.net/hajan/linqer-a-nice-tool-for-sql-to-linq-transition — VVN, Mar 01 '16 at 05:14
@VVN mohan111 thanks guys but I don't have this table in my database, I just got the linqer working and it's states that the table does not exist in database . This is virtual table created at runtime — Midhun Murali, Mar 01 '16 at 05:26
@HariPrasad I have corrected the SQL query , Please see the latest — Midhun Murali, Mar 01 '16 at 05:53
Use this, `var result = db_users.Where(u=> u.atedTime> DATEADD(wk,-1,GETDATE())).GroupBy(g=> new { userid = g.userId, DATEPART(minute,g.createdTime)/5)}).Select(g=> { userid = g.Key.userid, count = g.Count()}).GroupBy(g=>g.userid).Select(s=> userid = s.Key.userid, count = s.Count());` — Hari Prasad, Mar 01 '16 at 05:59
You need to include the namespace at the top, `using System.Linq; using System.Collections; using System.Collections.Generic;` — Hari Prasad, Mar 01 '16 at 06:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104958/discussion-between-midhun-murali-and-hari-prasad). — Midhun Murali, Mar 01 '16 at 06:05
@HariPrasad ,The DataTable does not seems to have a extension method Where() , I hope your example is using Dataset . Anyway I have add the namespace but it's not working — Midhun Murali, Mar 01 '16 at 06:08
This is how to get into the Where method. `yourDatatable.Rows.Cast().Where(expression)` — drunkcoder, Mar 01 '16 at 06:25

score 2 · Accepted Answer · answered Mar 01 '16 at 06:54

2

Bit complex query, giving my best to make it work.

var result = table.AsEnumerable().Where(u=> u.Field<DateTime>("createdTime") > DateTime.Now.AddDays(-7)) //subtract a week
        .GroupBy(g=> new { userid = g.Field<string>("userId") , span = g.Field<DateTime>("createdTime").Minute })
        .Select(g=> new { userid = g.Key.userid, count = g.Count()})
        .GroupBy(g=> g.userid ).Select(s=> new {userid = s.Key, count = s.Count()});

Working Demo

answered Mar 01 '16 at 06:54

Hari Prasad

16,716
4
21
35

Thanks Hari ,Let me check this – Midhun Murali Mar 01 '16 at 07:04
One thing span = g.Field("createdTime").Minute } is actually span = g.Field("createdTime").Minute/5 } , split data in 5 minute time slots – Midhun Murali Mar 01 '16 at 09:50

score 1 · Answer 2 · answered Mar 01 '16 at 07:29

This SQL can be rewritten like this

SELECT
  COUNT(U.UserId),
  U.[createdTime]
FROM USERS U WHERE createdTime> DATEADD(wk,-1,GETDATE())
GROUP BY U.UserId,
         DATEPART(MONTH, U.[createdTime]),
         DATEPART(DAY, U.[createdTime]),
         DATEPART(HOUR, U.[createdTime]),
         (DATEPART(MINUTE, U.[createdTime]) / 5)

And its corresponding Linq for DataTable would be

var users =  myDataTable.AsEnumerable()
                .Select(r=> new {
                    UserId = r.Field<int>("UserId"),
                    CreatedTime = r.Field<DateTime>("createdTime")
                }).ToList();
            var groupedUsersResult = from user in users where user.CreatedTime > user.CreatedTime.AddDays(-7) group user by 
                                   new {user.CreatedTime.Year,user.CreatedTime.Month,user.CreatedTime.Day,Minute=(user.CreatedTime.Minute/5),user.UserId}
                                   into groupedUsers select groupedUsers;

Fiddle is here

score 0 · Answer 3 · answered Mar 01 '16 at 06:05

0

I will suggest to use LINQPad4. It would be easy to do that and that will help you a lot in writing LINQ queries. https://www.linqpad.net/

answered Mar 01 '16 at 06:05

Sagar

272
1
4
13

I don't have the tables in the Database to create the query using linqpad/linqer – Midhun Murali Mar 01 '16 at 06:12

Converting a SQL to Linq Query

3 Answers3