CODEWITHSUNDEEP
sqlsql-serverlinqgroup-by
Developer
Developer

Reputation: 888

how to write linq "group by" and get count based on row in asp.net mvc

I have two tables timesheet and timesheet_log

In timesheet i have three columns id,comp_id,emp_id and in timesheet_log table, i have three columns id, timesheet_id, stat.

Now I want to group all the "STAT" column by "COMP_ID", for that i wrote query in sql

select tlog.stat,count(*) as count  from  timesheet ts
join
timesheet_log  tlog on ts.id = tlog.timesheet_id
where comp_id=10//For ex.
group by tlog.stat

In stat column contains each rows like "Not_Entered","Not_submitted" etc.., when i executing above query in sql server,i getting result like

result:
Stat               Count
Not_entered       10
Not_Submitted     8...
..................
-------------------

Now, I want to write the query in linq and assign variable count of each row to a varaible like:

if Not_entered count in 10 then
int emp_not_entered= //Count of Not entered.

I have tried in linq like 

        var result= (from timesheet in reslandentity.TIMESHEET
                                join tlog in reslandentity.TIMESHEET_LOG on timesheet.ID equals tlog.TIMESHEET_ID
                                group tlog by tlog.STAT into g

                                select   g).ToString();

But not getting what i expected, Help me anyone

Upvotes: 2

Views: 1861

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

You can access grouping key (value of STAT in your case) via Key property of grouping. To get count of items in each group just call Count() on group:

var result = from timesheet in reslandentity.TIMESHEET
             join tlog in reslandentity.TIMESHEET_LOG 
                  on timesheet.ID equals tlog.TIMESHEET_ID
             group tlog by tlog.STAT into g
             select new { Stat = g.Key, Count = g.Count() };

Keep in mind - this query will return IQueryable of some anonymous type with two properties - Stat and Count. Calling ToString() makes no sense here, because you will just get type name.

Upvotes: 1

Related Questions