Niels confirmed it for me. Yes, it is possible to run a LINQ to SQL query in SQL Server 2008 using a .NET managed stored procedure.

As he puts it: "yes you can - but as soon there are any transactions invloved things will go pear-shaped".

Note that there will also be an optimized LINQ to SQL provider as part of SQL Server 2008:

"Language Integrated Query (LINQ) enables developers to issue queries against data by using a managed programming language such as C# or Visual Basic.NET, instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server 2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns."