At last, I found some spare time to start playing with that Linq2SQL thing. Installed VS2008 RTM. fired it up, created a new C# console project, added a reference to System.Data.Linq, created the appropriate .dbml to Northwind database, and typed in the following code, more or less copied from the ScottGu blog (http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx):

            northwindDataContext db = new northwindDataContext();

            Product product = db.Products.Single(p => p.ProductName == "Flotemysost");
            product.UnitPrice = 99;
            product.UnitsInStock = 5;

            db.SubmitChanges();

Fired up the SQL Server profiler to capture what's really happening, and pressed F5.

And here's what was in the profiler trace:

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductName] = @p0',N'@p0 nvarchar(11)',@p0=N'Flotemysost'

exec sp_executesql N'UPDATE [dbo].[Products]
SET [UnitPrice] = @p9, [UnitsInStock] = @p10
WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([SupplierID] = @p2) AND ([CategoryID] = @p3) AND ([QuantityPerUnit] = @p4) AND ([UnitPrice] = @p5) AND ([UnitsInStock] = @p6) AND ([UnitsOnOrder] = @p7) AND ([ReorderLevel] = @p8) AND (NOT ([Discontinued] = 1))',
N'@p0 int,@p1 nvarchar(11),@p2 int,@p3 int,@p4 nvarchar(16),@p5 money,@p6 smallint,@p7 smallint,@p8 smallint,@p9 money,@p10 smallint',
@p0=71,@p1=N'Flotemysost',@p2=15,@p3=4,@p4=N'10 - 500 g pkgs.',@p5=$21.5000,@p6=26,@p7=0,@p8=0,@p9=$99.0000,@p10=5

What can we say about the SELECT statement? It's simple, but there are at least two things wrong. First, notice that the SELECT list incudes *all* columns from the Products table, although we had no intention of using them all. I agree, Linq has no way of guessing what we're going to need and what not, but anyway it's way too easy to type in an equivalent of "select *" without being aware of it. Second, the @p0 parameter is nvarchar(11). It's just plain stupid, IMHO. Remember, we have the data model in the project, and the Linq engine knows that the ProductName column's datatype is nvarchar(40). Nevertheless, it decided to simply count the characters from the "Flotemysost" const. Why, oh why? OK, so we will get the different query plan depending on the value passed to the query. Not exactly good for the plan reuse, right?

And what to say about the "optimistic" UPDATE statement? Well, it's just plain wrong.. Please -- never, never, never update the data without using a stored procedure. Please :)

TBC.

Dean