Entity Framework: To use or not to use

I'm starting a new ASP.NET project, and am looking into using the Entity Framework. It seems that MS is really pushing things in this direction, but coming from a SQL background, I have a bias towards the "Code - First" approach. I want the best of both worlds -- I want to have full control over my database schema, but also want the automation of the Entity Framework.

There's a way to design my SQL database myself, and then have EF take over all the CRUD operations. For anyone who tried this approach -- was it easy to maintain?

Also, wondering what all the database experts on Experts Exchange think about Entity Framework.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
of course you can design your own database and then use EF for the CRUD and in most cases EF is sufficient (it sure reduces the amount of code you have to maintain) It may not be optimized for your particular situation but good enough
Jacques Bourgeois (James Burger)PresidentCommented:
Sorry for the length of that post, seeing it after publishing it, I might have gone a little overboard on a day where I had nothing else to do. But there it is.

Easy to maintain? What a good question. You have some programming experience under your belt, am I right?

I am an old hand who still works from time to time on an application that was created in the 80's on VIP database (nobody knows about that one... tell me on what computer it ran and you win a prize), then moved to DBase (a little more mainstream, but still unknown by many modern programmers), with a short stint in Alpha Four (I vote for you as president if you ever worked with Alpha Four), then to Microsoft Access, and now on SQL Server, eventually on a BioDatabase (aren't we going neural or something?).

This is an application that I saw go from Basica and a few iterations of BASIC under 3 operating systems, before going to Windows through 5 versions of VB (sorry, I missed VB1), being completely rewritten for 8 versions of VB.NET. I am 200 years old by the way. Also by the way, that application is as modern as any application can be... as long as you stick with Windows Forms applications, which many do not see as modern anymore. All of this gives me the right to say that I know about maintenance. And that I have been following Microsoft since the beginning, which will add to my argumentation.

