Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LINQ to SQL and Entity Framework - What do DBAs think of these options? Expand / Collapse
Author
Message
Posted Friday, September 17, 2010 5:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 169, Visits: 894
I am a .NET developer working with a small team of developers who have to also double as our own DBDs. Recently, the subjects of LINQ to SQL and Entity Framework have been raised by one of our developers. I realize this is a SQL Server forum and developers would be using these via .NET, but I want to know how DBAs feel about these technologies hitting their databases. I've very quickly skimmed:

LINQ - http://msdn.microsoft.com/en-us/library/bb308959.aspx
Entity Framework: - http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx

At first glance, this goes against everything I was taught. I was taught "NO INLINE SQL!" yet that's what I'm seeing here. And I even saw a foreach that looks like it is possibly masquerading as a cursor (another big no-no). I rarely have a call to the DB that is a simple "select this from that where this". Most DBAs do not want developers hitting the database like this. Am I completely missing the boat here? Is this a huge paradigm shift that we need to embrace? Does using these technologies mean DBAs roles will change to be more operational DBAs? I can't see any of you wanting that to happen, right?

I'm just looking for top of the trees feedback on these concepts. I'm not ready to drink the Kool Aid yet.

Thank you for any input -
Lisa
Post #988637
Posted Wednesday, September 22, 2010 5:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
LSAdvantage (9/17/2010)
I am a .NET developer working with a small team of developers who have to also double as our own DBDs. Recently, the subjects of LINQ to SQL and Entity Framework have been raised by one of our developers. I realize this is a SQL Server forum and developers would be using these via .NET, but I want to know how DBAs feel about these technologies hitting their databases. I've very quickly skimmed:

LINQ - http://msdn.microsoft.com/en-us/library/bb308959.aspx
Entity Framework: - http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx

At first glance, this goes against everything I was taught. I was taught "NO INLINE SQL!" yet that's what I'm seeing here. And I even saw a foreach that looks like it is possibly masquerading as a cursor (another big no-no). I rarely have a call to the DB that is a simple "select this from that where this". Most DBAs do not want developers hitting the database like this. Am I completely missing the boat here? Is this a huge paradigm shift that we need to embrace? Does using these technologies mean DBAs roles will change to be more operational DBAs? I can't see any of you wanting that to happen, right?

I'm just looking for top of the trees feedback on these concepts. I'm not ready to drink the Kool Aid yet.


I started working with databases before the invention of relational databases, even then programmers managed to mask row-by-row loops on the application side - some stuff is not meant to change as I can see.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #991651
Posted Wednesday, September 29, 2010 10:08 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:16 PM
Points: 621, Visits: 377
I am a .NET developer with a lot of SQL Server 6.5 and onward experience. I have never been called a DBA but I have functioned in that role over the years.

If you search this site for "Entity Framework" you will find that most DBAs are not very excited about EF or ORM in general. Like most things, you can't stick your head in the sand and just be a coder and not worry about what kind of T-SQL the ORM is going to generate. You do need an appreciation for the structure of the database and the performance implications. However, we don't all work in places with guru MSSQL DBAs who know how to wire awesome stored procedures. Sometimes the DBAs are so busy they can barely keep up with the system administration stuff. Sometimes you have server freezes where you can only ALTER a stored procedure 5 times a year. Separating data access from business rules and user interface is a best practice and a new tool in your toolbox won't automatically prevent you from making bad decisions. So embedding the query, be it LINQ or T-SQL or whatever, inside your event handler for a button click is always possible.

ORM seems very promising because it should make code far more reusable and extensible. ORM + a good architecture for a piece of software + profiling & testing to see that the ORM is not doing something stupid on the SQL Server. The safest approach is probably to wait and see what happens with all this. And if you do try to use Entity Framework, do it on a small project, preferably something brand new.

LINQ to SQL is losing favor even among those who "are drunk on Microsoft Kool-Aid" because it doesn't support other back-end databases besides MSSQL... and other reasons you can easily find by Googling. For the moment, the big push is for EF and Microsoft really wants developers to start adopting it.
Post #995405
Posted Wednesday, September 29, 2010 5:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 169, Visits: 894
Thank you very much for the reply Andrew. Turns out we had a consult session with Microsoft last week here are some items from my notes:

- EF may not be best for web applications. It can run out of memory doing something big. You don't want to load a lot of data into EF. It's not the best fit for short, "bursty" appications. It is also not best if you have to constantly go to the db for information.

