Pros:





Declarative approach makes queries easier to understand and more compact


Extensibility and expression trees allow mostly consistent querying of multiple sources


Even in-process queries can be implemented in ways other than LINQ to Objects - e.g. Parallel LINQ and my own Push LINQ framework. Very flexible.


Fabulously useful for in-process queries, where it's easiest to understand


Great to be able to avoid SQL in strings etc


Wide range of operators provided by default, and others can easily be added for LINQ to Objects


Language features introduced primarily for LINQ are widely applicable elsewhere (yay for lambdas)




Cons:





Query expressions aren't understood well enough, and are overused. Often simple method invocation is shorter and simpler.


Inevitable inconsistencies between provider - impedance mismatch is still present, which is reasonable but needs to be understood


There will always be some things you can do in SQL but not in LINQ


Without understanding what's going on, it's easy to write very inefficient code


It's hard to write a LINQ provider. This may well be inevitable, but more guidance from Microsoft would be appreciated.


It's a new way of thinking about data access for most developers, and will need time for understanding to percolate


Not specifically LINQ but related to it - the way extension methods are discovered in C# isn't granular enough


Some operators are "missing", particularly the equivalents of OrderBy for things other than ordering - e.g. finding the item with the maximum value of a property


Deferred execution and streaming are poorly understood (but improving)


Debugging can be very tricky due to deferred execution and streaming




I find it's best when dealing with in-process queries. They're easy to predict, understand and extend. Complementary technologies like LINQ to XML and Parallel LINQ are great. LINQ to Objects can be used almost anywhere.



LINQ to SQL etc are really nice where they're appropriate, but they're harder to understand and need more expertise. They're also only applicable in certain areas of your code.