Entity Framework vs Direct Data Access

Question

I've always used direct data access for dealing with objects in the past (manually running a query, and mapping the results into data objects). I know Microsoft is currently pushing EF for their customers to use for querying data objects.

I've got a few questions for the community in respect to this :-

• If you have a complex database, i.e. a couple of hundred tables, a decent amount of stored procedures, views, everything is in 3NF. Is the burden of managing two schemas (one local EF schema mapping and one DB) worth the trade off?

• Once you start to ramp up the data access, how does caching compare on the two? I know in Direct access you can implement any form of caching you want, does EF allow something similar?

• Given Microsoft's history of killing off products after heavily pushing them and getting people to write for them (SQL-NS, Linq-to-Sql) how likley is this to happen to EF?

As I said I'm currently heavily using Direct Access at the moment, but considering a migration (i.e with new queries going forward, not backtracking on them all just yet), and was looking for advice from the rest of the community on their views.

Answer 1

If you have a complex database, i.e. a couple of hundred tables, a decent amount of stored procedures, views, everything is in 3NF. Is the burden of managing two schemas (one local EF schema mapping and one DB) worth the trade off?

You can use automated tools to keep your EF schema up-to-date, so it's not really that bad.

Once you start to ramp up the data access, how does caching compare on the two? I know in Direct access you can implement any form of caching you want, does EF allow something similar?

As far as I know, yes.

Given Microsoft's history of killing off products after heavily pushing them and getting people to write for them (SQL-NS, Linq-to-Sql) how likley is this to happen to EF?

This question is far too hypothetical.

The issue I'm having with EF is it's performance. Yea, you get rapid development, but trading off performance. With EF it's really easy to write a bad and slow code and if you don't 100% know what you're doing, you may end up with some serious performance issues later on (especially if you're dealing with hundreds of tables).

What I'd suggest is to try some Micro-ORM frameworks, like Dapper or Massive. You don't sacrifice that much performance, but it's easier to maintain than traditional Ado.net approach.

But hey, that's just me, you may love EF.

Answer 2

The thing to keep in mind with EF or any other ORM tool is that it just converts something (Linq expression trees in the case of EF and Linq2Entities) into SQL statements. Since there is an additional layer of abstraction and parsing there, it will always be slower that straight querying. However it is usually easier on a developer to use an ORM. Fortunately, most ORMs (not sure about EF in particular) provide some way of still running queries directly when needed.

You mention having a "complicated" database, and that usually means having complicated queries. That is something Linq doesn't really like. For example if you end up having queries that join 13 tables, and almost every returned column is passed to a db function, and has a bunch of case statements, and a bunch of sub-selects, then it gets nearly impossible to translate to Linq. The easier thing to do is wrap that complexity in a view, and use EF to just query the view.

On the plus side, EF provides the developer with intellisense, since real classes are built to mimic the DB. Depending on how your EF stuff is laid out, you can set your project up in such a way that the classes are all generated from the DB, so then if someone edits the DB schema, or changes a column data type, your C# code will no longer compile. With traditional direct SQL queries, you never find that out until runtime (or integration test time).

It is all tradeoffs... IMO the best thing to do is to try both and see which you like better, or architect a solution in such a way that provides either option. In the last app I worked on, I had a "database factory" class (factory pattern) that the other data access classes would use, and they could either ask the factory for a plain old ADO.NET Command object, or ask for the ORM object (the Context in the EF case, but I was using SubSonic so instead it was an IQueryable implementation). That way you can do "simple" queries through EF, and "complex" queries in SQL.