Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > LINQ
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
LINQ Discuss Microsoft's LINQ (Language INtegrated Query) for .NET 3.0 and later.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the LINQ section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 28th, 2008, 08:59 PM
Authorized User
 
Join Date: Dec 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default LINQ: use stored proc or Ad-hoc?

I'm new to LINQ and been reading about LINQ.

Any pros/cons to using stored procs instead of ad-hoc LINQ sql?

I know that stored procs are easier to change. As far as SQL Injection, i think both stored procs and Ad-hoc LINQ are safe.

My thought is: LINQ stored proc is not that hard to use. Why would I want to learn LINQ's ad-hoc SQL language?

I found this link as well but i think the author is biased and doesnt like stored procs.
http://weblogs.asp.net/shijuvarghese...-sql-quot.aspx

Any thoughts?
  #2 (permalink)  
Old December 29th, 2008, 08:57 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Oh boy, this is a doozy of a question. ^^ Ultimately the answer to this question it comes down to personal preference, design decisions, system architecture, and so forth.

The author of the article you provided is biased in the regard that they dislike stored procedures but they make some valid points such as what you can and can not do with PL/SQL or TSQL (although this is somewhat of an easy way out as I have seen some pretty elaborate pieces of DB code) and so forth. You should bare in mind that a good C# developer is not necessarily a good DB Programmer ;]

IMHO, there are arguments to be made for both sprocs and ad-hoc queries, to much for a simple forum post so I will keep it brief:

On one hand when someone starts talking about LINQ to SQL and ad-hoc queires I quickly slip back into my days of Classic ASP and building Dynamic SQL statements; these thoughts are enough to drive me crazy let alone the thought of actually implementing the same techniques again! On the other side of the coin though stored procedures falter when the query you need to excecute is truly dynamic and is not decided until runtime. For example, assume that you have a grid of data that you allow your users to filter based on column name. At runtime you might simply execute a basic select statement that returns all the rows and columns of data with some default sorting critera. Now the user may click on n number of columns on wish they wish to sort!

You could, of course, create a stored procedure for every single possiblity and then build a switch statement to execute the stored procedure or you could build a sproc like this:

sql Code:
CREATE PROCEDRUE foo
(
    @ColumnName varchar(50) = 'Column1'
    @SortDirection varchar(50) = 'ASC'
)
AS
DECLARE @sql varchar(max)
SET @sql = 'SELECT column1, column2, column3 FROM table Order By ' +
                 @ColumnName + ' ' +@SortDirection

EXECUTE @sql

Assume that you simply ran this sproc from SSMS and the SQL String that would get executed would be:

sql Code:
SELECT column1, column2, column3 FROM TABLE ORDER BY Column1 ASC

This can quickly become cumbersome and is an area where Ad-Hoc queries really come in handy.

As I said, I could probably roll for another page or two on the pros and cons of each but it is something that you should make up your mind for youself and what makes the most sense for your situation.

hth
-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
  #3 (permalink)  
Old December 29th, 2008, 09:05 PM
Authorized User
 
Join Date: Dec 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Doug...

You know what, I just read that Microsoft will put more effort into Entity Framework rather than LINQ-To-SQL. Not good. Looking into Entity Framework.
  #4 (permalink)  
Old December 29th, 2008, 09:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

One thing a person *could* factor in is the ability, in SQL Server 2005 and 2008, to write SPROCs in C# or VB.NET.

Now, all of a sudden, it *can* make sense to send a delimited list to your SP and let the C# code pick it apart and do what needs to be done.

Of course, the strongest argument for using SP's is the obvious one: You happen to work in a place where they don't allow any other kind of access to the DB. <grin/>
  #5 (permalink)  
Old December 29th, 2008, 09:19 PM
Authorized User
 
Join Date: Dec 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

what do you guys think of Entity Framework?
  #6 (permalink)  
Old December 29th, 2008, 09:50 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

There is a discussion to be had as to why or why you wouldn't want to be writing managed code in a Sproc but that is for another time.

