LINQ to SQL Gotchas - Using Stored procedures
I'm currently on the "I like LINQ to SQL" side of the
fence. I think it is very much a marmite question, you love it our you hate
it. But importantly you often hate it even if you haven't tried it.
I've been digging around with LINQ recently and whilst it is very nice and
easy to do things. This, as with many situations, can cause problems if you
don't know what is being done under the hood.
Mike Taulty is font of knowledge when it comes to LINQ and Entity Framework
and he recently posted about the use of stored procedures in LINQ. (LINQ
to SQL, Stored Procs, Output Params, Readers Reading)
One thing you find out is that LINQ may not execute a query on the line of
code you think it will, i.e.
var query = ctx.GetCustomersByCountry("UK");
Will not execute the procedure associated with the GetCustomersByCountry
method. It will only get executed when you do something with query.
Whats more when the procedure returns a resultset and you then iterate over
that resultset the results are read from the server one at a time. This means
that if you do complex processing in your loop you can tie up resources and a
connection to your database.
For more details look at Mike's blog post (LINQ
to SQL, Stored Procs, Output Params, Readers Reading)
Whilst this isn't as bad as serverside cursors in the old ADO days it is
still a gotcha you have to be careful of.
-