And when someone asks "was it easy to maintain?", I jump forward thinking that this SOMEONE is probably not 200 years old as I am, but surely needs to get an answer from an old Sage (that is what they say on a few T-Shirts I got from Expert-Exchange... shows that they don't know me very well). Keep in your mind however that old sages often say stupid things. So take what you want, and take the rest from young geniuses.

With EF, yes, you will keep control over your database schema. Data entities do not create your schema, they simply read it to generate classes.

Yes, EF will take over the CRUD operations, but never, never, never as well, as cleanly and with the performance that you would get from your own code.

Easy to maintain? No, at least in my experience.

In my opinion, EF is completely avoidable, and you have almost only good reasons to avoid it. But I wonder if you will be able to skip it. It's like touchtones phones when they came on the market. Why should I pay more to push a button than I do to turn a wheel. But today, if I had kept my good old dial phone, the only place I would still be able to call would be my dear mother's home. I unfortunately cannot press 1 on dial phone. But I always got to speak to a real human when I had a dial phone. So, if I had the choice...

I am not against new technologies, not at all. I would not still be working in that field of ours. I would not have jumped like a little kid in the 6500 classes that made the original framework after the decade I had spent feeling at ease with the 60 classes or so, mostly all controls, that were in the old VB. Big companies would not hire me to train their programmers. But I will jump into new technologies only if they bring me something that I did not have before, or if I am forced to do it.

For now at least, I am not forced to use EF. And I don't think that it brings me much, quite the contrary. Here is why.

I used to work a lot with the simple precursors of entities. The DAO DataControl in VB2 or 3. Then the ADO DataControl in VB4 or 5. When .NET came, these were gone. They were part of the many things that were completely dropped and could not make it when you stupidly tried to convert a VB6 application to VB.NET.

Then, in.NET, came the Typed Dataset, soon to be replaced by something whose name I can't remember, followed by LINQ that was all the craze when Visual Studio 2008 came out, but was almost forgotten (as far as database access is concerned) when the entity framework came out as a forgotten baby with 2008 SP1.

All very nice tools. The problem is that every 2 years, or 6 months, or so, they brought out a new tool, and the code from the old tool cannot be converted to the new one. You either rewrite (explain to a customer why you should rewrite something that works) or start using the new tool on new stuff. You then end up with applications that use 4 different data access paradigms. What a mess to MAINTAIN. How can you hire new programmers that know enough about all the different technologies to be able to properly maintain old code. We are close to the point when we will have programmers who were not even born when typed datasets became obsolete. But they still live in a lot of code, and we still see questions about them here and there. Reminds me of COBOL in many ways.

But you know what? Although I would never use it, the old "pure" ADO code I wrote in VB4,  (we're in VB14 now if my count is good) could probably still run as is in .NET. Not the code written around the RAD tools such as the DataControls. Code lives, but tools don't. So, when you think about maintenance...

Yes, the data entity is a faster way to go. Yes, Microsoft pushes everything around that, and has been for a lot longer than it did for any of its precursors. So, one might think that this is it, they finally nailed it, we can jump in the wagon and expect it to traverse the universe and time. But nobody who has followed Microsoft for a while would bet on that.

The code that was written around Typed Datasets in 2003 is obsolete today, although it stills work if I am not mislead. The code that was written in straight ADO.NET at that time is not obsolete. Some of it might be enhanced with methods that were added along the years, specially the asynchrone methods, but if it was written then as it should have been, it is still a good and efficient way to go. Most if not all the "code faster" tools that were created since the advent of .NET rely on ADO.NET in the background. ADO.NET lives, but RAD tools come and go. And all these tools do is add an extra layer that makes your work easier. But with a small paycheck in performance and, in my opinion, a big one in debugging. And also a paycheck on your time and expertise. Programmers who have been using ADO.NET directly since the beginning have developed quite an expertise. Those who went through the 4 different RAD technologies that went in an out during the same time had to relearn new tricks as soon as they became comfortable and efficient with something,

When I have a problem in ADO.NET, I look at the code and usually see what "I" did wrong. But when you are working with an extra layer between you and the database, and something that was ReadWrite one day suddenly becomes ReadOnly with no change in your code, try to see what makes the thing ticks in "their" code. It might simply be a specific value that never went in before. Already hard to spot in your code, imagine when it's in some code in the classes created behind your back when you work with data entities.

Now, I know, you never add a new field in a database. Nobody does that, isn't it. It always sends something in the fan. But as a bad programmer that I am, I do it from time to time. And in all the few instances where I tried working with data entities, it always ended up bad at some point when I had to change something in me schema. Not blowing something throug the fan, but going  straight into my face (this is a lot worse, I assure you). I know, I should not have added an extra field in my database when the government added a new deduction from the paycheck. Or when a merger forced me to keep two product numbers in two different formats for a while. But I told you, I am a bad programmer, I like to do stupid stuff like that. And the worse, it pleases me. Oh! Man! How do I love solving problem in code that I did not write, or even better, nirvanha, code that I do not see. Are you like me?

Seriously. The problems might be on my side. Maybe I did not have the patience to go on and would have found that I was the one doing things wrong. But never in my long life as a programmer did I have so much pain moving into a new technology, and few have moved as much and as fast as I did. I lost more time than I gained. After giving it a few tries, I came to the conclusion that what I gained having the data access classes done automatically was not worth the control I had over what was happening. And most of the time gained was eventually lost by more difficult debugging and problems that came out after simple but unavoidable changes in the schemas of the databases I worked with.

My experience is that if you are doing an application to maintain the teams scores for you bowling league, go ahead, don't lose time creating your own classes and that repetitive code that you have to write everytime you you have a method or property that requires you to call a stored procedure. Let the Entity Framework do it for you.

But if you are into applications that are critical for an entreprise, that will live for years if not decades, for which you might often have to do little changes without too much fuss, you might be better to work harder in the start, than waiting to end up with problems that are hard to pinpoint. If you value creation over debugging and maintenance, I do not thinkg that  EF is the way to go.

Yes, when I act as a bad programmer and add a new field in a table or change a stored procedure because of new requirements, I have to go back to my classes and check and change a few things. But personnally, I find that it often takes me less time than rerunning the wizard(s) or calling a Refresh while crossing my fingers that was in the old code will still work with the new entities.

This is a discussion that often came when I do training, which is my main activity nowadays. And I can't count the number of students who have come back to me after a few months or a few years and told me something like: "Tu sais, Jacques, tu avais raison. On a essayé les outils automatiques, et il y avait tellement de places où on bloquait et devait nous tourner vers le ADO pur qu'on a éventuellement décidé de laisser tomber les outils. De cette façon d'ailleurs, on n'a qu'une seule technologie à maîtriser. Et on devient meilleurs, parce qu'on l'utilise plus souvent que si nous avions à partager notre temps entre 2 façons de faire les choses. Il n'y a qu'en utilisant ADO tel quel qu'on arrive à tout faire avec une seule technologies.".

Sorry, but that is exacly the way they talk to me here. So I translate: "You know, Jacques, you were right. We tried the automatic tools, but got stuck in so many places where we needed to revert to pure ADO that we eventually dropped the tools. Also, that way, we have only one technology to master. And we get better at it, because we use it more often than we would by splitting our time between 2 ways of doing things. It's only by using ADO as is that we end up being able to do anything we want with only one technology."

So, if you can, if you are not stuck with project managers or customers that do not understand anyting, people who drop everything to jump on anything new that Microsoft says is the new path to heaven, if you have the leasure of spending a little more time on development and want to spend a lot less on maintenance and learning all the new wrapper technologies that they keep adding over it through time, why not go ADO.NET with your own classes, pure classes, that do the required jobs as efficiently as is possible, and that are not bloated with features that you will never use.

Oh! Boy! Everybody else who have data entities on your wall, and kneel in front of them for a minute or so before going to bed, don't be too harsh toward my beliefs. Everybody has the right to his own beliefs. These were mine.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I personally love EF, but I am NOT a fan at all of code first.  I always design the database first, and then generate the EF classes from that.

And in most cases, it does a great job.  Massively eases up on the amount of redundant code you would have to write yourself to create all your own wrapper classes.  It used to be a lot heavier and have a lot more code in it, but generally they're very basic classes that are created.
The change tracking and automated references are very handy.

It does however, do a very bad job on stored procedures, and not a fantastic one on views.  There are limitations, but overall it's a great tool for the job.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jacques Bourgeois (James Burger)PresidentCommented:
And since stored procedures are usually recommended over SQL in the code...

Give a look at the Use Stored Procedures or Parameterized Queries section in https://msdn.microsoft.com/en-us/library/ff647793.aspx.

And using EF, you cannot control many of the other issues in the same page.
Might argue that point on usually recommended.  

IMO, for simple CRUD operations, wrapping simple single row insert and delete statements into stored procedure calls to do exactly the same thing is redundant, unnecessary and not the best use of developer time.

For any complex queries, I almost always use procs (as I will freely admin EF will create horrid, bloated queries on more complicated items), but there's nothing stopping you from wrapping standard ado.net commands for executing complicated procs into the same BLL that holds the EF context and keeping access centralized.  Then you have the benefit of simple easily updatable entities for basic operations, and procs still called from EF for the more complex.

EF natively uses parameterized queries for everything, so security on that point is not an issue.
And the article attached is referencing SQL 2000, 5-6 versions and 11 years out of date, and even claims "Retired Content" at the top.
A lot of the concerns are still valid, but there have been such massive improvements on generated execution plans and performance since 2000 that it's a bit of a different story.
Jacques Bourgeois (James Burger)PresidentCommented:
I did knew that my reference was for 2000. But with MSDN now mixing answers from different different sources its becoming harder to find the official documentation, and I did not have time to look further than what I found first.

Here is a more recent one. It says basically the same thing.

One might also give a look at shahid_reza in the following post. It goes in more details and in different ways to explain the advantages of stored procedures.

As I said, simple queries and EF are good for bowling league types of applications. Not good at all for big complex applications that perform critical tasks for the entreprise. One has to judge for middle size applications.

It's does not take that more time to write a simple SQL command in a stored procedure that it is doing so in the code. Just thinking that you already have a first step against SQL injection might compensate for the difference in time. Also thinking about how you can reuse stored procedure, and you start to see that there is no time advantage for the programmer to use them instead of in-code SQL.

And have you ever been stuck with having to move everything from one database platform tools to another? The tools that do conversions will do a good to excellent job converting stored procedures, but won't touch anything done in code. Even quite simple SQL commands might need to be converted, because although there is a standard with SQL, every database vendor adds to it in order to try to move ahead of its competitors or to try to make things easier for programmers.

Over all their other advantages, using Stored Procedures is the best way to diminish the needs for maintenance in the future, sometimes such as to save hundreds of programmer time.
pzozulkaAuthor Commented:
Thanks to all for your great feedback.

James, I completely agree on your point that Microsoft has come out with a lot of "cool new tools" in the past that they've pushed heavily, and in the end, they never gained steam. Developers who followed the hype, lost many hours trying to learn one new technology after another until they have become obsolete (sometimes rather quickly). However, with EF, it's on version 7 now I believe, and initially released in 2008. Don't you feel that it's here to stay at this point, and that it may be worth looking into at this point? Granted it might still have some issues (stored procedures are not up to par, etc.), but I have to assume this will be ironed out in the versions to follow.

My point is this. I love ADO.NET, but it causes a lot of redundant code. Which is fine, if there's not better alternative. And perhaps, EF is not a better alternative...yet. But if EF continues to improve, at what point would you say a technology has come far enough to begin to work with it for enterprise level applications.

From what I've gathered from all the feedback is that it has it's benefits, and drawbacks. The drawbacks seem to outweigh the benefits, especially when there exists a bug, in an auto-generated class that you DID NOT write. I would hate to spend countless hours searching for something like that. Also, the lack of good sprocs support sounds like a big drawback as well. So again, there's pros and cons, and because the cons outweigh the pros, I'm hesitant to start a new project using EF. At the same time, I don't want to fall behind the pack and want to stay up to date with my knowledge on evolving tech. So having said that, I'm not sure when to start diving into EF if it keeps on its current course? Perhaps version 10?
Jacques Bourgeois (James Burger)PresidentCommented:
I addressed the fact that Data Entities have been around for longer than other database tools technologies.

One might think that this is it, they finally nailed it, we can jump in the wagon and expect it to traverse the universe and time. But nobody who has followed Microsoft for a while would bet on that.

That comment was vague I know. That is because I have no definitive opinion about that, everything is speculative when you start thinking about the future, universe and time. Data Controls were also the way to go for as long as EF has been there. But they were one the main things to drop when moving from VB6 to VB.NET. And Microsoft does seem not push at all on their replacement (BindingNavigator). I am an avid video gamer, but do not like to gamble. And for me, betting on EF is gambling. True, it seems a not too risky gamble. But I would subject my code to a bet, no matter what.

And betting or not on EF was only an introduction to my argumentation. The main thing is that coding the stuff yourself and using stored procedures, if done properly, is the best way to insure maximum performance and security, with minimum debugging and maintenance. Everything reposes on the importance you put on each of these 4 elements of programming.

EF as a better alternative to ADO.NET? Yes! Totally! Without any hesitation...

... if you are in a rush and work for an employer or customer that looks only at the cost of developing a product in 6 months instead of 7, without giving a look at the cost associated with probably many hundreds of extra hours on maintenance. Anybody who has been in this field for a few years knows that the real cost of an application is not its initial development, it's the maintenance, including the necessary upgrades as years go by and you move from one version of a given database to another, possibly one type of database to another, from one version of the OS to another, from desktop application to mobile application (no, we will never do that, they say today).

it's on version 7 now + I have to assume this will be ironed out in the versions to follow. If it has not been ironed out in 7 versions, do you bet it will be in future versions. Gambling, from my point of view.

Opinions may vary over that, but EF is not a technology. It's a tool that runs over a technology that is called ADO.NET. Yes it will evolve, but it is still lacking after so many iterations. In my long (too long some will say) experience in programming, I have learned that good technologies are usually quite right at the moment they come on the market. They are never right at that point, but close usually close. It did not take 7 versions of Windows before it became Windows NT. Framework 1.0 was already quite solid. Maybe not by today's standards, but for the time, it was something you could count on, more than anything that came before. SQL Server also came out good quite fast. And these are huge technologies to develop. If a simple tool as EF is still lacking after 7 version, I repeat myself, I would not gamble on it.

Your last paragraph is very close to the way I think.

But if I were you, I would take the time to give EF a try. Just think of the stuff you want to do in your projects. Give a try to EF in a smart test project. It is supposed to easy to start with. In my experience it was. But it is also that experience that showed me that it was not up to the level of work that my customers expected of me. So, you might learn from that.

And do not test on small stuff. Do it for real things. I do not know about you, but there is nothing I dislike more than having to work on an application created with 2 or 3 different technologies. You need to know them all, it can become confusing, and you might have problems hiring somebody that can work will all of these if you are in a rush to replace a programmer who suddenly has to go on an extended leave for some reason or another. If you test only on the small stuff, you might decide to go for it. And find out later that there are things that you cannot do, that require pure ADO.NET. And then be stuck with 2 technologies to maintain, to keep informed about on each new version of the framework. So test EF on the hard stuff too.

In my opinion, when you go for a decision between LINQ, data entities, the likes and ADO.NET, it is an all or nothing decision. Mixing technologies is sometimes inevitable, but it always ends up being a big pain.

I am at the point in my career where I do not have to take such decisions anymore, and am I glad about that. I had so many choices to do like that before: Apple or Microsoft, MacIntosh or Windows or OS2, VB or C++, SQL Server or Oracle, .NET or stick with what worked before. I've been lucky, I think I always made the right choice, at least for my own needs and the needs of my customers.

I wish you luck in the decision that you will take. No matter what, no decision will be completely bad. But choosing the track that is right for youself means more happyness in your work, and thus in your life.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.