Insert? Update? Nope, it’s MERGE!

I needed to track changes when updating a table, but I wasn’t really sure what would be the most perf-friendly method. Luckily I stumbled on a great post about T-SQL MERGE. The post described almost to a “T” what I needed to do: some records would stay the same, some would drop off, and others would change.

MERGE is pretty slick. I was able to easily take the 3 queries I had in place for insert, update and delete (well, soft delete anyway) and basically cut/paste them right into the merge command. It is nice that the syntax matches up so well and makes sense (unlike things like ROW_NUMBER() – yuck). It was really a nice, concise way to get exactly what I needed, and it performs very well.

EF and “dynamic” data

We use EF for our e-commerce website, and we have the need to get some data out in the form of reports. Ideally I’d like to be able to basically write up a report query and have the data come out on the other side. However, with EF it isn’t necessarily that simple.

I really don’t want to have to go through all the ceremony of creating models, mapping classes, services, etc. I’d like to keep it pretty simple with a service that can give me back the data I want, then I can use it in a Google Chart or a jQuery datatable or something on the client side. I want the server-side to be basic, and then I can focus on the client-side to give a rich report experience.

I did a quick search and came up with a couple of interesting options. The first is a way to dynamically query views. It sounds very interesting, but it looks like it isn’t quite what I am looking for. The post leaves things a little open-ended, but it looks like the views he is querying already have all the strongly-typed stuff needed and the code just performs dynamic queries against those static objects. I could be wrong though as there is not concrete example of how it is used, just the theory behind all the dynamic wackiness going on.

The next option looks a little more like what I am looking for – it returns a dynamic object using SqlQuery. The code in the post is a little verbose for my taste, but a commenter converted the code into an extension, and I like that idea a lot better. I put his code into a gist.

After looking into the options, I think that maybe the notion that I have to stick with EF is just holding me back. I am very familiar with Massive and have used it and enjoy using it. It is a single class file, and maybe it is just the thing I need to get this report data out of my database easily – which is the idea behind Massive!