For simple queries, it may be overkill, but for complex queries, it's a godsend. Take grouping, joining, and sorting for example.
var q =
from item in collection
group item by item.Property into prop
select prop;
var q =
from item in collection
join other in otherCollection on item.Property equals other.Property
select new { item, other };
var q =
from item in collection
orderby item.Property, item.Name
select item;
The beauty of LINQ is that you can compose all these operations, plus projection and filtering, into simple queries, rather than blocks upon blocks of increasingly inscrutable code.
Also, LINQ is built so that it doesn't have to filter the query in your C# code but can formulate queries in SQL or another entirely different query language (e.g., CAML for LINQ to SharePoint queries) if an appropriate IQueryProvider implementation is available. LINQ to SQL queries are processed in SQL Server rather than on your box, potentially resulting in more efficient data processing.
Lest I appear overly enthusiastic, a couple warnings: be careful not to go overboard, just like with SQL, and using LINQ doesn't excuse you from becoming familiar, if not expert, with the target query language. Query optimization is still an exercise left to the reader.