Retrieving count of records from SQL table in MVC

Question

I have a piece of sql similar to the code below that I wish to execute in my MVC site and display the results in the view:

select top 10 username, count(*)
from databasetable
group by username
order by count(*) desc

I've seen in many places that Entity Framework is the best way to do this, but i'm struggling to get it started and how to integrate it into my site. Can someone give me a push in the right direction on how to do it?

Thanks

'Best' is very subjective, and depends on - amongst other factors - what you're trying to do, what you're working with. For example, are you starting a project from scratch? using an existing database? — markpsmith
– markpsmith, Commented Nov 26, 2014 at 10:53
Its an existing database that is used for another system, I just want to run a few queries against it in my own site for checking purposes. The site i'm building is from scratch, though the database has been in place for some time — jimminybob
– jimminybob, Commented Nov 26, 2014 at 11:46
If you're using it as opportunity to learn Linq, then you could use LinqToSQL classes, or if keen to use Entity Framework, then a database-first approach would be a good option. Either way, I think a tutorial would be your first step e.g linqtosql or db-first EF — markpsmith
– markpsmith, Commented Nov 26, 2014 at 12:03

StackTrace · Accepted Answer · 2014-11-26 11:02:45Z

1

If you want to run queries using raw SQL directly against the database then have a look at the code snippet below.

     using (var context = new YourContext()) 
         { 
             var blogs = context.YourTable.SqlQuery("SELECT * FROM dbo.YourTable").ToList(); 
         }

See this link http://msdn.microsoft.com/en-us/data/jj592907.aspx

Substitute the select query with your own query.

edited Nov 26, 2014 at 11:02

answered Nov 26, 2014 at 10:57

StackTrace

9,44636 gold badges122 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jimminybob Over a year ago

I've just tried applying the example to my code and have put in my sql and i'm getting an error when i try to run it saying "CREATE DATABASE permission denied in database 'master'". My query is only selecting records, so not sure why it would need any create permissions, especially on the master database

jimminybob Over a year ago

Got it sorted now, not sure why it was displaying the error it was but there was an issue with my DB connection, which i've now fixed and all is working well. Thanks

Gert Arnold Over a year ago

Using Entity Framework as another tool to execute raw SQL is not something I would call "a push in the right direction". That push should be in the direction of using EF for what it's made for. But the question is far too broad to go into that.

Collectives™ on Stack Overflow

Retrieving count of records from SQL table in MVC

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related