CODEWITHSUNDEEP
c#sqlentity-frameworklambda
Sean
Sean

Reputation: 907

Entity Framework Grouping Data

On my database I can run the following command:

SELECT Distinct ApprovalStatus,  Id,  COUNT (ApprovalStatus) total
from dbo.DummyTable
WHERE Id!= 0
group by ApprovalStatus, Id

And will generate this:

ApprovalStatus | Id | Count
---------------|----|-------
Approved       | 47 | 1423
Pending        | 47 | 4
Approved       | 46 | 1685
Pending        | 46 | 47

I'm wanting the same in my project. Initially, I'm returning everything from my database via simple Entity Framework logic.

Now that I have the data, I'm trying to split it up by attempting to translate the SQL code to a Lambda expression. This is what I have:

var count = data.GroupBy(d => d.Id)
            .Select(x => new
            {
               ID = x.Select(x=>x.Id.ToString()),
               Count = x.Select(x => x.ApprovalStatus == "Pending").Count()
            });

What I'm struggling with is getting my code to output the data in a similar way to how I have it in SQL. As at some point I want to display the data on a web page.

Upvotes: 0

Views: 133

Answers (1)

TheGeneral
TheGeneral

Reputation: 81483

You could do this

var results = data.GroupBy(x => new { x.ApprovalStatus, x.Id })
                  .Select(x => new
                         {
                            ApprovalStatus = x.Key.ApprovalStatus,
                            Id = x.Key.Id,
                            Count = x.Count()
                         });

Upvotes: 4

Related Questions