CODEWITHSUNDEEP
linqlinq-to-sql
Fergers
Fergers

Reputation: 583

SQL to LINQ query asp.net

I am currently trying to get some statistics for my website but i cant seem to create the query for my database to get the username that if found most frequent in all the rows.

The sql query should look something like this:

SELECT username FROM Views GROUP BY 'username' ORDER BY COUNT(*) DESC LIMIT 1

How do i make that query in my controller?

Upvotes: 1

Views: 270

Answers (4)

Amy B
Amy B

Reputation: 110201

You can't do LIMIT 1 (mysql), since LinqToSql only generates TSql from MSSqlServer.

Upvotes: 0

Gaurav Agrawal
Gaurav Agrawal

Reputation: 4431

(from a in Views
group a by a.username into b
let c = b.count()
orderby c descending
select a.username).take(1);

Your query conversion .....

Upvotes: 1

Albireo
Albireo

Reputation: 11095

This is how you do that query using LINQ:

var temp = (from a in Views
            group a.username by a.username into b
            orderby b.Count() descending
            select b.Key).Take(1);

Upvotes: 0

Ankur
Ankur

Reputation: 33657

var username = db.Views.GroupBy(v => v.username).OrderByDescending(g => g.Count()).First().Key

Upvotes: 4

Related Questions