LINQ to SQL in a Nutshell
Why do I know LINQ, especially for SQL? LINQ is a single-query language written in C# or VB that's syntactically like SQL, but LINQ isn't just for SQL. LINQ is a query language that can be used for objects, SQL, XML, and more. The key here is that if you learn LINQ, theoretically you can use it to query anything. This is a good trick, considering the prior technology required to write custom code for objects, use XPath for XML, and SQL for SQL Server. You still can learn all of those technologies, or you could just learn LINQ.
In a nutshell, LINQ is associated with an IQueryProvider. The idea behind an IQueryProvider is that LINQ queries go in at one end and the query language of the provider comes out at the other. That is, you write LINQ for SQL, and SQL is generated to interact with the database; or you write LINQ, and Active Directory's query language or XPath statements come out the other end.
Currently, LINQ supports XML, objects, and SQL Server (both LINQ to DataSets and LINQ to SQL). LINQ to XML for Objects (LINQ to XSD) and LINQ to Entities (ADO.NET 3.0) are in the works.
LINQ to SQL works by creating classes that map to tables and using a DataContext. The DataContext represents the connection to the database, and the mapped classes are entity classes that map to queryable things like tables, views, and stored procedures.