Update (2010-12-30): I have updated the OData URI locations below, because of a recent move and re-architecture by the StackExchange team.
In case you haven’t read, Stack Overflow and the rest of the Stack Exchange sites are now able to be queried using OData. This is great because as Jeff points out in the blog post:
…if you just want to play with the data, it’s kind of tedious: you have to download the entire 700 plus megabyte archive, import it into some kind of database system — and only then can you even begin thinking about how to query out the results you’re looking for.
This hurdle has stopped me from performing some basic queries that I would like to know on the spot. But that is no longer the case, and I am going to show you how you can get started querying Stack Overflow from a tool called LINQPad.
First Things, First
You need to download LINQPad, which is a general learning and development tool for composing and executing LINQ queries. After you have downloaded and installed the application you will have a screen that looks like this:
Setting Up Stack Overflow OData
The next step is to add the OData endpoint for Stack Overflow, which is:
http://data.stackexchange.com/stackoverflow/atom
This is done by clicking on Add connection in the top left of LINQPad. Which will give you a screen that looks like this:
Select WCF Data Services and click Next. You will get a connection screen where you enter the endpoint for Stack Overflow that I described above:
After entered click OK, and you will be taken back to the LINQPad application with a new connection to the Stack Overflow data source that lists all the available tables and fields that you can query.
Lets Have Some Fun Querying
Lets start with an easy query that we all should know. Who is the number 1 Stack Overflow user?
What about which user has the highest number of votes?
What about down votes?
Show my insightful comments with a score of 5 or greater?
Conclusion
As you can see this is a very powerful way to get some quick answers out of Stack Overflow, and it is amazing that Microsoft, Azure, and Stack Overflow is able to bring all of this to us for free.
Also if interested they also offer OData endpoints for all of the other major Stack Exchange network of sites:
- Stack Overflow: http://data.stackexchange.com/stackoverflow/atom
- Server Fault: http://data.stackexchange.com/serverfault/atom
- Super User: http://data.stackexchange.com/stackoverflow/atom
- Meta Stack Overflow: http://data.stackexchange.com/meta/atom
- Ubuntu: http://data.stackexchange.com/ubuntu/atom
- Gaming: http://data.stackexchange.com/gaming/atom
- etc: http://data.stackexchange.com/<community>/atom
Post some of your interesting queries below.
LINQ, LINQPad, OData, Stackoverflow; Stack Exchange
Very cool. Thanks for the quick sample!
Patrick
Superb! I know nothing about OData but this will prompt me to look into it.
When I try to use the link, it gives me the error message like this:
Request Error
The server encountered an error processing the request. See server logs for more details.
I guess thay changed the url?
Hi Kenny, I have updated the URI to reflect the new addresses.
Seems very interesting as I am a big fan of StackOverflow so I thought this would be cool to see in action. However, after I created my oData connection i got this error:
Error: Schema version requires .NET framework 4.0 support. Download LINQPad 4.0 or later
As I am still on that old antiquated 3.5 framework, it would appear that I can’t checkout the awesomeness that is StackOverflow querying just yet.
Perhaps you could make a quick edit to throw in a quick blurb at the top of this article that mentions that it requires the 4.0 framework.
this is amazing!
the tool is very easy to use and seems to connect in that you can see the data structure. However when you run the query, you get error 53 – network related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server).
Would love to hear the fix on this…
Thanks!
Tracy
@Tracy – make sure you’ve selected the StackOverflow connection in the @Database’ box at the top right ofd the query window
i want select all posts of specefic User and i used this code :
from c in Posts
join p in Users on c.OwnerUserId equals p.Id
select c.Title +” “+ p.DisplayName
why why above code not working :
The method ‘Join’ is not supported.