In this post I am going to cover off how functions such as Count(), Average() and Sum() work, plus the different ways to call them. Then I will move onto some functions that are not from the domain of SQL but add great features.
At first glance, Lambda Expressions are bound to confuse most people. Myself included. However, a little digging and experimenting will show they are a simple enough concept. I will cover them now to try remove any confusion in later code snippets.
Before we proceed, this is a good time to see another feature of LINQ-to-SQL which allows you to change the property names of the generated classes to no longer match the underlying field names, this could be useful if you have a strange naming convention for your field names, or in our case with AdventureWorks we have instances like on the SalesOrderHeader class where it has two links to the Address table (BillTo and ShipTo) but these relationships get modeled Address and Address1.
This is obviously undesirable as there is no clear indication which one links to Address based on ShipToAddressID and which on BillToAddressID.
Luckily we have a solution close at hand.
Now I have some of the foundations out of the way, albeit in a rather brief overview that assumes a reasonable level of competency, it is time to move onto some of the more interesting code snippets.
One thing I never liked much about writing SQL in either FoxPro or with PassThrough technologies is how you piece together a complex query from a number of UI selections. The most common occurrence of this is in reporting. The number of ways you can usually slice and dice a sales report makes for some fairly nasty code to build a string based SQL statement.
In this, my third article on LINQ-to-SQL, I am going to be taking a step away from the code and delve into a discussion about what I will think most people will miss when they first approach this new technology.