SQL Queries and LinqPad
by Joseph on Apr 22nd in IDE, LINQ, SQL Server, tools, utilities
Just a quick post today.
I was recently brought on to do some custom SQL queries in a Database that I was unfamiliar with. I had a short time to work, and the queries involved traversing several objects. I could have spent time in SQL Server, figuring out relationships, documenting foreign keys, and generating UML diagrams to help write the queries. But that’s not what I did.
Instead, I used LinqPad to find my Root Node.
Once I found that, I was able to traverse down, find the information I needed, write the query in a language I was VERY fluent in (C# and Linq), and then clean it up in a language I’m pretty darn fluent in (T-SQL).
There are a couple of caveats here.
LinqPad uses Linq-To-SQL under the hood as the default ORM. That’s fine, for what I needed. The down-side is that the SQL it generates is not ALWAYS super optimized. One example was generating a “GroupingTime.” I took the TimeStamp of some transactions, truncated the time down to the hour (not rounding, I wanted the hour it occurred in), and then grouped by “GroupTime”. MS SQL Server has a built-in function that makes this very easy. But if you’re doing it in ANSI-SQL, it looks terrible. So one of my first changes was to use the built-in functions.
[gist id=”1c490b8f1adb4d1adab265d9e711fb2d” file=”UglyTimeTruncate.sql”]
Became the somewhat more readable
[gist id=”1c490b8f1adb4d1adab265d9e711fb2d” file=”BetterTimeTruncate.sql”]
Another LinqPad artifact was… gross… naming of table aliases. LinqPad (and any ORM in general) likes to do things like
[gist id=”1c490b8f1adb4d1adab265d9e711fb2d” file=”BadAlias.sql”]
I find that gross. It’s fine if I don’t have to read the SQL, but if I’m doing anything at all with it, I need to be able to read it. So, I cleaned that up too. The biggest help here was RedGate’s SQL Prompt. If you’re doing ANYTHING in SQL Server, and you don’t know about RedGate, then stop reading this article RIGHT NOW, and go look at their products. They make SQL much easier to work with, and much less painful to debug.
That’s it! Nothing earth shattering today. Just wanted to share a couple of the tools I’ve been using this week to make my life easier. What do you use?
Tools Used:
