Short answer now, I have to get ready for a class, but I'll give you a longer one this evening.
First, thanks for mentioning skip and take - I kinda forgot that I (and hundreds of other people) wrote about that (and other LINQ items) two+ years ago in CoDe and had user groups calling me asking me to talk about LINQ for them. <s>
As for the SQL equivalent, yes, I wrote about that over three years ago (as did many others), using ROW_NUMBER() OVER, with a case statement for the current order, and then another case statement for the generated row number being between a start index and an end index.
I didn't say L2S didn't have "value" - I questioned whether L2S provided more value than what was already there.
DBAs are far more likely to want the stored proc approach, because of the benefits of decoupling the T-SQL code from the application. I work for a company that (among other things) partners with many firms in job placement. So I routinely get to find out what I.T. shops and I.T. leads are looking for (and aren't as interested in looking for).
OK, some issues....
Let's start with 3 issues....first, ever see the classic demonstration of SQL isolation levels with two query windows for User A and User B? Try to go through that exercise with LINQ and two instances of .NET programs. See what happens.
Second, you can't subclass any of the table items from a context. Some may view this as not important, but it demonstrates what happens when you start pushing L2S.
Third...a question - suppose I like some of the new T-SQL 2008 features from L2S - can I do it?
More later...