One of the supposed advantages of the EF is that it abstracts you from
your database implementation. This is considered to be a good thing. It
lets me move between different database products (SQL Server, Oracle,
MySQL, etc) without having to rewrite anything the business tier of my
application. I just need to hook up a different provider to the entity
framework and I'm done. All of my business logic and query construction
stays at the application layer.
Ironically, this paradigm, which
comes from the object oriented world, fails to recognize one of the
most basic tenents of object oriented design. The separation of
interface from implementation!
First of all, I'd better point
out the reason for this post. Why is it a bad thing to put the query
construction into automated tools like the entity framework or
nHibernate, or into the hands of the application layer more generally?
The
short answer is performance. Databases aren't given much respect in
many organizations. It's just a place to persist information, right?
No, of course not, that's completely wrong. A database is an extremely
complex system of physical constructs like indexes, partitions, columns
composed of data types, keys, etc. SQL databases also have a
standardized (though vendor extended) language for data retrieval and
modification. This, of course, is SQL.
SQL is not a procedural
language. SQL is a declarative language. Procedural programming fu do
not translate to SQL-fu, and vice versa.
A person focused on
database development generally knows at least a little something about
database design, indexes, partitions, keys, and of course SQL. There's
a lot to know. I've been doing it for 7 years and still learn new and
complex technical details about SQL Server every week, if not every
day. A person focused on database development also, as a rule, knows a
little something about SQL.
A procedural programmer, as a rule
does not. Or at least, obviously someone who has spent 10 years mostly
focused on, say, .NET won't know as much about it as someone who has
spent the same amount of time focused on, say, SQL Server. The people
who wrote an object-relational mapping tool might know a little, but
they don't necessarily. And more importantly, the tool certainly
doesn't know anything about your specific indexes, partitions,
performance requirements, storage system, etc.
Long story short,
this means that the ability to write sensible and efficient SQL for
your specific database will only ever be possible by someone with
knowledge of the logical and physical structure of that database, and
never by an automated tool, or a tool which translates one language
(like LINQ) to another (like SQL).
Now here's the key to this
post. An ORM layer which dictates certain database requirements and
builds your queries for you is COMPLETELY UNNECESSARY in order to
achieve the goal of database vendor independence. The belief that it
accomplishes this disregards the separation of interface from
implementation.
Your database developers can, and should, do
this for you by providing the application code with an API. From an
OOAD point of view, this is simply the separation of public methods
from private data, and has been around FOREVER. How do we accomplish
this? With stored procedures, or routines. If we switch vendors the
only thing we have to do is ensure that the database provides the same
set of routines to the application layer. The code inside those methods
is irrelevant to the API itself. I can build a GetCustomer routine
(stored proc) in SQL. If a decision is made to move to Oracle, all that
matters is that the GetCustomer routine is provided, taking the same
parameters and returning the same results. The fact that internally it
is built using PL/SQL instead of T-SQL is completely hidden from the
application layer.
This does mean that the code INSIDE the
routines may have to be rewritten. I say may because if standards
compliant SQL is used, it doesn't. You can literally do a copy and
paste. But the thing is, you probably want to rewrite that code anyway.
Why? For the reasons I discussed above. The performance of your queries
depends extremely heavily on your logical data model, your physical
database structures, and requires a deep knowledge of same. That's
precisely the problem with automated query generation tools... they
don't. Your performance can be orders of magnitude worse if you don't
tune your queries based on your structures, and vice versa.
Need
a concrete example? What about nHibernate. GUID's are usually needed
for object creation, so each object gets it's own unique GUID which is
then used for lookup. Using GUIDs in a database has the potential for
MASSIVE performance loss. I'm talking orders of magnitude on query
times, as well as vastly (VASTLY) increasing the physical size of the
database on disk. Why? The reasons are complex, which is exactly the
point. Care in how they are used is required to avoid this. But do the
application programmers know the pitfalls? No, they're application
programmers! They don't have to know anything about databases, that's
what nHibernate is for, right?
