ADO.NET Entitity Framework vs. LINQ to SQL

I have never been a huge fan of all the ADO.NET stuff – one exception: the DataReader. Everything else seems to me just to be bloated functionality around what I really need. How ADO.NET handles connections and commands are in my opinion overly "complex" – not that they are complex in any way, they could just be done way simpler. Whenever I have been writing code that access a database, I tend to rely on my trusted companion: the Enterprise Library. It provides such a nice and clean abstraction to the unneeded (IMHO) overhead posed by Connection, Command and Parameter handling.

Compare:

Database db = DatabaseFactory.CreateDatabase("my connectionstring"); 
using (IDataReader reader = db.ExecuteReader("my stored procedure", "my parameter 1", myParameter2, etc)) 
{ 
    // do my thang 
}

To:

using (SqlConnection conn = new SqlConnection("my connectionstring")) 
{ 
    conn.Open(); 
    SqlCommand cmd = conn.CreateCommand(); 
    cmd.CommandText = "my stored procedure"; 
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter param = cmd.CreateParameter(); 
    param.ParameterName = "@param1"; 
    param.DbType = DbType.String; 
    param.Value = "my parameter 1"; 
    cmd.Parameters.Add(param);

    param = cmd.CreateParameter(); 
    param.ParameterName = "@param2"; 
    param.DbType = DbType.Int32; 
    param.Value = myParameter2; 
    cmd.Parameters.Add(param);

    param = cmd.CreateParameter(); 
    param.ParameterName = "@param3"; 
    param.DbType = DbType.Int32; 
    param.Value = etc; 
    cmd.Parameters.Add(param);

    using (IDataReader reader = cmd.ExecuteReader()) 
    { 
        // do your thang 
    } 
}

And don’t get me started on the DataSet…

Now, MS has implied that LINQ to SQL will be deprecated and focus will be moved to the ADO.NET Entity Framework and LINQ to Entities. This has already stirred up the .NET community: David Hayden, Ian Cooper, Ayende Rahien to name a few.

I have never really considered using LINQ to SQL in big applications, mainly because I think LINQ to SQL imposes some constraints on my applications – one simple thing like the “low coupling” principle is not something that comes out of the box, it has to be tweaked and twisted to even give just a hint of low coupling. However I see LINQ to SQL as a very, very nice low-level method to create a basic SQL based data foundation.

For example, I am privately playing around with a blog engine based on ASP.NET MVC and LINQ, both XML and SQL. In this case LINQ to SQL is near-perfect for my needs. It provides sufficient abstraction from the database without imposing too much overhead, by providing flexibility in all future scenarios if the database suddenly need to be converted to an Oracle DB or refactored/de-normalized due to poor performance or whatever. It allows me to write SQL like-statements that (to a large extent) is verified at compile time. Sure I could do the same in LINQ to Entities, but it feels like firing up a super-tanker for something that really just need to be according to the KISS principle. LINQ to SQL is to a large extent very much KISS’ish, and fits perfectly in to the use I required for my playground project here.

I like to throw in this favorite quote of mine:

"Perfection is not when there’s nothing to add, but when there’s nothing to take away."
- Antoine de Saint-Exupéry

Or Occam’s Razor for that matter:

“All other things being equal, the simplest solution is the best”

Another thing about LINQ to Entities (and this is just based on a my brief experience with the ADO.NET Entity Framework), the designers within Visual Studio are not very good pretty bad very bad.

When creating a new “ADO.NET Entity Data Model” it does allow a reverse engineering possibility for an existing database. The model you end of with is simply a 1:1 mapping between entities and tables, i.e. the Active Record pattern. This is fine, but LINQ to SQL does the same – again “supertanker”. The Entity Framework is designed to be an abstraction of your database not “just” an Active Record pattern, i.e. your data model does not need to reflect your database schema in a 1:1 mapping. This is pretty neat, and can be useful, especially on larger projects where you are not creating the database schema yourself, or you are working on top of an existing SQL database. BUT if I create a very simple SQL database for a very specific scenario, I don’t really need this.

Another thing that is very bad in the designers is that once your model has been reverse engineered and you want to begin modifying/abstracting it, things get very, very tedious. The tools in the designer are not helping very much, i.e. apa

rt from initial reverse engineering the database, utilizing the schema meta data to create properties etc. is not possible. The model is (granted) fairly intuitive, but the task of adding properties that map to columns, etc. is just overly “complex” (again complex in as it could be done way simpler).

The key feature of the Entity Framework to work on-top of any database has also on my first shot failed. Maybe it is poor database design, I’m not really a DBA so I’ll let others be the judge of that. But I have a scenario where I have a n:n relation between two tables – Person and Group. But instead of having the foreign keys in my cross reference table being the primary key hereof, I have an explicit primary key:

When creating the ADO.NET Entity Data Model in Visual Studio (by reverse engineering).

That is of course fair enough, so I remove the PersonGroup entity and create a many-many association between Person and Group with the following setup:

I get this error:

Error 3025: Problem in Mapping Fragment starting at line 115: Must specify mapping for all key properties (PersonGroup.Id) of table PersonGroup.

You also have to map ALL columns in the database to properties in the entity model, maybe I don’t what that! What if my entity model is really built on top of an already running database, and my job is to create a another user interface for e.g. a mobile device which requires just a subset of the database schema. I don’t want to map the entire shebang, for one because it is very tedious and second because the rest of the schema might contain sensitive information that should not be exposed on the mobile device (or whatever reason).

What is it with that homemade Entity-SQL stuff, that really smells too much like reinventing the wheel – we already have SQL (a standard) which on top of that has T-SQL and then we have LINQ in our managed code – this is really enough query languages for one application! Do we really need yet another proprietary query language (although it has a lot of similarity with SQL – but so does T-SQL and LINQ)? I don’t think so!

Not to mention that for demoing LINQ to SQL is just perfect – however I don’t (sadly) think that could justify MS investments in the LINQ to SQL framework alone.

This is just my gut feeling, maybe it has no substance when it comes to reality! But one thing I know is: I like LINQ to SQL for what it is, but equally much for what it isn’t.

Please prove me wrong!