- As far as LINQ goes, they mentioned that if you had contractors, this would keep them away from proprietary data. They indicated customers would use LINQ to talk to Entity Framework. You would also use LINQ if you don't want to go back to the database.

- Windows Communication Foundation was talked about as the preferable approach for our team (I know nothing about this and will be attending a class in November).

- They also talked about Velocity. It's primarily used at the UI level to store large amounts of data efficiently. Velocity is a private cloud and full API that's focused on in memory data.

All these technologies are completely new for me so I can't really speak to anything in more detail. This is just the best I could regurgitate from my notes! Our Dev Manager is recommending a WCF approach and I highly suspect LINQ and EF will not be coming into play with our projects at this point.

Thank you again!
Post #995666
Posted Friday, October 29, 2010 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 1, 2010 10:04 AM
Points: 2, Visits: 12
Like a lot of things with Microsoft "advice", it tends to be contradictory in nature. For example, in just about all of the "n-Tier" articles and samples the main theme is to use StoredProcedures. Yet what do we find in the literature?

....A bunch of T-SQL in the application! lol.
Post #1012916
Posted Friday, October 29, 2010 6:40 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:16 PM
Points: 621, Visits: 377
I get the feeling that Microsoft is large and sprawling enough that it's hard to know what different groups are all up to. I ignored LINQ for the first several years because I am very comfortable with T-SQL and I didn't really need it, I thought. I use it now because it's getting harder to avoid with the frameworks MS is now giving us, especially WCF and that kind of stuff.

I now work somewhere where changing the schema is something that happens quarterly-ish, so sprocs are right out for most things. But I wonder how much unneeded data goes over the wire just that we can append that .Where with LINQ on the middle tier.
Post #1012970
Posted Friday, October 7, 2011 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 7, 2011 9:35 AM
Points: 1, Visits: 3
Some inventions have no real use in life.

Regards,
Jason
http://usa.redirectme.net
Post #1187227
Posted Friday, October 7, 2011 9:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 11,322, Visits: 13,115
In my opinion EF is better than Linq to SQL. Like anything it can be used well or poorly. I prefer to use stored procedures because I believe they make maintaining and tuning the T-SQL much easier. You can use SP's with either EF or Linq to SQL, so you can do it. I think Florian Reischl has a good series on his blog about business layers, data layers, and database access, starting here, http://florianreischl.blogspot.com/2011/09/tables-are-no-domain-objects-part-1.html

SQL Server MVP Mladen Pradjic also has a good post about this here, http://tinyurl.com/DecouplingPrajdic




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1187451
Posted Wednesday, December 19, 2012 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 6:59 AM
Points: 149, Visits: 51
As an experienced app developer and DBA, I have the following thoughts:
Selecting data within Entity framework using Stored Procs is somewhat of a nightmare for devs. Selecting data through the entity model often generates huge SQL statements which are often made of case statements to determine which part is actually executed on the SQL Server or use a massive amount of unnecessary sub queries. These generate many execution plans on the SQL server, rarely reusing a current execution plan. This is a nasty performance hit on the SQL box. It takes away scope from the DBA to rejiggle SQL code to provide a better execution plan. It leaves the DBA only with option of looking at cached plans on the server and generating any necessary indexes which is only "half a job". It also bloats SQL log files from the somewhat crazy SQL statments that Entity Framework generates to execute against the SQL Box. The product is of complete dismay to me. If you want to have a performant SQL Server and work in an enterprise environment my advise (and I talk from experience) is to stay away from Entity framework!
Post #1398506
Posted Wednesday, December 19, 2012 9:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
In my experience, both of these are usually substitutes for skill in the subject.

That can be a good thing, if the company you work for can't hire expertise (not available, not affordable, whatever), and needs something that will probably at least work, even if not well.

However, it can leave you in a situation where, when you finally can/must obtain expertise (either through hiring an expert or through training existing employees), that the expertise is pointless because of the limitations that are now imposed on things like performance tuning. This is true of any data solution that doesn't include all data access through a DAL the tuning expert (usually a DBA) can code in, whether that DAL is procs or something higher in the stack than those.

LINQ to stored procedures, with no direct table/view/UDF access, on the other hand, I've had more positive experience with. But it seems to be a rare solution. It does require having proc-writing expertise available through one means or another, so may not be available to everyone.

Since database tuning expertise is usually a DBA skillset, and since DBAs tend usually towards writing SQL in stored procedures, which can act as an API to the data (a Data Access Layer), I've found that to be a "best option" in cases where it can be done.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1398516
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse