I’ve begun my learning escapade with LINQ to SQL. I sort of had an idea what it was about but had never really had a chance to play with it. I’m glad I took the time! There’s a million and one tutorials on the net so I won’t bother replicating one. I’ll just do a ‘snapshot’ of some of the features.
First of all, you add the LINQ to SQL Class in Solution Explorer. Then drag a couple of tables from Server Explorer onto the design surface. You can then tell it what the relationship between the tables is (you don’t have to have set this in SQL!).
Then, when you hit save, it auto generates some classes for you that look like voodoo inside:
Now the cool part… the code window. Check out how simply you can select a record and update it. Then see how we can select a record which would have required a SQL JOIN before. We’ve selected from the JobHistory table, where the related BackupJob has a Computer field which equals “Mimir”. It’s all automatic, I’ve not added anything else in. Very clever.
Adding a record in SQL is normally an utter PITA. Lack of intellisense means having to build a huge parameterised query. Not with LINQ to SQL!
Adding Relational Records
I’ve still got more digging to do into the product but this bit here has pretty much blown me away. This would have been a right nightmare in SQL. You’d have had to do an insert and return the SCOPE_IDENTITY, then insert the history rows using that. This makes is staggeringly easy. You just build the objects in memory, add the ‘child’ rows to the main row and off you go.
and to prove it works, here are the rows:
I have to say, I’m hugely impressed! I wish I’d known about it sooner. For performance, it’s undoubtedly better to stick to hand rolled SQL, but for those horrendously boring CRUD scenarios like saving user preferences, this will be a god send!!