LINQ-to-SQL CompiledQuery.Compile() with Update, Delete, Insert?

Question

All,

So I've got all my select queries in LINQ-to-SQL converted to using CompiledQueries to speed things up. Works great so far for select statements, but I haven't been able to figure out how to pre-compile insert, update, or delete statements.

Granted, when you're inserting, deleting or updating in LINQ-to-SQL, you have to use the object model. But obviously somewhere along the way it generates a query, which would be nice to pre-compile and store in a static member.

Is this possible? What is LINQ performance like for updates, deletes and inserts when its not pre-compiled? I could see it being a lot faster than the selects, because what they do underneath is a lot simpler and less "dynamic"...

Answer 1

There is a big difference. Linq-To-SQL select queries can be large complex expressions trees. Its these that may take sometime 'compiling'. In this case coalescing to some T-SQL that can be run against a SQL Server. So it makes sense to cache the result of an operation so that it can be re-used.

However other Delete, Update, and Insert are simple operations that do not require an expression tree to be converted to T-SQL (LINQ itself is all about querying). Its just unfortunate that we've been trained to think of SQL code that performs these other operations as 'queries', we're not actuall asking for any info.

These operations are only defined by the DataContext not by LINQ hence the code to perform these functions is already compiled.

Answer 2

I think of the three only insert would make sense to be able to compile and re-use because delete is trivially simple (DELETE FROM Table WHERE Key...) and UPDATE only updates the fields that have changed and so varies per update operation.

[)amien

Answer 3

L2S uses "sp_executeSQL" so after you run it the first time it will be in the stored procedure execution plan cache. Subsequent runs (of the same query - not same params) will reuse the compiled plan from the cache. So what you are asking for is automagically handled by SQL Server 'behind the scenes'.