why i use Linq with Store procedure ?

Question

0.00/5 (No votes)

See more:

I use Linq in my current project, which work fine,

But now problem is my Team Leader said to me that,
why i not use store procedure in my project,and they want to change whole project linq queries into Store produre,

I study So many article for this(Linq with Store procedure),

But i notice that,when i use Store produre, All queries is written in Ado.net,
and linq is just use to call that store produre,

so my question is where i use Linq queries,

i think After use of Store produre whole project is convert in Ado.net Project,

so plz advice me what can i do,And tell me some Advantage of using Linq with Store produr

Posted 22-May-13 19:30pm

Arun kumar Gauttam

Updated 22-May-13 20:15pm

v2

Add a Solution

3 solutions

Solution 1

Linq is created on the top of ADO.NET.One of the main Advantage of Linq is you can write Type Safe Queries that is more error free than ADO.NET. There are some performance issue by using Linq. If you are working on a complex query, my suggestion is going with ADO.NET.

Hope this helps

Posted 22-May-13 19:39pm

Jameel VM

Comments

Arun kumar Gauttam 23-May-13 1:55am

i agree, but my question is if i already use Linq then why i use Linq with Store produre,
and if i use Store produre then why i use Linq,

I think Linq and Store Produre, Both are Different techniques,
Then Why some Devlopers use Both at same project

Jameel VM 23-May-13 2:05am

You can use Linq not only for Querying but over a collection and xml linq is a good choice..

Jameel VM 23-May-13 2:09am

One of an ORMs biggest advantages over ADO.NET is in initial development speed (not application speed). Maintenance can also be much easier with an ORM than with ADO.NET

Solution 2

Code should only access the database through stored procedures and should not have direct access to the tables. This way, we can enforce abstraction and the SPs become the interface to the database. This treats the database as a separate entity so writing SPs are always considered as an ideal way of accessing the databases. So the project might have been dealt with linq or ADO, store procedure should be the bridge between the Application and Database. Do write SPs every time you undertake any project.

Posted 22-May-13 20:48pm

winners6

Comments

Rob Philpott 23-May-13 3:21am

Dude, you've pasted stuff from my answer to the other question here, but what I wrote was far more balanced. The advice 'Do write SPs every time you undertake any project' is absurd.

More importantly, if this work has already been done and is working ok, why on earth would you change it? You introduce more work and new risk doing this and that will not be to the benefit of the project.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2013-05-22T21:16:00

Solution 3

Your team leader promotes a quite outdated approach. Not because it is not a good one, but because it is not the best in conjunction with dot.net.
SPs are part of the DAL, but they represent business logic as well, so if you want to do a good separation of concerns, using SPs will break this. LINQ, more precisely Entity Framework and Linq to EF is meant to give you the proper tools to build BL in managed code by using your business model as managed types. SPs are good if you have some special tasks where you can't use managed code. Ah, and you have CLR integrated SPs too, so the range of tools you can choose from is quite large. Using SPs just to hide tables is of no real use, if you manage access in the proper manner.
Even Microsoft is promoting EF above all other tools.
The interesting part is placing the tires. If you can install the BL tire built on EF near the data store (from network throughput point of view, ex: on the same server) you can even get better performance out of your application than with SPs. But even if you don't, you still get a maintainable code. That is important in life-cycle management.

Posted 22-May-13 21:16pm

Zoltán Zörgő

Comments

Rob Philpott 23-May-13 3:26am

Fair points - have you ever tried a CLR integrated stored proc? They really are most unpleasant (unless things have improved).

Also, how will you get better performance with EF than SPs? And I've yet to see a system where a database server also runs code (enterprise wise).

Zoltán Zörgő 23-May-13 3:44am

Yes, I have used CLR itegrated SPs. Of course, there are some inconveniences - but if you need the whole .net infrastructure behind you, you learn to live with them.

SPs are not compiled, they are partly precompiled, and there is some caching too; so if you run that specific SP rarely, it will take time to build, while your BL library is compiled managed code. The more complicated code you put in your SP, the more time it takes, and less optimal it will be.

DB server not running code? What is SP than? It is code, believe me. Is your SQL server not code? It is code. But this is only one interpretation. SQL Servel - Application server - Web server: just an (old) interpretation of the infrastructure and separation of concerns. If you think in application related infrastructure you have to take care of load, performance, scalability and so on, and you will end up in rethinking that kind of separation and getting an other. But lets's make the portion you put near the the SQL server thinner: don't put the BL there, only the DAL layers. So you can say: hey, my dbms server is dealing with data only, and not with business logic. Cool. You still have a quick managed layer you can use, and nobody will complain that you put application logic on that server.

How would you position a self-hosted WCF service in the old infrastructure view? Or a self hosted web-api? Or a NoSQL database service with server side map-reduce? All these are technologies that need new view.

Rob Philpott 23-May-13 3:51am

I don't think it matters than your BL is compiled. You still end up with an expression tree which needs converting to SQL, and the same caching etc. applies.

Zoltán Zörgő 23-May-13 4:03am

Not true. If you use SQL just to access data, your sql expression threes will be much simpler. But you are still missing my point. As I see I won't convince you. Time will :)

Rob Philpott 23-May-13 5:42am

Death first!

Zoltán Zörgő 23-May-13 5:45am

:)

Arun kumar Gauttam 23-May-13 4:47am

tell me just one thing,that i use linq with Sp ,or Not use LInq with Sp

Zoltán Zörgő 23-May-13 4:51am

You can, but you should not. But if you are told to do so, it will be hard no to comply.

Arun kumar Gauttam 23-May-13 5:29am

thanks for suggestion,