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

Question

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

Sergey Berezovskiy · Accepted Answer · 2014-07-11 09:34:07Z

1

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.

answered Jul 11, 2014 at 9:34

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Developer Over a year ago

can you show me as example to get the value based on key?

Sergey Berezovskiy Over a year ago

@sanjay simplest var count = result.First(x => x.Stat == "Not_entered").Count (it does not check if key exist). Also you can put all values into dictionary: var stats = result.ToDictionary(x => x.Key, x => x.Count) and use it as any other dictionary

Developer Over a year ago

i got it like int EMPLOYEES_NOT_SUBMITTED = dashboardDetails.Where(m => m.STAT == "NOT_SUBMITTED").Select(m => m.COUNT).FirstOrDefault();

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related