Late last year when I implemented the unread count Live Tile in gReadie I found myself stunned at just how complicated it was. It seemed to me that it would make a lot more sense if I could just whack a [LiveTileUpdater] attribute on a static method in a class and the phone would run that method occasionally.
I was therefore delighted when details about Mango were released as this is basically what Microsoft implemented.
Over the last few weeks I have been doing a complete rewrite of gReadie, my Google Reader client for Windows Phone 7.
The original codebase for gReadie was really quite cluttered, and I wasn’t going to be able to take advantage of the new features Mango enables without pulling it all out and starting again.
With most of the rewrite behind me now, It is time to start putting together a few blog posts discussing the new features I am using and lessons learnt along the way.
An issue I have seen blogged about a number of times with LINQ-to-SQL is that by strong typing queries, you lose the ability to create generic functions for processes such as fetching records by their Primary Key.
I have recently switched over to using SQLMetal to generate my LINQ-to-SQL DBML and Context class. There are two things I like about it, one is that it is far faster. I just click a script on my desktop rather than open up the designer and recreate each table that has changed. I also prefer some of its naming conventions, it deals with multiple relationships to a single table better.
Consider the case where you have an Orders table and it has two fields holding Address keys (ie ShippingAddressID and InvoiceAddressID). The LINQ-to-SQL generator in Visual Studio will create 4 properties ShippingAddressID, InvoiceAddressID, Address, Address1. The last two being references to the Address entities. The problem being you cant work out which is which. SQLMetal will detect this and one of them will be named ShippingAddress, sadly the other will still be simply Address. There is hope though.
I have recently started work on what will become our first production application using LINQ-to-SQL and had hoped to use the CreateDatabase() function that is found on the generated DataContext to simplify the process of setting up the database on the client machine.
Ideally I wanted a nice simple piece of code like this
Next we are going to look at how you can use partial classes and partial methods to add functionality to your generated LINQ-to-SQL classes. One of the nice things about partial classes and the new partial methods is that you can extend the generated classes into a separate file that is not destroyed when you update the underlying data model. See my complaint here about other ways of changing the classes that does not have this benefit.
First things first is to create a new C# class file, I have called it AdventureWorks_Extra.cs to sort it below the AdventureWorks.dbml
One aspect of LINQ I have not covered yet is the equivalent of a GROUP BY in SQL. The GroupBy() function (which of course can be used from a LINQ expression as well as from the method syntax and I will show both) provides this functionality. One of the interesting things about grouping however is that there is a new interface introduced that you will want to understand, lets take a look at the method signature for GroupBy().
I have been looking at my web stats for the recent run of LINQ-to-SQL posts, and it seems a lot of people are making their way here from searches about some of the more standard features of LINQ-to-SQL. In the interest of addressing these visitors I am going to put together a post that covers the basics of data access.
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.
I am going to start by showing a few new language features that are important to LINQ (and hence LINQ-to-SQL) that you may not have come across yet if you are not already playing around with this stuff.