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.)

image

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.

image

image

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).

image

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

Dime Fiction: The Sun Engine

(Note: This is a work of fiction that should normally go on Dime Fiction, except that I’m closing that blog because my inspiration comes in waves and isn’t focused enough yet…)

Nurob was woken in the morning by a strange noise. Strange noises weren’t uncommon in his line of work, but today they were especially strange. He quickly got dressed and spoke to Nahira, his best foreman. She was working for the engine since several years, but what made her stand out was her ability to analyze even the most subtle of issues.

He remembered when one of this engineers would just look at a slight deviation from the norm as a “minor malfunction of the sensors”, but later on it would actually cause a major Hurricane on some remote planet, and he remembered the locals calling it “Sandy” for whatever strange reason.

To be fair, that “remote planet” was more important to them than anything else. it was the one planet in this system that currently bore life. Nurob remembered a careless predecessor in his job that ignored warning signals which turned out to be genuine, and it cost them a whole planet of life.

As Engine Guardians, they were technically not responsible for anything out of the Norm, but losing the planet that was commonly called “Mars” caused a big investigation from the Creators and eventually led to Nurob’s appointment to guardian. So he knew that the last remaining planet with life was sacred to him. The creators were reasonable people that liked to experiment, but at the same time they were also serious about what they were doing. They knew that creating life was non-trivial, and that creating sentience was even harder. Wasting sentient species for no good reason was pretty much the only reason why he got his job, and Nurob had no intention or losing it.

It amused and concerned him to equal parts how Nahira was treated by her peers. Their species did not really differentiate between Males and Females, but there were subtle differences, and the Creators made a point of splitting any species into two halves, but all of the Males discounted Nahiras observations as simple female intuition that was as misguided as the human concept of Religion. But Nurob learned to trust Nahira a long time ago when she predicted a giant impact that almost lost them the last remaining planet.

He quickly dressed in simple pants and robes to avoid any accusation of indecency and ran down the hallway. On his way, he looked at the giant dials that were showing details like temperature, rotation speed, solar power output and other less important numbers. These numbers looked good at first glance, but Nurob knew that something, somewhere must have been off. As he entered the monitoring chamber, Nerakul took notice.

“Nurob! Why are you here? Your shift doesn’t start yet!” he asked, but Nurob was too concerned to answer in a meaningful way. “Where is Nahira? I need to see her immediately because she sounded the alarm!” Nerakul was visibly disgusted. “Do you really believe in that nonsense? You saw the numbers on your way. The Jewel of Life is getting supplied with enough solar energy to sustain for several thousand years! Nahira is just having one of her intuitions, you know how women are!”

Nurob ignored him. He was used to Nerakul’s dismissive attitude and he knew that it was mostly justified, but the risk was too high. Everything related to that strange planet the locals called “Earth” was top priority because a loss would be catastrophic. He finally found Nahira in a corner, monitoring some solar flare activity. “Nahira, you rang the alarm! What is wrong?” She was visibly nervous, and Nurob was wondering if it was because of him or because of the situation at hand. He had some time thinking about her, so he knew that she only just started her job a few cycles ago and never had any special occasions to deal with. “Sir, I saw that the Solar Energy output dipped by 16% to only 1012.24 units. What made me concerned is that the engine temperature also dropped by 17 degrees. I fear that we may not get the Solar Energy up again within the next three or four cycles.”. He listened carefully and took a moment to think. The last time the Solar Energy dropped by more than 10 percent, the planet they all cared so much about went through something called an “Ice Age”. Nurob was only a simple worker back then, a Grunt that kept the machine running. But he remembered the big commotion that was going on at the time. People were panicking about losing the planet, even though everyone knew that a lot of mammals would easily survive. Nurob could not let this happen.

After going through some more diagnostic checks that were available in the monitoring chamber that didn’t lead to any meaningful results, he knew that he would have to go where none of the guardians went in a long time. He had to look at the core of the Engine. To a primitive mammal from the planet Earth, this might have been extremely impressive. But the Guardians knew that the Engine was just an extremely old design that was kept alive through good maintenance and sheer luck. He noticed Nahira on the other side of the corridor. “Nahira!” he shouted “I could use a hand to fix this. Since you’re the only Guardian who seems to notice this stuff you’re also the only one I trust to help me”. She was visibly impressed and hesitated for a moment, then nodded and walked towards him. “Grand Guardian, I have no experience with the Engine itself, are you really sure?” He just looked at her and waved his arm towards the hatch. “You have as much experience as I do, which is to say, not very much. But fools like Nerakul don’t notice small differences, so how would they be able to adjust an Engine of this size?”

