Philosophy of correct working with ORM (Entity Framework )

Question

Hi. I'm an old-school database programmer. And all my life i've working with database via DAL and stored procedures. Now i got a requirement to use Entity Framework.

Could you tell me your expirience and architecture best practicies how to work with it ?

As I know ORM was made for programmers who don't know SQL expression. And this is only benefit of ORM. Am I right ?

I got architecture document and I don't know clearly what I shoud do with ORM. I think that my steps should be: 1) Create complete database 2) Create high-level entities in model such "Price" which is realy consists from few database tables 3) Map database tables on entities.

Answer 1

An ORM does a lot more than just allow non-SQL programmers to talk to databases!

Instead of having to deal with loads of handwritten DAL code, and getting back a row/column representation of your data, an ORM turns each row of a table into a strongly-typed object.

So you end up with e.g. a Customer, and you can access its phone number as a strongly-typed property:

string customerPhone = MyCustomer.PhoneNumber;

That is a lot better than:

string customerPhone = MyCustomerTable.Rows[5].Column["PhoneNumber"].ToString();

You get no support whatsoever from the IDE in making this work - be aware of mistyping the column name! You won't find out 'til runtime - either you get no data back, or you get an exception.... no very pleasant.

It's first of all much easier to use that Customer object you get back, the properties are nicely available, strongly-typed, and discoverable in Intellisense, and so forth.

So besides possibly saving you from having to hand-craft a lot of boring SQL and DAL code, an ORM also brings a lot of benefits in using the data from the database - discoverability in your code editor, type safety and more.

I agree - the thought of an ORM generating SQL statements on the fly, and executing those, can be scary. But at least in Entity Framework v4 (.NET 4), Microsoft has done an admirable job of optimizing the SQL being used. It might not be perfect in 100% of the cases, but in a large percentage of the time, it's a lot better than any SQL any non-expert SQL programmer would write...

Plus: in EF4, if you really want to and see a need to, you can always define and use your own Stored procs for INSERT, UPDATE, DELETE on any entity.

Answer 2

I can relate to your sentiment of wanting to have complete control over your SQL. I have been researching ORM usage myself, and while I can't state a case nearly as well as marc_s has, I thought I might chime in with a couple more points.

I think the point of ORM is to shift the focus away from writing SQL and DAL code, and instead focus more on the business logic. You can be more agile with an ORM tool, because you don't have to refactor your data model or stored procedures every time you change your object model. In fact, ORM essentially give you a layer of abstraction, so you can potentially make changes to your schema without affecting your code, and vice-versa. ORM might not always generate the most efficient SQL, but you may benefit in faster development time. For small projects however, the benefits of ORM might not be worth the extra time spent configuring the ORM.

I know that doesn't answer your questions though.

To your 2nd question, it seems to me that many developers on S.O. here who are very skilled in SQL still advocate the use of and themselves use ORM tools such as Hibernate, LINQ to SQL, and Entity Framework. In fact, you still need to know SQL sometimes even if you use ORM, and it's typically the more complicated queries, so your theory about ORM being mainly "for programmers who don't know SQL" might be wrong. Plus you get caching from your ORM layer.

Furthermore, Jeff Atwood, who is the lead developer of S.O. (this site here), claims that he loves SQL (and I'd bet he's very good at it), and he also strives to avoid adding extra tenchnologies to his stack, but yet he choose to use LINQ to SQL to build S.O. Years ago already he claimed that, "Stored Procedures should be considered database assembly language: for use in only the most performance critical situations."

To your 1st question, here's another article from Jeff Atwood's blog that talks about varies ways (including using ORM) to deal with the object-relational impedance mistmatch problem, which helped me put things in perspective. It's also interesting because his opinion of ORM must have changed since then. In the article he said you should, "either abandon relational databases, or abandon objects," as well as, "I tend to err on the side of the database-as-model camp." But as I said, some of the bullet points helped put things into perspective for me.