6

Does Entity Framework mean you might never have to use SQL or design database tables again?

Is this what "persistence ignorance" means?

I am new to EF and ORMs in general, and I would like to understand how much of it is hype and how much is reality.

17

EntityFramework is a great tool, but like any great tool you would do yourself a great disservice to not understand the inner workings of how it translates a data model into a database schema, and also how it translates its own queries into SQL.

Everything works great until something goes wrong, then you are looking in log files trying to decipher the generated SQL to figure out where the bug is. Good luck with that if you do not have an understanding of the schema or SQL.

The term "Persistence Ignorance" is heralded as a good thing, but only in terms of creating loose coupling from your data access layer. Your business logic and presentation layers should be "Persistence Ignorant", not the developer!

Only in the US is Ignorance celebrated as a good thing ;)

  • An example of your second paragraph - We had a stored proc on a "black box" legacy database that was timing out under certain circumstances. It took hours to resolve the problem since this usage was so well buried (in this case in CSLA code, but I suspect EF would be similar). Then we had to locate someone with permission to edit the SP. Bottom line is that generated code can mask problems if one isn't careful – jfrankcarr Dec 5 '11 at 14:34
  • 1
    +1. Over in the Java space, we have TopLink and Hibernate which predate EF by a few years; they absolutely haven't invalidated any SQL knowledge. They've also proven not to be a silver bullet, some problems are better handled with plain SQL or one of the NoSQL datastores. – Barend Dec 5 '11 at 14:36
6

Actually no. The basic database concepts will apply and you have to know some basic rules of what databases can do or else you'd face significant performance issues.

Entity Frameworks are useful to handle the conversion of mapping data from database records into objects. This allows developers to not have to deal with creating data access objects because the framework does it for you already.

So this will do quite a bit for developers especially when persisting data.

Looking up data can be performed with entity frameworks as well, but lookups are not always best done with just entity frameworks alone because if done improperly you will have memory issues when someone decides to load up the entire database into memory. Or I/O issues if someone decides to do queries inside another query. (Those two are the common performance issues I see when I review other people's code).

Sometimes it may be that you need raw SQL, stored procedure or a database view in order to get the best performance/maintainability ratio.

As such a good DBA is a valued resource. Although I try to relegate the database to be just a data store, they still need to perform tuning so the proper indices and storage allocation is done correctly.

One thing that entity frameworks allow me to do is to talk about objects and tables with the DBA in logical terms rather than physical terms. It allows application development and database to form a contract in the form of an Object Relational diagram that they agree to reduce the chances of defects (or at the very least quickly assign the blame).

UPDATE: Three other things I would like to add...

  1. Views. ORMs may be able to read views, but it still takes a relatively skilled DBA to make a very complex one so it will optimize queries for other parts of the application.

  2. Data migration. As your project grows so would your schema. You'd need a skilled enough DBA to know how to migrate your data from one version of your application to another.

  3. Data archival. As your project keeps on going, eventually you'd have a large dataset and you'd have to figure out how to partition and archive the old data so it will not impact the performance.

  • +1 for EF is an ORM and as such it is good for simple reads but potentially a big problem for complex (i.e. real world) reads. – Joel Brown Dec 5 '11 at 13:28
  • I don't understand how it could get the SQL wrong. If the compiler understands your objects how could it not translate it into SQL. I reckon I could code it myself. ;) – CJ7 Dec 5 '11 at 13:33
  • 2
    @CraigJ : in most cases it will generate valid SQL which will get you the result set. But, because we are dealing with a generic sql generator, it will not generate the optimal sql statements for the problem in many cases. 99.99% of the time that doesn't matter. That other .01% of the time it really, really, really matters. – Wyatt Barnett Dec 5 '11 at 13:55
  • @CraigJ On top of what Wyatt Barnett pointed out, many times a small mistake in your data model, a disconnect between your actual data model and schema (usually when generating model from existing schema, not the other way around), or a hard to spot EF query bug, can sometimes require deep inspection of generated SQL to quickly locate the problem. Sometimes to avoid face-to-keyboard bouts of extreme frustration, it is easier to turn debug logging on and copy SQL directly into your database query editor. I can't tell you how many times I have had a WTF OF COURSE moment of clarity from this. – maple_shaft Dec 5 '11 at 14:20
4

+1 to maple_shaft. I would add that even when you are using the (much balyhooed) EF Code First you are still modelling tables. It's been more than 20 years that people have used modelling and CASE tools that put an abstraction layer between themselves and the DDL script that builds their tables. That doesn't change the fact that they need to know how to design tables properly.

2

Definitely no because EF is heavily dependent on correct database design. It usually fails expectation where used with legacy or incorrectly designed databases. Also full power of EF can be unleashed only when combining with direct SQL.

Each abstraction shields you from some implementation. ORM is abstraction of data access but only coding monkeys are shielded from these details as well. Developers must understand concepts running under the hood of abstraction to know how to use them correctly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.