Should I migrate to Entity Framework? [closed]

Question

I've been using the SqlCommand class and writing my own T-SQL queries for a very long time. I've stuck with it because I'm comfortable with it, and it probably stemmed from me starting as a windows developer in the days before we had many other great options. I've been doing web development now for about 10 years, and still using it and writing my own query strings. I've written a dynamic class for all my database needs on the fly, all I have to do is write the T-SQL, which I can do quickly and use in any project.

Other developers have got big eyed when they see my code, and encourage me to learn LINQ which I do not like at all.

I'm starting a new project now in ASP.NET MVC and now feel like I should get out of the dinosaur days and move to Entity Framework to handle my data.

I know that the SqlCommand class is the nuts and bolts of the Entity Framework behind the scenes, so I feel like why should I go through the middle man (entity) to achieve the same results and spend time learning something new.

Can someone please explain to me why I need to move on, or if I should stick with what I know?

Cachou

You may find that in the long run you save time and avoid complications. The reason you are using MVC over WebForms or standard ASP probably bears similar reason? – Casarez

I would give EF a try in a sample project. I've switched to EF quite a while ago (with .NET 4.0), and never looked back. EF can do 80% or so of your boring coding for you - and you can concentrate on the more interesting parts! – Larine

If you like your writing your own queries and want a small step closer to entity framework without a deep dive checkout out Dapper.Net(As an alternative to EF). – Betthel

Answer 1

Well, with SqlCommand... If you're doing it right, you've either already written the equivalent of an ORM, or you are spending a lot of time writing code that does stuff like packaging up arguments, and mapping columns to objects. If you're not doing either of those, then chances are you have unsafe code and are passing around untyped DataSets and then doing lots of casting, or you're doing dangerous concatenated SQL queries that are prone to SQL Injection.

The advantage of of using EF (or any ORM) is that it handles the SQL generation, the object mapping, the parameter packaging, and gives you a very powerful query interface that lets you write multiple queries very quickly.

Why write 10 lines of code when you can write one?

Whether or not you use EF is really a personal choice... but if you're doing serious, object oriented development, you should be using an ORM of some kind (maybe even a micro ORM, like dapper or massive). Or write your own micro-orm for that matter.

It's just a huge pain to use ADO without some kind of automatic mapping technology.

Narcose

Answer 2

The main reasons to switch to EF is

• It is faster to develop with EF once you learnt it
• Especially joins are simpler to write in most cases
• You get compile time checking that all your columns and tables actually exists
• You have the option to use EF migrations that is one of the best database migration tools out there

I switched years ago (via Linq2SQL) and have never looked back.

Go for EF code first and use migrations.

Constellate

Answer 3

I've been working with NHibernate, EntityFramewrk, LinqToSQL and with bare SQLCommand over the years. ORMs vary in quality of generated commands and the overhead that they require but they provide simplicity and type safety to your sql commands. Which you will not have if you write sql as strings. It is good to know them, as they are great tools for rapid development of small to medium application in my opinion they fail with large and complicated environments.

Recently we have started a new project and decided to use plain old SQLCommand for it with our own Linq to SQL translator. It's doing good for simple queries and we can fall back to dedicated code for more complicated ones. Other thing is that we like to have full control over our database and in my opinion we loose that when using ORM.

Gormand

I fail to see how you don't have full control over your database if you use an ORM. You aren't required to let the ORM generate your database for you. I have never done that. I always create the database first, then map the ORM to it. – Narcose

@ErikFunkenbusch yes that is an option, but what I meant as database was not only creation or updates but views, stored procedures, functions that can accomplish tasks in fraction of time compared to reading entities from orm and processing logic in .net. If I'm not mistaken EF can map views and call stored procedures from version 6 so that is new to them. – Gormand

No, EF has always been able to call stored procedures and map the results back to objects. What was new in 6 was the ability to use stored process to back mapping changes for updates and deletes. But again, I fail to understand how any of that has to do with "control" of your database. – Narcose

Answer 4

I suspect I'll be alone in this opinion, but here goes.

I've thought about this a lot myself. I'm also a SqlCommand and ADO guy, and I expect I will be for at least a little while longer.

This does remind me of a discussion on LinkedIn that you might want to look at here. I'm sure there are lots of sites talking about the advantages, but that has a few developers discussing their takes on the matter.

This is what I had to say on it:

I tend to prefer writing my own database access code, so I go the ADO.NET route. I suppose some might call me a purist (and not in a good way) for that, but I just like the flexibility it gives me. I have full control and I know whether something will or will not work, which saves time looking at documentation if nothing else. Realistically, I use some wrappers I wrote myself to do something similar to Entity Framework, but simultaneously give me more control. It's still all built on ADO, though, so I can always go in and change whatever I want, whenever I want to.

Basically, in general, I don't really see much reason to switch over. I don't think that it makes code any less readable (in fact I like the option of verbosity). I like that by writing my own statements, I can be sure that what I'm doing will work, and I can copy the code directly into SSMS with complete knowledge that it will do exactly the same thing in both places.

The wrappers I've written provide access on a few levels, one where I can write T-SQL directly in (the wrapper here is that I have a string.Format-esque, params-style way of entering parameters), one that's built on that where I can build different types of statements directly with semi-strongly-typed code, and the top-level one that's based on a series of attributes and hints that acts most like EntityFramework.

There are times when I want to use each of these, and I just find it frustrating to be limited as heavily as EntityFramework and other equivalent systems seem to enforce. I want to be the one writing my code so I know what's wrong and how to fix it. It's not like it really takes me all that much time to write a SELECT out, so it wouldn't save me that much to do something else.

And on top of that, as you said, SqlCommand isn't going anywhere any time soon. So I just haven't really found any reason to leave the comfort of having full control.

Edit:

Having read the other couple responses, I guess I have something to add. I assumed you had already written an ORM similar to what I have, but if you're writing out the full SqlCommand text for every call you make and casting everything on the fly, that's very likely to cause you problems. So I'd definitely get away from that. I tend to like, again, having my own ORM to do with what I please (I guess a good example of that is, for instance, I'll automatically parse an XElement if that's what the application needs, or I'll change Sql Spatial data into something more manageable), but you should definitely switch to EF if you're choice is between that and constantly writing out every line involved in making an ADO call. That's going to do way more harm than good.

Edit 2:

I suppose another important thing to remember is that you'll have to keep your database structure tied to your object model if you write your own ORM. EF will keep everything synchronized, which can be nice, but again I still like to have more control over everything and I don't mind doing that work myself.

Lament

I'm curious, do your wrappers create prepared statements so that they are pre-compiled, and not prone to sql injection? – Narcose

Yes. Even for my lowest-level wrapper, the one where it accepts pure T-SQL, has functions that can be used like this, ExecuteNonQuery("SELECT * FROM tablename WHERE username = @p0", tbUsername.Text);. That's obviously a completely fictitious example, but it shows the syntax. Basically I go through and add in parameters for each of the objects passed in, with the naming convention of "@p{index}". That makes parameterization super easy. As I mentioned briefly, it's not too dissimilar to string.Format, although of course using my own delimiters makes them a little more compatible. – Lament