NIRANJAN G NIRANJAN G - 2 months ago 5x
C# Question

Linq group by query with count and other fields in mvc4

I am writing linq query to get some details from table. I want to get count of

for each

My table structure is as follows

ID clientId clientName empID docStatus
1 IN1001 Infy 100 Verified
2 IN1001 Infy 101 notVerified
3 IN1001 Infy 102 notVerified

After querying I want data in the below form

clientID clientName Count
IN1001 Infy 2

This is what i tried below. I am able to get count but where i should put where condition in my below query.

var noofRecords = (from c in db.ts_upld_doc
group c by c.upld_docid into grouping
select new
key = grouping.Key,
Count = grouping.Count()
return noofRecords.Count();

thank you in advance.


Probably you'll what to place the where clause before the grouping (why to group these items that you are filtering out). Then, if you want the output as you specified, the easiest will be to group both by the clientID and clientName.

Also in your above query you are grouping by the upld_docid (ID) field - which is a unique field in your data - meaning all your groups have a size of 1 - as if you didn't group.

See below query, should do what you want:

var noofRecords = (from c in db.ts_upld_doc
                   where c.docStatus == "notVerified"
                   group c by new { c.clientID, c.clientName }  into grouping
                   select new
                        ClientId = grouping.Key.clientID,
                        ClientName = grouping.Key.clientName,
                        Count = grouping.Count()