The Linq Saga!

Blog

The last couple of weeks of my journey were tough! I went through one of those times every self-taught developer knows well: feeling absolutely stuck.

The problem

I had gained some momentum building my first app. Day after day I was just building, tweaking, implementing new features, without much trouble. My creative juices were flowing seamlessly. Pure coding bliss.

There’s still a bunch of stuff to fix on that app, but I decided to start my second. After all, I do need some volume in my portfolio. In the processing of developing it, I wanted to implement a report system. Nothing too crazy I have an SQL table of events that have a type and a date. Then I wanted to display the count of events per date or in any other way I wanted.

The journey

I naively thought it would take me a couple of hours to figure it how to implement the report. I started trying to do it with typescript in the front-end. Something like generating a map with the dates and an object with the type and count as properties. 

It turned out to be more laborious than I thought so after half a day I decided to try to do it in the backend, using the power of C#. After another couple of hours banging my head against the wall with Linq, I posted a question on Stackoverflow.

The realisation

I got bad reputation for the question, which made me realise I don’t know much of Linq and SQL in the first place. The replies on my post were close to unreadable to me. The chain of Linq queries was way above my knowledge and they didn’t work because the solutions didn’t use Linq against Entity framework. I spent three hours trying to adjust the reply to use entity framework but it just wouldn’t work.

At that point I was already very tired and I had even skipped my lunch time Uber shift and my afternoon workout to try to fix it. So the frustration was kicking in. I jumped into Pluralsight to watch some videos about Linq but suddenly I realised what the problem is. I just didn’t know enough SQL. Even if I used Linq I feel like I’d be cheating, after all the database is the app’s brain and SQL is the nerve impulses.

The beautiful SQL query to pivot a table dynamically

The change

So after two days of feeling helpless I decided to embrace my ignorance and go back to the beginning. Revisiting SQL from scratch Pluralsight, this time running all sorts of queries , as simple as a SELECT and directly into SQLite. Then I decided to move to MS SQL Server because SQLite didn’t offer good support for Datetimeoffset, which I use in my program. Instead of going back and forth using Windows Parallels I used SQL Server in a Docker Container.

It’s amazing that in two years of training I have neglected talking directly with databases, which is what I love the most. Starting to finally understand SQL in a deeper level was like finally being able to communicate after being lost in a new country. 

I’ve also realised I can combine raw SQL with Linq using the rawsql query, which could be helpful in cases where Linq shows limitations.

Eureka! 

The Result! Pivoted Data!

After almost three weeks and a LOT of studying, I finally did it! I can implement any type of report I want. This will unlock the power to create many apps in a short amount of time. Some of the things I’ve learned in the process.

  • Entity Framework has limitations. Apparently it’s well known that “Group By” can face some issues against EF.
  • String Concatenation in C#! So much better than plus sign!
  • Basic SQL, pivoting tables statically and dynamically, creating stored procedures
  • Basic Linq, anonymous objects and reporting the equivalent to a pivoted table
  • Javascript is case sensitive! The Final Hurdle in the front-end. Wasted about five hours on this one.

Leave a Comment

Your email address will not be published. Required fields are marked *