Why use an ORM?

Question

Possible Duplicate:
Why should you use an ORM?

This quote is from the book "Asp.net mvc framework unleashed" by Walther and so far is a very good book.

The Entity Framework shields you from needing to interact directly with the database. By taking advantage of the Entity Framework, you never need to write SQL queries again. You can write all your data access code in C# or VB.NET instead of SQL.

I'm not sure I understand why we need to be "shielded" from the database or what's so great about using "C# or VB.NET isntead of SQL". I don't think SQL is a complicated language to use.

Why do we need to be shielded from the database and avoid writing sql?

PS:Most of my experience is in building a few small departmental web forms and not in building large complicated sites so maybe that's why don't understand this.

Answer 1

One of the advantages of using ORM is that you can concentrate all you business logic in one place - your application - and not have it split between your application and the database. You are going to have to map between your run-time objects and your relational data storage anyway, so having something that does it for you removes another thing you would otherwise have to maintain.

Another advantage is that you can use different data storage mechanisms (Oracle database vs Microsoft SQL database) without having to change the business logic. This can be vital if your client's company policy is to have only one database provider.

It's another tool that helps with Separation of Concerns.

Answer 2

This is a question that gets asked quite often (not necessarily on SO, but "in the ether"). Bear in mind that you can use an ORM in one of two ways:

1. Specify your query in code, which causes the ORM to generate a SQL query in the dialect specific to the individual database provider (i.e., it will write T-SQL for SQL Server, PL/SQL for Oracle, etc.). How this query is specified may vary from ORM to ORM (using LINQ and expressions is the newest way, but other ways--fluent interfaces, for example--also exist and are more prevalent), but the important fact is that it's defined in code
2. Specify your query in a stored procedure, then provide the procedure to the ORM

Strictly speaking, an ORM simply serves as a means of allowing you to access your database objects (whatever they may be) in a strongly-typed object system. The name literally means that it's providing an object-oriented wrapper around a relational storage mechanism.

Query Generation

The natural extension, though, is the object-oriented query generation, which is also the most controversial. There are people that are staunchly in both camps (one side says that all of your queries should be generated and that you're never writing SQL, the other says that you should manually write all of your SQL), and there's no way to prove that one is more right than the other; there are advantages to both.

For example, a generated query is going to be as typo-free as the the population of the database entities in code (in other words, as long as the table, column, and object names are correct in the ORM, then you're never going to fat-finger one of their names as you may in a manual query). It's also simpler to model basic--and even some complex--relationships in code than it is in SQL. Both the LINQ, expression, and fluent interfaces can provide a more intuitive (to the uninitiated) query mechanism.

On the other hand, computers are not as smart as we are. Generated queries, at least those done right now, are not guaranteed to be as optimized as possible. ORM's generally do not consider index hints, partitions, file groups, statistics, creative join gymnastics, etc. when creating their queries. In a scenario where these things can optimize the query, a hand-written query will likely be more efficient.

Object Modeling

In the end, though, the biggest boon to ORM's (IMHO) is the type safety that they introduce. The best part about this aspect is that it has only benefits; you can still hand-code your queries if you like (though most ORM's, if not all, require that these be done via stored procedures) and still take advantage of strongly-typed objects that take the tedium out of database programming.

For example, the complete code for executing a stored procedure and accessing the results in, say, ADO.NET would be something like:

DataSet set = new DataSet();

using(SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using(SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "stored_proc_name";

        using(SqlDataAdapter adapter = new SqlDataAdapter())
        {
            adapter.SelectCommand = cmd;

            adapter.Fill(set);
        }
    }
}

foreach(DataRow row in set.Tables[0].Rows)
{
    Console.WriteLine("Name: {0} ID: {1}", row["Name"], row["ID"]);
}

Whereas an ORM would look something like...

using(DataContext context = new DataContext())
{
    foreach(EntityName item in context.StoredProcedureName())
    {
        Console.WriteLine("Name: {0} ID: {1}", item.Name, item.ID);
    }
}

Obviously this is a simplistic example, and more than likely some of that ADO.NET activity is abstracted away. The advantage of the ORM is that it does this boilerplate work for you. There's no reason for you to create and open the connection, create the command, set the properties, add parameters, and so on when something can do the exact same steps for you and leave you to write your own code. Your SQL optimizations can still be present, but you get a much simpler, more readable interface into querying your database.