LINQ to SQL

There is an updated post, written in August 2016: https://www.stum.de/2016/08/24/thoughts-on-orms-2016-edition/

When someone asks me about my opinion about Microsoft, my answer will be among the lines of this: “Their Development Tools are a shining beacon on a sea of darkness, they have moved into the future at a time when everyone was still trying to polish the crap from the 1970’s. But instead of ruling the tech world with high quality products, the inability of their management to execute has allowed other companies to dominate them”. Today, I want to look at one of the many technologies that meet that description.

LINQ to SQL (or L2S) is a phenomenal technology and as of February 2013 still my ORM of choice. But the amount of failure around this product is stunning. First of, L2S is an ORM made for a single database: Microsoft SQL Server. It can’t do Oracle. It can’t do MySQL. And it won’t do any of the shiny new NoSQL databases. It does do at least three versions of SQL Server (2005/08/10, haven’t tried 2000 or 2012) though, and it does them extremely well.

There are several complaints about it though, one of them being the SQL that it generates. After trying out other ORMs, I found that L2S generates by far the best SQL. Open up System.Data.Linq in dotPeek and look at the complexity within it. I don’t know how many manhours went into this, but all of this was made to generate good SQL, because it was focused on a single underlying database. Unless EF significantly improved over the last 6 months, it is nowhere close to generate good SQL. (Side note: Entity Framework didn’t support mapping columns to enums for a long time. I think they added that, but this was the thing that told me that EF is not a serious product.)

Sometimes, your queries cause joins or subselects that you did not expect. Accidentally writing a foreach-loop that makes 1 call per iteration (rather than 1 bigger call before the loop which is iterated over) is easy, in fact it’s almost the default in a lot of cases. Finding these instances is hard because your only built-in tool is the SQL Server Profiler, and that one doesn’t come with SQL Express. I bought Ayende’s L2SProf and I am still almost recommending it to everyone working with it, it is THE #1 debugging tool. Why did I write “almost” then? Because Sam Saffron in his infinite awesomeness made MiniProfiler, which is not only a requirement for any serious ASP.net MVC developer, but it does a spectacular job capturing and analyzing the generated SQL.

Once you are past the stupid but super-common SELECT N+1 problem, you can tweak the LoadOptions to force joins/subselects (e.g., “I know that when I select a ParentObject, I need its ChildObjects as well, so let’s tell L2S to fetch them automatically, thus avoiding or at least improving on SELECT N+1”).

That last point (Monitoring Queries) is a good Segway into another issue, the tooling support. Microsoft loves Designer Surfaces, and so it is no wonder that L2S is driven by one as well. To work with L2S you need to generate database classes, and this is usually done though a .dbml file. This is one of the weakest points, because that stuff is awful:

• If you want to map a column to an enum, you often need to prefix the enum type with global:: (e.g., global::MyApplication.Models.MyEnum). If you don’t you get an unhelpful error message.
• If you ever change the table definition in the database, there is no “Refresh Table from DB” command. You either manually need to add the column or remove and readd the table on the design surface, losing all other changes (like enum mappings).
• The Code was generated by a tool, and it looks like it. It’s ugly.

DBML files are garbage. So I went ahead and created a T4 template that connects to the database, reads the table definitions and creates L2S Classes. If I change the schema, one click on “Run Custom Tool” regenerates the whole lot. The generated code is green in Resharper. Classes can be partial if I need to extend them without affecting the regeneration. Classes implement ICloneable and a CompareWithClone method that allows me to get a list of changes between two instances (e.g., for Auditing purposes – Fetch record, clone, modify record, compare clone with modified record) and it does so without Reflection. Foreign Key relations? No problem, EntityRef/EntitySet is generated. Writing this made my experience with L2S a million times better. I do not have it to share at the moment though (working on that).

The future of L2S is in doubt since that “Update on LINQ to SQL and LINQ to Entities Roadmap” came out. However, it got a massive update in .net 4.0, despite concerns. I think it even got support for SPARSE columns in a later update.

It has a set of issues (e.g., the mapping code is SLOW) and it seems unlikely that these issues are resolved. And this is where Microsoft’s inability to execute properly shines through. Integrating with SQL Server is a beauty in .net, much better than working with the awful, awful Oracle or MySQL client libraries. It is not as sexy as the NoSQL stuff (where RavenDB is the product of choice if you want a document db) but SQL Server is the workhorse that does 95+% of the work in all applications and it’s the default choice for .net.

I don’t want a product that gives me half-assed integration with multiple databases, because I don’t need to support more than one, and chances are high you don’t need either. I want a single kick ass technology for SQL Server. I don’t want to write mundane CRUD SQL queries manually, because these aren’t the 1970’s anymore. But when I do notice a bottleneck, I want to be able to optimize it. L2S does that (did you know that DataContext.ExecuteCommand allows execution of SQL?). I love Dapper, but it’s a bit too much plumbing for the simple, common stuff, sorry. I want to reduce complexity and code size, not increase it by hand-optimizing a simple SELECT. That’s like writing a game fully in x86-64 assembly language instead of using a more high level language and only go down to assembly where it offers an advantage worthy of the price.

As an external dev, I can fix some of it. I fixed the DBML madness. I fixed the testing issues (see that generated XML file? It contains enough data for me to spin up either a SQL User instance or a SQL Compact database, recreate the schema and then clean up at the end of the test).

But I can’t fix the slow mapping and I can’t add any support for SQL Server 2012 improvements. And if Microsoft doesn’t want to, this is just another slightly unpolished jewel that they are throwing away instead of making it shine.

I do not know who owns LINQ to SQL internally at Microsoft, but here is my plea: Please open source LINQ to SQL. You have done so with a lot of the ASP.net stuff and in my opinion it made it a better product. I don’t want to decompile the DLL and try making my own, illegal fork of it. I want to go in and fix the performance issues around mapping. I want to sit down with a database guy and the debugging tools to see where queries can be optimized.

I want LINQ to SQL to be the best tool to work with the sanest RDBMS choice on .net and even though it already holds that spot, I want to see it leave the competition in the dust. Please Microsoft, stop screwing this stuff up.

Thanks,
A developer that uses the heck out of L2S every single day