The Engine was indeed massive. Built billions of cycles ago, it was made with ancient technology that required several hundreds of components for even the simplest tasks, but it also had a certain nostalgic beauty to it. Nurob had worked on much more modern engines, but he always felt that the new stuff lacked personality, or as he called it, “soul”, a word he learned during his studies of the planet he was appointed to guard. This old engine was a piece of art, build by the creators when they had their Bipedal phase of creation. Duality was a recurring theme: Two Legs, Two Arms, Two Eyes, Two Genders, Two Personalities. Over time, they experimented a bit more with different numbers, and while some experiments were successes (increasing Gender diversity worked really well in more civilized areas of the world) there were also a lot of failures (increasing the number of legs to four created some truly useful species, but going past that only caused creatures everyone universally hated). Nurob often wondered why the two personalities were allowed to exist (he personally preferred the personality that loved life and embraced fun over the more protective one), but the Creators made it clear that any disturbance would have severe implication to the Guardians, so he obeyed.

They spent several hours investigating the mechanical part of the Engine, but everything seemed in order. It was an elegant (if somewhat verbose) design and thus easy to troubleshoot, so they moved over to the software side of things. Nurob often admired the humans for their naiveté, believing in omniscience of their Creators even though they are really just trying out a lot of genes to see which work and then leave the hard work to the Guardians. At the same time, being in the heart of the Engine was something no human would ever accomplish. Nahira had to smile when she logged in to the Administration Interface. Primitive races are easy to impress, and what could be more impressive than the power of light? It took only a little Energy to light up the sphere they were in, but Humans were impressed by what they called the “Sun”. And that was only the light! Nurob often wondered what would happen of Humans would see the real power behind Solar Energy, the giant Engine? He had witnessed the discovery of electricity, the realization that the Earth was round, the first Lunar Landing and the end of the Curse of the Black Sox. But as belief-shattering these events were, they couldn’t compare with the realization that there was a giant engine at the core of the most important celestial body, build by sentient beings and used for experimentation.

But as with any experiment, the Software made by the creators had flaws. Sometimes, it would allocate resources in the wrong places. It would do that a lot, actually. The common case would just result in slightly larger bones, but there were uncommon cases where a pregnant woman would give birth to two children that were joined in one body structure. At first, this was seen as a major issue, but upon closer investigation it became clear that the solution won’t be easy. There was an issue if multiple births happen at the same time, and the solution would cause a potential slowdown in births. As the earth population grew and grew, the issue would occur more and more often, but the Creators did not want to reduce the birth rate out of fear of the planet. Instead, they decided to intervene. They decided to inject knowledge about a workaround into the fetus of a man later known as Ben Carson and were delighted when he later on managed to resolve the issue after it happened to people. Of course, this workaround wasn’t viable for the general case and had some risks on its own, but compared to the risk of decreasing human birth rate, it was deemed acceptable.

Nahira saw the issue first. “It seems that the Creators didn’t expect this race to live past last year, or at least the past earth year” she said. “It looks like there is another Ice Age planned, although the documentation says that the next one should be pretty far off.” she finished before Nurob took a look at the data. Indeed, the Earth was due for a Reset, which meant hefty storms followed by cold temperatures for thousands of years. But that seemed inappropriate at the time. Those Humans had proven willingness to improve. Several times in the past, Nurob considered pushing the reset button, but someone somewhere always evolved one step further and saved the race. And even though there were many things he didn’t like, the current humans seemed a lot more evolved than any of their predecessors. In fact, they even started to treat their breeding gender as equals, something which the Guardians still struggled with.

They discussed several alternative solutions. Slowing down the Earth’s rotation around the Sun was the most promising solution, but the humans would very likely notice it and react like any other animal whose cage was disturbed – panic would spread and potentially cause irreparable damage. Moving the Engine closer to the Earth would have similar ripple effects to other planets and would be equally visible. Ever since a guy with a Mohawk shot a robot 300 million miles to space to land in a target area the size of a sheet of paper, the Guardians knew that the humans would notice every small change. “I remember writing code like this in school” Nahira eventually said. What concerned Nurob was the thing she hadn’t said. It was a high risk, this codebase was written a long time ago, and none of the original Creators were available for guidance. There was no test environment and no easy way to rollback any changes. There was no documentation as to why some of the choices were made. But there was also no way to add more duct tape to it. (Nurob once heard that expression from human engineers. He didn’t know what Duct Tape was, but the humans were so excited about it, Nurob would use the phrase a lot.)

Nahira sat down in front of the archaic terminal again and studied what she saw. “I can see what they are doing. This is a very verbose way of doing things and I’m too afraid to change it too much. But see this?” She pointed at a part of the code. Nurob had never worked on the Software side of the engine. He was primarily a mechanic, and even though he could read enough to understand what’s going on, he admired her ability to actually make sense of it. “This seems to be… depending on some timing? The timing of that giant magnetic platter in section C of the engine? I know the platter slows down over time, so I assume this part also executes slower over time?” She nodded. “Indeed. This is a common issue on older engines like these. But it is a relatively easy fix with some of the new methods we developed in recent years. See these two things here? They need to happen before we can continue, but they are not dependent on each other. So I can just…” She started typing frantically. Nurob knew the look in her eyes too well. Absolute immersion, focus on the thing one is working on. Being “in the zone”, to use another term he learned from the humans. There are many Guardians in the engine, but most only did what they were told to do, very few had the ability to completely dive into the inner workings with passion and genuine interest. Eventually, she looked at him again. “So this is now executing in parallel, which should buy us another three or four billion earth years to come up with a proper solution. Should we deploy this change to the engine?”

