LINQ is a real weird bundle of syntax that is being added to C#. The speaker, Anand Iyer, mentioned that it will be implemented in Mono. First it adds a SQL syntax for slicing in memory data. The classic example is around doing sub-selection and ordering of a List. This is nice, but looks funny. Reminds me of Oracle's attempt to get people to write SQL in the middle of JSP pages.
The second part of LINQ is as an ORM for databases and XML. This winds up looking like a bizarre mix of EJB3 and ActiveRecord. Annotations are used to embed mapping (table to class, property to column, etc.) Or you can use "implied" bindings (convention over configuration-ish.) Obviously the latter gives you a lot less code to write. I think you can also use a mix of these things.
It's funny to watch a language evolve. C# started off copying Java, but improving on it. Like Java, it has started to envy some aspects of dynamic languages. Microsoft has been able to move much faster with C# than Sun has with Java. Of course a big part of that is that it's a younger, much less widely used language. The stuff in LINQ really allows for syntax that looks eerily like Ruby or Python with constructs like someObj.find(name=>"Bob") At the same time, you get to keep a lot IntelliSense, which is huge for developer productivity.
If you've ever wondered what Java would look like if you could mix-in Groovy, then take a look at LINQ. It's a mess. It really looks crazy. It even made the speaker crazy. He kept trying to correct indentation and spacing to make it look legible. I guess that's what we all have to look forward to in Java. It also makes me appreciate ActionScript. It's come from the other direction, starting out completely dynamic and becoming strongly typed (which bought them a 10x+ performance boost.) Look at a complex ActionScript 3.0 class and a C# class that uses LINQ. From a pure "grammarian" perspective, you will really appreciate AS3.