CODEWITHSUNDEEP
sqlsql-serverlinq
Axel Andersen
Axel Andersen

Reputation: 350

Translate sql with COUNT into LINQ

I can't seem to find a way to translate this sql into LINQ. It will give me all unique rows, and count the value of NumberOf field in each row.

SELECT Id, SupId, Text, ExternalId, NumberOf, count(s.NumberOf)
FROM Stock s
GROUP BY s.Id, s.SupId, s.Text, s.ExternalId, s.NumberOf

Can anyone help me with this?

Upvotes: 0

Views: 74

Answers (2)

qxg
qxg

Reputation: 7036

Group your data by an anonymous class.

var query = from s in Stock 
            group s by new {s.Id, s.SupId, s.Text, s.ExternalId} into g
            select new {g.Key.Id, g.Key.SupId, g.Key.Text, g.Key.ExternalId, Count = g.Count()};

Upvotes: 0

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

you have to do like this:

var consolidatedChildren =
    from s in Stock
    group s by new
    {
        s.Id, 
        s.SupId, 
        s.Text, 
        s.ExternalId, 
        s.NumberOf
    } into gs
    select new
    {
        Id = gs.Key.School,
        SupId =gs.Key.SupId, 
        Text = gs.Key.Text, 
        ExternalId =gs.Key.ExternalId, 
        NumberOf =gs.Key.NumberOf,
        Count = gs.Count()  //--------------> Count of Rows
    };

Upvotes: 1

Related Questions