CODEWITHSUNDEEP
c#linqasp.net-mvc-4
NIRANJAN G
NIRANJAN G

Reputation: 69

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 docStatus with "notVerified" for each clientID.

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.

Upvotes: 1

Views: 1435

Answers (2)

Gilad Green
Gilad Green

Reputation: 37299

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()
                   }).ToList();

Upvotes: 1

Zein Makki
Zein Makki

Reputation: 30022

You can place the where in multiple places, one option:

var noofRecords = (from c in db.ts_upld_doc
                   where c.docStatus == "notVerified"
                   group c by c.upld_docid into grouping
                   select new
                   {
                        key = grouping.Key,
                        Count = grouping.Count()
                   });

return noofRecords.Count();

Be Aware !! You're returning the number of the records, and not the Property Count the you set to grouping.Count()

To get the expected results, you should do the following:

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()
                   });

return noofRecords.ToList();

Upvotes: 0

Related Questions