Re: LINQ Queries vs Stored Procs

Tech-Archive recommends: Fix windows errors by optimizing your registry

I think the security issue is the big one. If ALL data access is via Stored Procedures, you can limit the user to only executing stored procedures, without allowing read permissions on any tables. If you have well-defined application roles, you can group users into role-based security groups and assign the minimally-necessary permissions on the stored procedures. Stored procedures eliminate most sql injection attacks, which are more possible with dynamic sql.

I personally prefer putting sql into stored procs instead of the application because I find it a more effective way to organize the code. It separates the database processing details from the application processing. But I think that's just personal preference.

"Jon Skeet [C# MVP]" <skeet@xxxxxxxxx> wrote in message news:MPG.21edd90b3baba01179a@xxxxxxxxxxxxxxxxxxxxxxx
Frank Calahan <ichbineinhund@xxxxxxxxx> wrote:

<snip>

Other than that, why should anyone would stop using SPs and start
using LINQ to SQL queries? Can anyone provide a reference that
refutes the guidance above from the Microsoft Patterns and Practices
Group?

The discussion I've seen isn't nearly as unbalanced as you've made out.
I won't go into all the details, but searching for "Frans Bouma"
"stored procedures" will go a long way. Alternatively, look at the
documentation for ORM projects (including the one Frans develops).
They're obviously bias in the other direction.

I'm very surprised to see the patterns and practices group put out what
looks like FUD. Let's see:

o Execution plan optimization and caching: this is done for prepared
statements too

o Passing less information across the network: yeah, like the size of
your SQL statement (excluding parameters which would have to be passed
in either way) is likely to be in any way significant traffic

o Putting responsibility into the hands of SQL experts: true, although
there's no reason not to get your DBAs to review your LINQ queries too.
In my experience, putting in this extra layer *mostly* results in a
lack of flexibility: if I want to do anything even slightly different
to what's already been done, you have to go to a lot more trouble with
the "always use SPs" mantra.

o Maintenance and security benefits: in certain situations I agree
about security. Personally I'd rather maintain code than stored procs,
although it's often easier to deploy a fixed stored proc once than
update all clients, of course.

o Countermeasure for SQL injection: not an issue for LINQ to SQL (or
any decent ORM or use of parameterized SQL) in the first place


Now, which issue are you actually concerned about? If it's performance,
I suggest you try it against *your* data, as that's what's going to be
really important.

In some cases stored procs certainly are the best way to go - but all
of the processing can be expressed in a single query statement, I
suspect you'll usually find that a tuned stored proc and a tuned LINQ
to SQL query will be very similar in performance. The tuning of both is
important, however. Also make sure when you do performance tests that
you take account of the slight hit incurred the first time a particular
query is executed in LINQ to SQL - don't just do one query and time
that; time lots of them.

--
Jon Skeet - <skeet@xxxxxxxxx>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

.

Relevant Pages

  • UDF in LINQ: "Stored Procedures cannot be used inside queries"
    ... I've a question about calling stored procedures in LINQ queries. ... I have some CLR user-defined functions in a SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: LINQ Queries vs Stored Procs
    ... Stored Procedures, you can limit the user to only executing stored ... most sql injection attacks, which are more possible with dynamic sql. ... Also about the security aspects: it's not as you claim it to be: I can ... I as a user in Marketing have to use your app which uses procs and I ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Critique my LINQ to SQL strategy using stored procs, please
    ... LINQ to SQL seems like a good option for our data access needs, ... All business logic will reside in the stored procs (company ... Using LINQ to SQL, create a single LINQ to SQL dbml ... The power is the use of LINQ to work with the virtual database, which using all stored procedures defeats the purpose of using LINQ-To-SQL. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Help with Stored Procedure
    ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
    (microsoft.public.sqlserver.programming)