Using LINQ For Simple SQL Queries From C#
While it's always a good idea to keep your SQL code separate to your C# code in ASP.NET there are times when you just want to do a quick query on a database without wanting to go about creating Stored Procedures or adding Queries to table adapters.
This happened to me in a current project which has a "admin portal", which has a "dashboard". The dashboard should show the status of the system - counts of new entries, unanswered support calls etc etc.
Assuming you have Table Adapters in place that connect you to the tables needed you can use LINQ to quickly get data from the database, like so:
ClaimsTableAdapter cta = new ClaimsTableAdapter(); //Total liability for approved claims var q1 = (from a in cta.GetData() where a.Approved && !a.Redeemed select a.Points).Sum().ToString(); //Claims logged in the last month var q2 = (from a in cta.GetData() where a.created >= DateTime.Now.AddMonths(-1) select a).Count().ToString();
Looks a bit like SQL doesn't it!? From what I've read on LINQ the idea is to give the power of SQL to developers who might not be up-to-scratch with SQL coding (err, a bit like myself).
Notice how I used the DateTime C# class to adjust a date and didn't need to remember how to do that in SQL code!
Until now I'd overlooked LINQ. Now I've seen how useful and quick it can be I plan on getting to know it in more depth. For this I'll be using LinqPad and have a the O'Reilly Pocket Reference on order.
Skip to the 
You can subscribe to an individual
I don't have any problems with SQL, but I would never use it instead of LINQ. For starters, the code you end up with is much cleaner and easy to use. If that wasn't enough:
* Reading and saving data is much, much simpler.
* Your parameters are automatically validated to avoid code insertion (if you wrote SQL directly, you needed to manually validate parameters or use stored procedures).
* You can add methods and properties to the tables in your model, making your model simpler and easier to use.
* If you use LINQ to Entities, you can change your database system without changing anything in your SQL code.
* Chaining conditions and building queries (using IQueriable) is quite easy.
And I'm sure I've forgotten a few advantages in the previous list.
The only important thing you have to understand when using Linq is *when* the data gets read (for example, when you access a property generated as a relation in the DBML) as otherwise you might be reading a lot of unneeded data from the db into memory.
Reply
"While it's always a good idea to keep your SQL code separate to your C# code in ASP.NET..."
IMHO I do not believe that is what matters. What matters is that you keep data access logic seperated from business logic seperated from views seperated from view logic. In a data access class, it is perfectly fine to use LINQ, raw SQL or whatever you think works best.
Anyways, LINQ indeed is awesome. Just one of word of warning, already mentioned by @S: The way you make your calls on related objects matter a lot. In larger systems you will still need to know all your SQL stuff and indexing stuff.
Reply