There are Zealots on both sides for and against EF but, again, it is a matter of preference. L2S worked very well on paper (heck I can divide by zero on paper!) but, in my opinion, there were far to many nuances associated with L2S. For example: if you made a change to the physical data store you had to go back and manually refresh the dbml file and so on and so on.

The EF is currently something that I am playing around with and so have not made a decision one way or another but it looks promising. Whether or not I abandon using the Enterprise Library in favor of the EF remains to be seen but I have had an overal positive experience with what I have seen thus far.

-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
  #7 (permalink)  
Old December 29th, 2008, 10:02 PM
Authorized User
 
Join Date: Dec 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

we have the Enterprise Library. EL uses ADO.Net (dataset, datareader) etc.

Reason we're thinking about using EF or L2S is performance. We looked into typed data sets and compared to L2S, L2S was much better.

I will start reading about EF. My main concern is lack of info out there, especially about using it with stored procs. No books until Feb 2009 sometime.
  #8 (permalink)  
Old December 29th, 2008, 10:38 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

So explain something to me: MS has continually maintained (and almost surely correctly so) that the highest possible performance in ADO.NET is obtained using a DataReader.

So then isn't it true that the more "layers" you put between your code and a DataReader the worse the performance is going to get???

NOTE: I know absolutely nothing about Entity Framework, but just the word "Framework" makes me think of all the bloated frameworks I've worked with in the past. Oh, they were really nice for rapid code development, but they all add layers upon layers to the basic DB performance. So I can't help but wonder if maybe, if performance is your main goal, whether you should chuck all this stuff and go to hand-written code that utilizes DataReaders almost exclusively??
  #9 (permalink)  
Old December 29th, 2008, 10:43 PM
Authorized User
 
Join Date: Dec 2008
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Someone shoot me please

I know. We got to where we are because someone on the team decided to disagree with the rest of us. I agree with the datareader.
  #10 (permalink)  
Old December 29th, 2008, 11:05 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

EF is now part of the ADO.NET stack and, while you present a good point, I have not noticed any performance degradation. I guess the best way to think of EF's conceptual data model would be to look at a relational data model (with some degree of normalization) and create views of this data. These "views" are what you would query against in your code via Entity SQL (similar to T-SQL but uses lambda expressions and the like) like so (the following code is from some MSDN documentation):

csharp Code:
using(MapConnection con = new
                      MapConnection(Settings.Default.AdventureWorks)) {
    con.Open();

    MapCommand cmd = con.CreateCommand();
    cmd.CommandText =
        "SELECT sp.FirstName, sp.LastName, sp.HireDate " +
        "FROM AdventureWorks.AdventureWorksDB.SalesPeople AS sp " +
        "WHERE sp.HireDate > @date";
    cmd.Parameters.AddWithValue("date", hireDate);

    DbDataReader r = cmd.ExecuteReader();
    while(r.Read()) {
        Console.WriteLine("{0}\t{1}", r["FirstName"], r["LastName"]);
    }
}

As you can see this isn't much different from what you would do if you were executing this statement directly against SQL Server. The one thing to point out here is that SQL Server is incapable of translating, thus executing, Entity SQL and so there is a middle layer between the conceptual data model and the logical one that provides a mapping and translating mechanisim so that the data is propery reflected from the data store.

So, by your statement Pedant, it is logical to assume that if there was going to be some soft of bottle neck with EF it would most certainly occur in this middle layer between execution of the Entity SQL against the Conceptual model and the Translation and Execution against the logical data store. I am by no means an expert on the EF but I will say that for all of the nuances of L2S Microsoft did a particularly good job of translating ad-hoc LINQ queries into effecient T-SQL (though there were a few articles that I read where the T-SQL was deplorable) so I would hope that they expressed the same level of meticulousness with the EF.


-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 2 AD HOC Testdriven.net tests? phsarjk BOOK: Professional XNA Game Programming: For Xbox 360 and Windows ISBN: 978-0-470-12677-6 1 May 17th, 2007 04:12 PM
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
Ad Hoc Query Classes Bob Bedell Pro VB.NET 2002/2003 0 September 21st, 2003 01:32 AM



All times are GMT -4. The time now is 10:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.