This was the hard decisions a person in his position had to make. It is what everyone expected from him. His job seemed so easy to the other Guardians who were doing the real work, but his decision right now could destroy the project or make it succeed. Success was one of these things no one could measure because it just meant the absence of catastrophic failure. This is what he had to avoid now. But after watching Nahira work, he was confident that this was the right decision. “We’re going to emit some Solar Flares while we’re deploying. That should keep the brief outage hidden. Push it!” he declared.

There was a large observation window in the engine room, and Nurob stood there watching the planet Earth through it. Whenever he looked through one of the windows that were part of the Engine complex, he believed that someone was watching him as well. These primitive mammals with their limited intelligence and capacity, yet with more creativity than any other species he knew. Their enthusiasm for the Universe was what made him love them so much. The Flares would surely give them some headaches and he could already imagine them running around, looking at data and worried about the things they shot into space. And as he stood there and watched, Nurob wondered if someone else was watching him right now, in an Engine that really is powering him and the Guardians?

He knew that he would have another billion earth years to ponder that thought.

Let’s talk about the third best thing in the World

Beer. One of the great things about the US is that homebrewing is legal. It kinda needs to be because the prohibition killed off so many American breweries, we’re still living in the aftermath where the beer with the biggest market share is not American at all (It’s owned by a Belgian-Brazilian* company) and also tastes like urine.

But once you start looking past Bud, you realize just how awesome American Microbreweries are. If you’re in SoCal, I highly recommend the Orange County Beer Festival in Silverado to sample the goods that people brew here. If you’re visiting, I recommend anything from Kona Brewing Co, most Sam Adams or a Shiner Bock.

Homebrewing is one of those things that are easy to pickup and start and then offer a lot of room to master it. I started out with a simple starter kit that contained almost everything I needed, except for a 5 gallon kettle and some bottles (5 gallon results in ~45 12oz bottles).

After checking your local laws, you start out by boiling the beer for an hour, putting in any special ingredients that come with the recipe kit and stirring a lot. If you have a lawn and an outdoor burner, awesome! A gas stove works well enough as well, just expect it to take longer, but the risk of nasty boilovers is lower.

P1000991

After that, you need to chill it as quickly as possible. An ice bath works perfectly well for the start, although once you get serious I’d recommend a Wort Chiller.

P1010002

You then move the chilled wort into a fermenting bucket, which can be a glass carboy or a plastic bucket. Make sure that stuff is sanitized (the starter kit comes with sanitizing solution).

P1010013

This then goes into a cold, dark, quiet place for 2-4 weeks, depending on the recipe. Now you can take the time cleaning up the kettle and all equipment while you’re waiting…

After the fermentation is done, you fill the beer into a bottling bucket, together with some sugar (the recipe contains all the details). From there, you then fill the (sanitized) beer bottles one by one. This may be a bit messy as stuff will drip when you pull out the hose. Having another person helps, but isn’t required.

P1010033

The kit comes with a bottle capper and caps, which is a quite fun part of the bottling process. If you want some awesome custom printed caps, Bottlemark sells them for 12 cents a cap. Might be a good investment for the future.

P1010035

After you’ve bottled all ~45 bottles, it takes another week or two and you’re good to go! (These beers are bottle conditioned and there will be a small layer of (harmless) sediment at the bottom. It’s recommended to drink from a glass.

P1010040

This process is a bit simplified, but not by much, I mainly left out sanitizing and siphoning stuff. Brewing beer from a recipe kit is a really simple process, but one that opens the door into a whole world of experimentation and good flavors. You can also grab a few 1 gallon kits to have several experimental brews in parallel. There is also a StackExchange site dedicated to Home Brewing.

The 21st Amendment to the US constitution was one of the most important decisions in the past hundred years, a true victory of democracy. Let’s not desecrate that by drinking Bud.

*Just to be clear, this is no stab against the countries Brazil or Belgium or their beer culture. Especially Belgium has an amazing culture of great beers like Stella Artois. I don’t know much about Brazilian beers, but people told me that Eisenbahn Weizenbier is awesome.

**Drink in moderation. Alcohol can be awesome (I’ll refer to Korpiklaani who said it better than I ever could), but it also has destroyed lives of people who grew too dependent on it. And seriously, if you drink and drive, you deserve to get into an accident that leaves you paralyzed for the rest of your life.

***I think it’s stupid that the legal drinking age in the USA is 21. I come from a country that legalizes Beer at 16 and even though there is still a lot of stupid drinking happening, Germans are known for having mastered social drinking. Ask anyone who’s ever been to a Hofbräuhaus.