CODEWITHSUNDEEP
c#sql-serverlinq
jensengar
jensengar

Reputation: 6167

Grouped Counts With linq

I have a table with visitor browser information that I am trying to use on an analytics page. The table contains BrowserType, BrowserName, and BrowserVersion. I have built the query using native sql but am struggling to convert it to linq.

Here is my sql query: 

SELECT COUNT(1) AS visit, s.BrowserType, s.BrowserName, s.BrowserVersion FROM Stats s GROUP BY s.BrowserType, s.BrowserName, s.BrowserVersion;

This gives me a result set that looks like this:

Query Results

The stats table obviously contains other data such as dates and users information, but assume I have already queried for the relevant rows/objects and just need to get the count information from it. If I do something like this, I get errors in my select clause:

var groupedResults = from r in results
                    group r by new { BrowserType = r.BrowserType, BrowserName = r.BrowserName, BrowserVersion = r.BrowserVersion } into g
                    select new
                    {
                        Name = g.BrowserName,
                        Version = g.BrowserVersion,
                        Count = g.Count()
                    });

Upvotes: 1

Views: 75

Answers (3)

DKR
DKR

Reputation: 5734

var groupedResults = from r in results
                    group r by new { r.BrowserType, r.BrowserName,r.BrowserVersion } into g
                    select new
                    {
                        Type=g.Key.BrowserType,
                        Name = g.Key.BrowserName,
                        Version=g.Key.BrowserVersion,
                         Count=g.Count()

                    });

Hope it will help you

Upvotes: 0

The Smallest
The Smallest

Reputation: 5773

You need to use Key property of IGrouping interface. Using chain syntax it would be like this:

var groupedResults = results
    .GroupBy(x => new { x.BrowserType, x.BrowserName, x.BrowserVersion})
    .Select(x => new {x.Key.BrowserType, x.Key.BrowserName, x.Key.BrowserVersion})
    .ToArray();

Upvotes: 1

XenoPuTtSs
XenoPuTtSs

Reputation: 1284

Using my customer table I did this. Is this what you are looking for?

var f = from c in Customer
                    .GroupBy(b=>new { b.Firstname,b.Lastname})
                    .Select(g => new { 
                             Metric = g.Key, 
                             Count = g.Count() 
                   })
        select c;

which generated the T-SQL

SELECT COUNT(*) AS [Count], [t0].[firstname] AS [Firstname], [t0].[lastname] AS [Lastname]
FROM [customer] AS [t0]
GROUP BY [t0].[firstname], [t0].[lastname]

Upvotes: 0

Related Questions